Conditional MySQL Query

Discussion in 'Web Design and Development' started by Brendon Bauer, Apr 15, 2011.

  1. Brendon Bauer, Apr 15, 2011
    Last edited: Apr 15, 2011

    Brendon Bauer macrumors 6502

    Joined:
    May 14, 2007
    Location:
    Good 'ol USofA
    #1
    Ok, I have a query right now that pulls all the events out of a table. It works great. However, I want to add one condition to the query... my setup is as follows:

    1. The first table (events) contains the events and their data. It consists of: eventID, eventTitle, eventDateTime, etc.

    2. The second table (events_to_categories) contains: eventID and categoryID. This table just links the events to the categories they belong to. One event can belong to multiple categories (leading to multiple rows in this table with the same eventID).

    My original query is as follows:

    Code:
    SELECT YEAR(`eventDateTime`) as `year`, MONTH(`eventDateTime`) as `month`, DAY(`eventDateTime`) as `day`, `eventID`, `eventDateTime`, `eventTitle`, `eventDescRaw` FROM `events` ORDER BY `year`, `month`, `day` ASC
    I would like to be able to narrow down my results from all events returned to only events with specific categories (assigned to them in the second table). For example, to pull all events assigned to the category id of 1. Or even multiple categories, such as events that are in category 1 or 2... just not all events. How would I go about doing this? For more background on this, here is the original thread (but I figured it was mostly solved and deemed this a separate question): http://forums.macrumors.com/showthread.php?t=1136892

    Thanks in advance! :)
    Brendon
     
  2. Dunmail macrumors regular

    Joined:
    Mar 27, 2009
    Location:
    Skipton, UK
    #2
    SQL uses the keyword WHERE to limit the selection:

    Code:
    SELECT * FROM myTable WHERE category = 0;
    Note the single equals sign. You can join conditions in the usual manner:

    Code:
    SELECT * FROM myTable WHERE category = 0 AND gender = 'm';
     
  3. Brendon Bauer thread starter macrumors 6502

    Joined:
    May 14, 2007
    Location:
    Good 'ol USofA
    #3
    Well, I guess I had already gathered that much :p. I know how to use the WHERE to limit results in one table, but how would I go about using the same query I have but limiting results to WHERE eventID (the same in events and events_to_categories) is listed with categoryID that I have specified.

    Screenshots of example tables:
    http://cl.ly/642g
    http://cl.ly/63Ls

    What if I wanted only events that were assigned to categoryID of 5? Or 3? Or 5 and 3? I'm not really sure how to structure the query...

    Thanks for the response!:)
     
  4. firestarter macrumors 603

    firestarter

    Joined:
    Dec 31, 2002
    Location:
    Green and pleasant land
    #4
    You join the tables in your select.

    select t1.*
    from events as t1,
    events_to_categories as t2
    where t1.eventID=t2.eventID
    and t2.categoryID = 5

    (could change that last line to 'and (t2.categoryID=5 or t2.categoryID=3)'


    (NB: I'm more used to Sybase than mySql, but I think this syntax should work)
     
  5. Brendon Bauer thread starter macrumors 6502

    Joined:
    May 14, 2007
    Location:
    Good 'ol USofA
    #5
    Thanks, that worked great! A lot more simple then I was expecting...
     

Share This Page