Joining 2 symetric table trees

Hello

This is my code which dosent work

SELECT Person_1.PersonName, Statistics_1.DateEmission AS DateEmission1,

Statistics_2.DateEmission AS DateEmission2

FROM Statistics AS Statistics_1 INNER JOIN

Person AS Person_1 ON Statistics_1.PersonID = Person_1.PersonId LEFT OUTER JOIN

Person AS Person_2 ON Person_1.PersonId = Person_2.PersonId INNER JOIN

Statistics AS Statistics_2 ON Person_2.PersonId = Statistics_2.PersonID

WHERE (Person_1.PersonId = @.PersonID) AND (Statistics_1.DateBack = @.Date1) AND

(Statistics_2.DateBack = @.Date2)

In fact the code in bold is exactly the same with 2 different instances of tables Person and statistics, My problem is when this condition (Statistics_1.DateBack = @.Date1) AND (Statistics_2.DateBack = @.Date2) is true I got my result but when (Statistics_1.DateBack = @.Date1) OR (Statistics_2.DateBack = @.Date2) IS False I got nothing,

I need to get a not empty result when (Statistics_1.DateBack = @.Date1) is true as I used a LEFT OUTER JOIN between the 2 trees.

I don't understand why my code is wrong, can someone show me how to fix this issue.

Thanks in advance.

Does the example below return the results you're expecting?

If not, then could you provide sample data for both tables, along with the expected results?

Thanks
Chris

SELECT Person_1.PersonName,
Statistics_1.DateEmission AS DateEmission1,
Statistics_2.DateEmission AS DateEmission2
FROM [Statistics] AS Statistics_1
INNER JOIN [Person] AS Person_1 ON Statistics_1.PersonID = Person_1.PersonId
LEFT OUTER JOIN ([Person] AS Person_2
INNER JOIN [Statistics] AS Statistics_2 ON Person_2.PersonId = Statistics_2.PersonID AND Statistics_2.DateBack = @.Date2
) ON Person_1.PersonId = Person_2.PersonId
WHERE (Person_1.PersonId = @.PersonID)
AND (Statistics_1.DateBack = @.Date1)

|||EDIT: Never mind, looks like Chris beat me to it. ;)

|||

Thanks Chris it works :D

do u have an explanation Why my SQL Code didnt work ?

|||

Fekih Mehdi wrote:

Thanks Chris it works :D

do u have an explanation Why my SQL Code didnt work ?

It's because of the order of the joins. They're handled in the order written (functionally speaking - I believe the query optimizer will sometimes do them in a different order internally if it'll perform better).

So first it does the inner join, which gives no unmatched rows. Then the left outer join is performed, bringing some unmatched rows into the record set. Then the next inner join is performed, and since those previous unmatched rows now don't match anything in this join, they're filtered out again.