www.webdeveloper.com
Results 1 to 4 of 4

Thread: help with sql "RIGHT JOIN"

  1. #1
    Join Date
    Aug 2003
    Posts
    113

    help with sql "RIGHT JOIN"

    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:
    PHP Code:
    SELECT table_two.timestable_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!

  2. #2
    Join Date
    Feb 2003
    Location
    Derby, UK
    Posts
    456
    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

  3. #3
    Join Date
    Nov 2003
    Posts
    655
    Alternative way of doing it, if you'd prefer to keep only the fields being JOINed in the ON clause:
    PHP Code:
    SELECT table_two.timestable_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 

  4. #4
    Join Date
    Aug 2003
    Posts
    113
    Sweet moves, my friends.

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

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles