Query to return fewer rows

Databases
1

Hello everyone,

one of my tables has an autoincremented ID “event_id” and an “event_category_id” ID. The same “event_category_id” ID can appear multiple times, thus “event_id”'s 18,19 and 25 can all have an “event_category_id” ID of 9. Normally the query would return all "event_id"s but it can happen that "event_id"s 18 and 19 are unavailable, so only ID 25 must be returned.

The pseudo query I came up with looks like this:

WHERE event_category_id = 9 (but return only 1 row instead of 5) AND event_category_id = 15 (but return only 3 rows instead of 4)

Anyone have an idea how I can make this work?

Thank you in advance!

2

how do you determine that an event ID is “unavailable”?

3

That’s a good question. How do you know that an event is unavailable? Because you’ll need to add that constrain to your code.

Also,is it possible that an event has two event_category_id’s at the same time? I’m wodering your AND clause :slight_smile:

4

If all the tickets for the event_id 18 and 19 are sold out (= 1), then put that in a sold_out column and do this:

where
  sold_out = 0 
  and (event_category_id = 9 or event_category_id = 15)
5

i am ~so~ confused

we really do need to see the CREATE TABLE statements, along with sample data, especially sample data that covers what happened to “unavailable” events

6

Hi all,

sorry for any confusion. The problem appears to be solved. Those event ids that are unavailable (they become unavailable when selected by a user) are in an array, which are then used in a WHERE NOT IN clause.

Anyway, it’s working fine. Thanks for your help!