SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Addict
    Join Date
    Jan 2004
    Location
    New York
    Posts
    254
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Wondering how to add a conditional in a SELECT query

    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.

  2. #2
    Tranceoholic lilleman's Avatar
    Join Date
    Feb 2004
    Location
    Írebro, Sweden
    Posts
    2,716
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Perhaps something like this could do the trick?

    Code:
    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.
    ERIK RIKLUND :: Yes, I've been gone quite a while.

  3. #3
    SitePoint Addict
    Join Date
    Jan 2004
    Location
    New York
    Posts
    254
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,267
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    Jan 2004
    Location
    New York
    Posts
    254
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    damn that worked like a charm thanks so much. I cant even find info about that word 'outer' in the mysql manual.

  6. #6
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •