Wondering how to add a conditional in a SELECT query

[Atealtha]

here are my two tables

table: radio
radioshow_id
name

table: radio2staff
radioshow_id
staff_id

I want to get the entire list of table 'radio' with an additional column that tells me if a certain staff (staff_id) is in the radioshow. 1 if the staff is in, and 0 if it is not. I know to get a complete list of radio, I need to do a LEFT JOIN. But I don't know how to put in a conditional statement in the query.

I'm using MySQL 4.0.

[lilleman]

Hi,

Perhaps something like this could do the trick?

SELECT radio.*, IF(staff.staff_id IS NULL, 0, 1) AS in_show
FROM radio LEFT JOIN radio2staff as staff
  ON staff.radioshow_id = radio.radioshow_id

Yours, Erik.

[Atealtha]

well my function has a staff_id parameter in it. So I'll try that with IF(staff.staff_id = $sid ...)

update, I tried the query but it shows all rows with all in_show columns showing 1. I checked and it should not be.

[r937]

select radio.radioshow_id
     , radio.name
     , case when staff.staff_id is null
            then 0 else 1 end as in_show
  from radio 
left outer
  join radio2staff as staff
    on radio.radioshow_id 
     = staff.radioshow_id      
   and staff.staff_id = 937

[Atealtha]

damn that worked like a charm thanks so much. I cant even find info about that word 'outer' in the mysql manual.

[guelphdad]

Here is an explanation on LEFT JOIN in the manual (right joins are included as well).

http://dev.mysql.com/doc/mysql/en/LE...imization.html

Thought that might help you understand it clearly. The word OUTER is optional but when you put it in your query and use the optional word INNER for an inner join, it helps you figure out how the join is working when you look at old code or someone else looks at your code.

From the Join Syntax part of the manual they offer this explanation

The { OJ ... LEFT OUTER JOIN ...} syntax shown in the preceding list exists only for compatibility with ODBC.

Here is a link to that page in the manual:
http://dev.mysql.com/doc/mysql/en/JOIN.html

Hope that helps clarify for you or others following.