Problem with simple SQL join

This relates to a webpage that should show all upcoming events, and mark any that are in the current user's diary.

diary table

diary_id

member_id

event_id

event table

event_id

region_id

...

region table

region_id

...

member table

member_id

...

QUERY:

SELECT event.*, region.name, diary.diary_id 
FROM event, region 
LEFT JOIN diary on diary.member开发者_开发百科_id = 10 AND diary.event_id = event.event_id
WHERE region.region_id = event.region_id AND `date` >= NOW()

This is returning unknown column event.event_id and I can't figure out why. I'm no SQL whiz but expected this would just work and give me a NULL in the diary_id column for all events that are not in the user's diary

You are mixing join syntax. Try this instead.

SELECT event.*,
       region.name,
       diary.diary_id
FROM   event
       INNER JOIN region
         ON region.region_id = event.region_id
       LEFT JOIN diary
         ON diary.member_id = 10
            AND diary.event_id = event.event_id
WHERE  `date` >= NOW()  

Update

Your problem with not finding event_id is because of this FROM event, region. It can't find event_id in the on clause. Change your query as suggested above but it would also be possible to fix it by switching places of the tables to FROM region, event. Don't do that. Use the new join syntax introduced to the SQL language some 20 years ago.

Don't put diary.member_id = 10 in the where clause if you want to do the left join. In PL/SQL this will turn your left join into a join without asking you

The below should do better:

SELECT event.*, region.name, diary.diary_id 
FROM event
JOIN region on region.region_id = event.region_id  
LEFT JOIN ( select diary_id, event_id
              from diary 
             where diary.member_id = 10 ) diary
       ON diary.event_id = event.event_id
WHERE `date` >= NOW()

First of all I wouldn't put diary.member_id = 10 in the Join:

SELECT event.*, region.name, diary.diary_id 
FROM `event`, region 
LEFT JOIN diary ON diary.event_id = event.event_id
WHERE region.region_id = event.region_id AND `date` >= NOW() AND diary.member_id = 10

Are you sure that event.event_id is not event.id?