select from same row different value 1

[stakadush]

hey there!

i have 3 tables.
[ol]
[li]event - list of events[/li]
[li]participant - list of participants[/li]
[li]participant_to_event - linking participants to a given event[/li]
[/ol]


let's say i have three participants with the ids 100, 200 & 300.

i would like to get the event id from participant_to_event where these 3 participants have participated.

is it possible to do in one query?

something like:

SELECT * FROM `event` AS ev STRAIGHT_JOIN `participant_to_event` AS pa
WHERE ev.`event_id`=pa.`event_id` AND (pa.`participant_id`=100 AND pa.`participant_id`=200 AND pa.`participant_id`=300)

which of course doesn't work

also, i don't know how many participants are actually participating in each event...

any ideas?

(-:

 

[r937]

select event_id
  from participant_to_event 
 where participant_id in (100,200,300)
group
    by event_id
having count(*) = 3

r937.com | rudy.ca

 

[stakadush]

works great...thanks!

(-:

 

[stakadush]

i need an improvement for this query, when searching events with only two participants.

i have another field in the participant_to_event table that is called identifier.
let's say the person talking is going to have identifier=1 and the person listening will have identifier=2.
it is possible that i will have 2 or more events with same two participants, only sometimes one will be speaker & the other listener.

so i need to get the event where both participants (100 & 200) participate, but 100's the speaker (identifier=1) and 200's the listener (identifier=2).

is it possible to use this query and add this condition?

(-:

 

[r937]

so i need to get the event where both participants (100 & 200) participate, but 100's the speaker (identifier=1) and 200's the listener (identifier=2).

select event_id
  from participant_to_event 
 where participant_id = 100 and identifier=1
    or participant_id = 200 and identifier=2
group
    by event_id
having count(*) = 2

r937.com | rudy.ca

 

[stakadush]

beautiful...thank you so much!

(-: