MySQL OUTER JOIN problem

MySQL OUTER JOIN problem

Post by Michael Fu » Fri, 29 Feb 2008 07:06:01


Does MySQL have some weird quirk regarding OUTER JOINs? I have this
query:

SELECT Date,P.Category,P.Person
FROM Bookings B RIGHT OUTER JOIN People P ON(B.Person=P.Person)
WHERE P.Category = '2' AND ((EndDate>='2008-02-01' And
Date<'2008-02-28') OR DATE IS NULL)

This works fine on DBISAM but on MySQL it is not including
non-matching records from the People table. Can someone explain why?

I thought it might be the Date field so I modified the SQL to just use
the EndDate field but still get a similar effect.

Thanks in advance.
 
 
 

1. MS SQL 2000 LEFT OUTER JOIN and RIGHT OUTER JOIN problem?

2. When is an outer join not an outer join?

>

Yep. And it shouldn't.

Look at this old post of mine in another forum:
http://www.yqcomputer.com/

You must move your WHERE clause into the ON or you will continue to get
results like that.

SELECT RT.ReportName, COUNT(CR.ReportTypeID) AS CNT
FROM ReportType RT LEFT OUTER JOIN ClientReport CR
ON ( RT.ReportTypeID = CR.ReportTypeID
AND CR.ReportGUID = '9D973D11-6ACC-4079-B4F8-DF4A733ED760' )
GROUP BY RT.ReportName

The parens aren't needed in SQL Server, but they don't hurt. (They are
needed in Access.)

3. Access: problem with outer join - "Join Expression not Supported"

4. left join/inner join/outer join with repeat values

5. Joining two sets of INNER JOINs with an OUTER JOIN

6. Performance degradation with view and "left outer join" vs. "from x, outer y"

7. OUTER JOIN not returning all rows of outer table

8. Is outer apply like outer join?

9. DataTables and Inner Join - wrong - I wanted an outer join

10. LINQ: Inner Join & Left Outer Join Q

11. Inner Join/ Outer Join? Heck I don't know....

12. Left Outer Join on Join Table with Clause

13. Changing outer joins to inner join

14. Query Join - Outer Join?

15. is there a significant speed difference between INNER JOIN and LEFT OUTER JOIN?