help with sql "RIGHT JOIN"

[Daot Lagorille]

I am currently smacking my forehead into the top of my desk. Here's why!

I am trying to get a RIGHT JOIN to work:

SELECT table_two.times, table_one.dates
FROM table_one
RIGHT JOIN table_two ON table_two.times = table_one.times
WHERE table_one.dates =  '2004-04-14' 

Now, I am trying to get a list of all the times from table_two, which is itself just a list of times from 9 am to 5 pm in half hour increments, with all matches from table one, such that if a time from table two does not show up in table one, in the result, the "table_one.dates" field should just be null.

Does this make sense?

Anyway, I am not getting the non-matches in the result set, as I should because I am using the RIGHT JOIN.

Make with the help please!

[DaiWelsh]

It is because you are using a WHERE clause that specifies a value for the left hand table. If you specify that as an additional criteria for the join instead it should work, e.g.

SELECT table_two.times, table_one.dates
FROM table_one
RIGHT JOIN table_two
ON (table_two.times = table_one.times
AND table_one.dates = '2004-04-14')

If you join and then use a where clause it (effectively if not literally) gets the results for the join then applies the where clause, so you would have had the 'NULL' records you wanted after the join but they were then removed by the where.

Hope that makes sense.

Dai

[CardboardHammer]

Alternative way of doing it, if you'd prefer to keep only the fields being JOINed in the ON clause:

SELECT table_two.times, table_one.dates 
FROM table_one 
RIGHT JOIN table_two ON table_two.times = table_one.times 
WHERE table_one.dates =  '2004-04-14' OR table_one.dates IS NULL 

[Daot Lagorille]

Sweet moves, my friends.

Thanks very much. I shall now go bandage my poor swollen forehead.