[Resolved] what to use instead of subquery?

hakano

hi all,

i have a problem which i think has an easy solution but i couldn't find it:

i have 3 tables, normalised.
one holds people:
tb_people(user_id, name, phone, bla bla bla)
the other holds equipments:
tb_equip(equip_id, eq_name bla bla bla)
and the third table has the links of the equipments to people:
tb_p_e(user_id, equip_id)

i want to select the names of people which for example have equipments 4 AND 7. it works for people with equipments 4 OR 7.

this works :

SELECT DISTINCT name FROM tb_people, tb_p_e WHERE tb_p_e.user_id=tb_people.user_id AND (equip_id=4 OR equip_id=7);

but it doesnt work for AND (of course it doesnt because it cannot select a row from tb_p_e where equip_id cannot be 4 and 7 in the same row).

i know that i can get what i want with subqueries but the server which i am working is not controlled by me and it has an old version of mysql which doesnt support subQs.

any help would be most appreciated.

mrhappiness

either

SELECT p.user_id, p.name
FROM
  tb_p_e pe1 
  INNER JOIN tb_people p ON pe1.user_id = p.user_id
    INNER JOIN tb_p_e pe2 ON p.user_id = pe2.user_id
WHERE
  pe1.equip_id = 4
  AND pe2.equip_id = 7

SELECT 
  p.user_id, p.name, 
  COUNT( p.user_id ) has_both
FROM 
  tb_people p
  INNER JOIN tb_p_e pe USING (user_id)
WHERE 
  pe.equip_id =4
  OR pe.equip_id =7
GROUP BY 
  p.user_id, p.name
HAVING 
  has_both =2

the 1st example should be faster

hakano

thank you very much mrhappiness.

both examples work fine but i will use the second one (even it is slower).

in my problem i might generate the sql for 2 equipments or even 15 equipments. that depends on the users selection which is in my case a posted array from another page.

it is easier for me to generate an sql in the form of second example where i have unknown number of equipments. (and i also think that the second one "might" be faster when the filter is based on multiple equipments)

thanks again for your reply. (it helped me much)