Need alittle help :)

Mark_Nordstrom

Ok as in previous post Ive said. I have 2 tables one Attendance with name, event, date and one Event with event, date.

What i wanna do is query Event table. List all events, problem is I wanna create a temp 3rd column when i query it on a webpage that shows a x for the rows that match the Attendance table with the same event name. So i have 2 tables each with event name. SO Say i had event T and Event M but T wasnt on Attendance and M was it would be

name , T
name, M, x

Hopefully you understand what i mean 🙂

tomhath

Your question doesn't make sense. Where did "name" come from if "T" wasn't in Attendance?

name , T
name, M, x

Mark_Nordstrom

tomhath wrote:
Your question doesn't make sense. Where did "name" come from if "T" wasn't in Attendance?

name , T
name, M, x

Shouldnt of been name

Should be

T
M, x

T and M are event names and X shows you went to that event.

tomhath

You want every row in Event, some of which will match a row in Attendance. That should make you think "LEFT JOIN". This is untested but should get you started:

select Event.event,
case when Attendance.event is null then '' else 'x' end as attended
from Event
left join Attendance on Attendance.event = Event.event
where ...

Mark_Nordstrom

select Event.event,
case when Attendance.event is null then '' else 'x' end as attended
from Event
left join Attendance on Attendance.event = Event.event
WHERE name='Mark' AND Event.date > (CURDATE() - INTERVAL 2 MONTH) ORDER BY Event.date ASC

Still showing all common events and not ones that dont match.

Problem is when you left join Attendance on Attendance.event = Event.event isnt it just joining the event rows that match ? Not the entire event table ?