Join condition - SQL Server Forums

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

Join condition

Author

Topic

madhan
Yak Posting Veteran

59 Posts

Posted - 2015-01-01 : 12:41:26

Hi - I am using AdventureWorks2012 database and trying to solve the following query problem.

Write a query that returns all the rows from the Sales.SalesPerson table joined to
the Sales.SalesOrderHeader table along with the SalesOrderID column even if no
orders match. Include the SalesPersonID and SalesYTD columns in the results.

I am planning to use "Left outer join" for this query.
Since it says "all the rows from Sales.SalesPerson table" Starting with this table.


Select SalesPerson.SalesYTD,SalesOrderHeader.SalesPersonID
from Sales.SalesPerson
left outer join Sales.SalesOrderHeader
on SalesPerson.BusinessEntityID=SalesOrderHeader.SalesPersonID

There is no SalesOredrID in Salesperson table for me to join. But the query problem talked about it. Please let me know the correct way of writing this query. Thanks

mandm
Posting Yak Master

120 Posts

Posted - 2015-01-01 : 13:12:35

Yes the query you have is correct. If you script out the SalesOrderHeader table create you see that the SalesPersonId is a foreign key reference back the the BusinessEntityId in the SalesPerson table

madhan
Yak Posting Veteran

59 Posts

Posted - 2015-01-02 : 13:02:18

Thanks for letting me know the query is correct. infact I wrote that query by looking at the Salesorderheader script and found out the FK.

My exact doubt is on the line"Sales.SalesOrderHeader table along with the SalesOrderID column even if no
orders match". does that mean do we need to provide any kind of join with SalesOrderID? even if no orders match means I think I need to join the salesorderdetails table becasue that is the table used for orders. Please help

mandm
Posting Yak Master

120 Posts

Posted - 2015-01-02 : 16:03:27

1) No I don't see anything in the description that would require you to JOIN to the SalesOrderDetail table.

2) I think it means that your SELECT list should return SalesOrderId even though it may return as NULL.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources