How to join to select statements

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to join to select statements

Author

Topic

lio1972
Starting Member

19 Posts

Posted - 2012-10-28 : 09:18:49

Hi I am practising with NorthWind and I dont know how to join this to selects by orderID
Thanks

select Orders.OrderID,sum([Order Details].Quantity*[Order Details].UnitPrice) from Orders inner join [Order Details] 
on orders.OrderID=[Order Details].OrderID group by orders.OrderID;

 select Employees.FirstName,Orders.OrderID 
 from Employees inner join Orders on Employees.EmployeeID=Orders.EmployeeID;

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-10-28 : 11:44:17

What result are you looking for?
I'm guess there is more than one employee per order so you will get duplicate information.

try
select Orders.OrderID,sum([Order Details].Quantity*[Order Details].UnitPrice), max(e.FirstName)
from Orders
inner join [Order Details]
on orders.OrderID=[Order Details].OrderID
join Employees e
on e..EmployeeID=Orders.EmployeeID
group by orders.OrderID
order by orders.OrderID

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

lio1972
Starting Member

19 Posts

Posted - 2012-10-28 : 12:41:46

Thank you!
I tryed to dislplayed the sums of all different orders with the employee name in the front

select Employees.FirstName,Orders.OrderID ,sum([Order Details].Quantity*[Order Details].UnitPrice)
 from Employees 
 left join Orders on Employees.EmployeeID=Orders.EmployeeID 
 inner join [Order Details] 
 on orders.OrderID=[Order Details].OrderID group by Orders.OrderID;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-29 : 09:43:22

if you want sum of orders you should not be including OrderID in result as it makes result one per order and you wont be able to aggregate over entire orders


select Employees.FirstName,sum([Order Details].Quantity*[Order Details].UnitPrice) AS TotalOrderPrice
 from Employees 
 left join Orders on Employees.EmployeeID=Orders.EmployeeID 
 inner join [Order Details] 
 on orders.OrderID=[Order Details].OrderID 
group by Employees.FirstName;

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources