[SOLVED] Relational table query help needed

[MarioRossi]

Say I have a relational table as in the attached image.

 

My current query is....

 

SELECT DISTINCT pty_att_rel.id_pty FROM pty_att_rel WHERE id_att IN (2,7)

 

which returns 2 rows - id_pty equaling 1 and 4.

 

What I want to achieve is to return only those rows where id_att equals 2 AND 7.

 

If I alter the query to "WHERE id_att = 2 AND id_att = 7" obviously this cannot work.

 

Any suggestions?  

 

[attachment deleted by admin]

[Illusion]

Three solutions

1.

SELECT DISTINCT pty_att_rel.id_pty FROM (SELECT DISTINCT pty_att_rel.id_pty from pty_att_rel WHERE id_att=2) where id_att=7;

2.a self join

3.UNION also works too.

[MarioRossi]

Thanks for your help, I have researched UNION but from what I can tell I need to use INTERSECT instead. A UNION would still return the results of both seperate queries.

 

From what I can see INTERESECT is not supported with MySQL so I think I need an alternative method of doing this.

 

Once I get this resolved I will need to do more than check for 2 values I may have to check against 2,7,9,13,25 etc so I need a solution that will allow this without too much of a performance hit.

 

Thanks again.

[Illusion]

sorry , union doesn't solve your problem, my first solution works when when you want compare with 2 values when we have multiple values we have too look around.

[MarioRossi]

OK I think I've got a working solution, though I had hoped for something a little more elegant.

 

SELECT DISTINCT pty_att_rel.id_pty FROM pty_att_rel

WHERE id_pty IN (SELECT DISTINCT pty_att_rel.id_pty FROM pty_att_rel WHERE id_att = 7)

AND id_pty IN (SELECT DISTINCT pty_att_rel.id_pty FROM pty_att_rel WHERE id_att = 2)

AND id_pty IN (SELECT DISTINCT pty_att_rel.id_pty FROM pty_att_rel WHERE id_att = 3)

 

this means for every id_att I want to check against I will have to add another AND to the WHERE condition, can you see a more elegant solution or should I use this? Am I likely to hit any performance issues? I should say I will probably have about 10-15 of these conditions at the most and will be doing a few left joins to the returned rows to retrieve some extra info.

[Illusion]

Assuming that you don't have any duplicate records

 

select id_pty,count(*) as cnt  from pty_att_rel where id_att IN(7,5,3) GROUP BY id_pty HAVING cnt=3;

 

we need to compare cnt with no of values in 'IN' condtion

 

I think, its just a hack.

[MarioRossi]

Great stuff. I think i'll give that a go, I see no reason why it shouldn't work so thanks very much for your help - great solution!