[SOLVED] Semi-complex m-t-m query?

[Chotor]

I'm trying to build a (for me) complex query in PHP.

On my way there, I'm looking for how to do this query:

 

I have a many-to-many table

event_id|person_id
1 | 1
1 | 3
1 | 13
1 | 15
1 | 22
1 | 23
2 | 1
2 | 4
2 | 5
2 | 7
2 | 13
2 | 21
2 | 27
2 | 33
3 | 2
3 | 5
...

How do I select the event_id (return only this single value) where person_id is 13 AND 23 (and even an arbitrary sequence of person_ids).

For example:

where person_id is 1 AND 13 (should return both 1 and 2)

where person_id is 1 AND 13 AND 23 (should return only 1)

where person_id is 4 AND 21 AND 27 AND 33 (should return only 2)

 

There's more to come if I solve this one.

[nadeemshafi9]

$sql = "SELECT * FROM table WHERE person_id = 13 AND person_id = 23;";

 

$result = mysql_query($sql);

 

while($person = mysql_fetch_array($result)){

    echo $person["person_id"]." - ".$person["event_id"];

}

[rarebit]

I'm thinking you'll want to use some kind of group feature, not sure if it'll work but... whilst I finish my sarnie i'll 'ave a go... might be latter though!

[Chotor]

$sql = "SELECT * FROM table WHERE person_id = 13 AND person_id = 23;";

 

I understand what you're thinking, but this does not work (because no single row containt BOTH 13 and 23.. they are separate rows).

[rarebit]

Sorry, my times up for now, this is where I was going...

$s = "SELECT CONCAT_WS(CAST(x AS CHAR), " ", CAST(y AS CHAR)) as z FROM test_math HAVING (z = "1 13")";

but it doesnt work, but

[nadeemshafi9]

i think you have two tables and its a one to many relation, a many to many relation has 3 tables or a coma list ids

 

SELECT p.person_id, p.person_name, e.event_id, e.event_name FROM person_table p , event_table e WHERE e.person_id = p.person_id AND p.person_id = 1 OR p.person_id = 13;

 

 

you may need to use AS new_feild_name or $row[1].$row[2] etc in order to output in php

[Chotor]

What if I have a movie table and an persons table (that contain actors, directors, writers and producers) ?

Wouldn't I then need to have a table connecting the actors from the person table to movies?

 

I really don't want to change the layout of the database at this point...

 

[PFMaBiSmAd]

For your original question, see my post in this thread - http://www.phpfreaks.com/forums/index.php/topic,200534.msg906719.html#msg906719

 

The value being tested in the HAVING clause needs to be set to the number of person_id's you are selecting. There might be a way to do this in the query, but that is beyond my sql ability. If you are building the query string using php, it would be simple to build the IN(....) clause and put the correct number into the HAVING clause.

 

For some of your examples the queries would look like -

 

SELECT event_id, COUNT(*) as cnt FROM your_table
WHERE person_id IN(13,23)
GROUP BY event_id HAVING cnt = 2

 

SELECT event_id, COUNT(*) as cnt FROM your_table
WHERE person_id IN(4,21,27,33)
GROUP BY event_id HAVING cnt = 4

[Chotor]

Thanks, that's exactly what I wanted!

You are the man!

I've played with it, and connected the person.id with person.name so that I can select person.name='John Doe' and not only person.id=2.

 

Thanks. Works like a charm.