Outer Join

[Guest_imported]

I have a sql statment that returns everything I want but if one cell in the DB for a row is null it will not return that whole row. I understand that I must accomplish this through an outer join statement but I am pulling data from a few diff tables. Attached is my SQL statement, I need to make sure that even if the Buddy field has nothing in it I want to pull that whole row back..... Thanks!

************************************************

strQ = "select tblEmployees.PhoneNumber, tblEmployees.lname, tblMaster.Buddy, tblEmployees.ACESID, tblEmployees.fname, t2.PhoneNumber, tblEmployees.ACESID, tblInOut.in, tblEmployees.UserName from tblEmployees, tblEmployees t2, tblInOut, tblMaster where tblEmployees.Unit in ('fin1', 'fin2', 'fin3', 'fin4') and tblInOut.UserName = tblEmployees.UserName and tblMaster.Employee = tblEmployees.ACESID and t2.ACESID = tblMaster.Buddy order by tblEmployees.lname"

**************************************************

 

[MalcolmW]

If you search for the phrase "outer join" you should find some sample code. Malcolm

 

[mron]

SQL Server won't let you do outer joins on queries where there are already inner joins. But if you screw around with Query Analyzer, you can try the *= and =* operators to force a join anyway. From the SQL Help:

SQL Server supports both the SQL-92 outer join syntax and a legacy syntax for specifying outer joins based on using the *= and =* operators in the WHERE clause. The SQL-92 syntax is recommended because it is not subject to the ambiguity that sometimes results from the legacy Transact-SQL outer joins.

 

[magicrat]

Try this:
strQ = "select tblEmployees.PhoneNumber, tblEmployees.lname, tblMaster.Buddy, tblEmployees.ACESID, tblEmployees.fname, t2.PhoneNumber, tblEmployees.ACESID, tblInOut.in, tblEmployees.UserName
from tblEmployees Left Outer Join tblInOut
On tblEmployees.UserName = tblInOut.UserName,
tblEmployees tblMaster On
tblEmployees.ACESID = tblMaster.Employee,
tblEmployees t2Left Outer Join tblMaster On
t2.ACESID = tblMaster.Buddy
where tblEmployees.Unit in ('fin1', 'fin2', 'fin3', 'fin4') order by tblEmployees.lname"

Not sure I've ordered your tables correctly in the outer join statements, but this syntax should work. Th combination of Outer joins may not yield the result you want, if you want an inner join between tblEmployees and tblInOut for example.

 

[MalcolmW]

"SQL Server won't let you do outer joins on queries where there are already inner joins." -mron (visitor)
I'm not sure what this means, because I haven't encountered this limitation. If anyone understands this, I would appreciate an explanation. Malcolm