[SQL] Join question

LazyManc

LazyManc

LazyManc

Associate
Joined
21 May 2003
Posts
1,365
Right, I have 3 tables, "events" , "events_per_news_item" , and 'news_items'

I want a list of all events, with an extra column with some indicator if it's active for a specified news_item (this will be for creating a multi-select list).

e.g.

Code: 
event_id  | name             | selected
--------------------------------------
1001      | bjork            | no
3848      | beck             | no
3494      | arctic monkeys   | yes
8214      | css              | no

The query I've come up with only returns the rows with a match in events_per_news_item, but I need a list of every event. The selected column doesn't have to be yes or no, it can just be the news_item_id or null.

Code: 
SELECT 
  events.event_id,
  events.name,
  events_per_news_item.news_item_id AS selected
FROM
  events
  LEFT JOIN events_per_news_item ON events.event_id = events_per_news_item.event_id
WHERE
  events_per_news_item.news_item_id = 8
 
I thought LEFT JOIN was the same as LEFT OUTER JOIN ?

And I thought LEFT JOIN was supposed to return everything in the first table regardless of a match in the second table? :(
 
If I run the query without the WHERE clause it returns all rows in event, but will match the additional column for any news_item_id which isn't what I need.

Maybe I'm coming at it from the wrong side?
 
That's a possible work-around but i'd rather keep the tables normalised for the time being as I would have to implement some kind of trigger script to keep that field up to date.
 
Ok, events_item_per_news_item is an associative table to show which events are linked to a news item.

I need a list of all events, and an additional column with a status of whether they are linked through the events_per_news_item table to a particular news_item_id I specify in the query.
 
Thanks Haircut that's almost perfect - the only problem is, if an event is linked to a news item that isn't the current one, it won't be returned :(
 
Ah, had the same OP on the DevShed forums, and someone there seems to have cracked it - very simple in the end:

Code: 
SELECT 
  events.event_id,
  events.name,
  events_per_news_item.news_item_id AS selected
FROM
  events
  LEFT JOIN events_per_news_item ON events.event_id = events_per_news_item.event_id
  AND events_per_news_item.news_item_id = 8

Simply changing the WHERE to an AND includes it in the LEFT OUTER JOIN and it returns exactly what I needed.

Very grateful for your help though guys, I'm sure you would've nailed it if I'd explained it properly to begin with :)
 
You must log in or register to reply here.
Back
Top Bottom