Conditional MySQL Query

[Brendon Bauer]

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:

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): https://forums.macrumors.com/threads/1136892/

Thanks in advance!
Brendon

 

[Dunmail]

SQL uses the keyword WHERE to limit the selection:

SELECT * FROM myTable WHERE category = 0;

Note the single equals sign. You can join conditions in the usual manner:

SELECT * FROM myTable WHERE category = 0 AND gender = 'm';

 

[Brendon Bauer]

Well, I guess I had already gathered that much . 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!

 

[firestarter]

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)

 

[Brendon Bauer]

Thanks, that worked great! A lot more simple then I was expecting...