[SQL] Join question

LazyManc · 14 Aug 2007 at 10:24

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

WrathOfAchilles · 14 Aug 2007 at 10:37

Outer join?

LazyManc · 14 Aug 2007 at 10:49

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?

WrathOfAchilles · 14 Aug 2007 at 10:52

Googled, your right.

Hmm...

LazyManc · 14 Aug 2007 at 10:56

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?

WrathOfAchilles · 14 Aug 2007 at 11:02

If time becomes an issue, you could add a flag column to events and populate it where there is a match in the events_per_news_item table. You could do your selections using that flag then.

WrathOfAchilles · 14 Aug 2007 at 11:04

Confused!

I assumed the where clause was an extra condition you needed but didn't mention.

What is it exactly you need?

LazyManc · 14 Aug 2007 at 11:05

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.

Haircut · 14 Aug 2007 at 11:06

Can you post a bit more info about your tables, such as all the fields and how they join?

LazyManc · 14 Aug 2007 at 11:07

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.

LazyManc · 14 Aug 2007 at 11:09

It's for a news_item edit page, I want a multi-select box with any associated events already highlighted.

Haircut · 14 Aug 2007 at 11:12

OK, try replacing the WHERE clause with:

Code:

WHERE
  (events_per_news_item.news_item_id = 8
   OR events_per_news_item.news_item_id IS NULL
  )

As you're doing the outer join the news item should be populated with 8 (or whatever you've chosen) for those events which have a news item and NULL for others, you just need to include the NULL part in your WHERE statement.

You can use ISNULL, NVL or whatever operator does the appropriate NULL replacement function for your db if you like.

You can then use a CASE statement in the SELECT to display yes or no correctly.

LazyManc · 14 Aug 2007 at 12:04

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

Haircut · 14 Aug 2007 at 12:29

LazyManc said:
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, yes I see what you mean now.

In that case can't you just remove the WHERE clause entirely and then use a CASE statement in the SELECT clause to set the selected indicator, something like:

Code:

SELECT 
  events.event_id,
  events.name,
  CASE events_per_news_item.news_item_id 
	WHEN 8, 'Yes'
	ELSE 'No'
  END AS selected
FROM
  events
  LEFT JOIN events_per_news_item ON events.event_id = events_per_news_item.event_id

Then, rather than putting your news_item_id in the WHERE clause it just goes in the CASE statement and you get the correct value from there.

Only thing with that is, if you have more than one news item for an event you will get duplicate rows.

Would you just want one entry in this case, or are multiples OK?

LazyManc · 14 Aug 2007 at 13:32

I need it without the multiple rows - I suppose I could strip them out in PHP afterwards.

LazyManc · 14 Aug 2007 at 13:41

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