Multiple values returned

[acesn8s]

I am trying to fix an existing query that does not return all the people that it should. I think I have found the problem but by removing the part of the WHERE CLAUSE statement that is keeping the query from returning all the people it causes it to return multiple rows of the same person. I have tried an outer join but that has no effect.

By removing this line I get multiple rows returned but if it is in place I do not get rows returned for people that have a null value in Table_One. The query uses Table_Two.Person_ID in the Select statement.

SELECT
Table_Two.Person_ID,
Table_Two.Name,
Table_Four.JobName,
Table_Three.JobID

FROM
Table_One,
Table_Two,
Table_Three,
Table_Four

WHERE
Table_One.Person_ID = Table_Two.Person_ID --This seems to be the problem
AND Table_One.Person_ID = Table_Four.Person_ID

 

[bborissov]

Because when you have this type of query it uses INNER join. That means the resulset is generated from the records that have the same Person_Id in all four tables.
Try this:

SELECT
Table_Two.Person_ID,
Table_Two.Name,
Table_Four.JobName,
Table_Three.JobID

FROM Table_Two
LEFT JOIN Table_One   ON Table_One.Person_ID   = Table_Two.Person_ID
LEFT JOIN Table_Three ON Table_Three.Person_ID = Table_Two.Person_ID
LEFT JOIN Table_Four  ON Table_Four.Person_ID  = Table_Two.Person_ID

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[acesn8s]

Thank you for the help bborissov.