Selecting Information from two different tables

[simeybt]

I know this must sound simple but I cant get my head around this. I am running a query that pulls information form a sales table for a specified week and matches it up with information from the staff table. this part works fine, but I want also to be able to pick another field from a "Bonus table" using the Userid as the key field and a specified week. What I am getting at the minute is a list of names and there sales of people who exist only the "Bonus Table". What I want is a list of all people and there sales along with their bonus if they have one.

i.e

ID OBS IBS Bonus
123456 1 1 245
565645 2 8 854


Simon

 

[BNPMike]

Join Bonus and staff with a right join then join staff onto sales with an ordinary inner join.

 

[simeybt]

I'm not completely sure how to do this. which of the three options for the relationships do I use for each join i.e. option 1, 2 or 3. just another note the bonus table does not contain entries for all the people in the sales table.

 

[BNPMike]

Choose "Include all records from staff and only those that are equal from Bonus..", then choose option 1 for the other join - which is what is should be already.

 

[AndreQ1]

SELECT Sales.SalesColumns
, Staff.StaffColumns
, Bonus.BonusColumns
FROM Sales LEFT OUTER JOIN Staff ON Sales.ID = StaffID
LEFT OUTER JOIN Bonus ON Staff.ID = Bonus.ID


The order you present the tables is:
TableWithAllRows LEFT OUTER JOIN TableWithSomeRows.

The key is that all tables thereafter have to be LEFT OUTER JOINed