sql SELECT question

ViCT0R

Hi all,

I need some help with a quite basic select query. I've been trying to find out how to do it, without luck 🙁

I have this table called "relation" which has these items:

id id_prop id_equip
1 0 3
2 0 4
4 0 8
5 0 10
6 1 8
7 1 11

8 2 5
9 2 11

The query I need, has to find out the id_prop that has the selected id_equip's. Some examples:

Who has id_equip 3 and 4? result: 0 (from id_prop)
Who has id_equip 8? result: 0 and 1
Who has id_equip 11? result: 1 and 2
Who has id_equip 8 and 11? result: 1

SELECT id_prop FROM relation .... ?

Thanks a lot in advance 🙂

Victor

_SuperString

you could use a sub query (limited types are available in MySQL). Try this:

SELECT id_prop
FROM    relation
WHERE id_equip
IN (3, 4)

you need to comma seperate the desired values in the IN() part.

hope this is what you need.

ViCT0R

Yes! That's exactly it!
Thanks so much 🙂

Victor

ViCT0R

Uhm, no ... wait.

This:

SELECT id_prop
FROM    relation
WHERE id_equip
IN (8, 11)

would answer: 1 and 2
where I need it to answer just: 1
because 2 doesn't have id_equip=8

it that possible?

Thanks again,

Victor

Wien

Well, it's not so easy but I think this works :

select id_prop, count(id_prop)
from relation
where id_equip in (8,11)
group by id_prop
having count(id_prop) > 1;

BUT if you want to check for three equip, ( id_equip in (8,11,15) ) you need to modify the HAVING COUNT ( having count(id_prop) > 2 )

ViCT0R

That looks like the solution! 🙂

Quite an original method but it works like a charm, hehe.

Thanks for your time Wien,

Greetings

Víctor