Welcome to the MacNN Forums.

John LaPlante · May 19, 2003, 04:38 PM

I have a many-to-many junction table that matches our computer labs to the equipment that is contained in each lab. The table only has 2 fields, lab_id and equipment_id. I'm having a problem composing a sql query to find a lab that has specific equipment. For example, if I want to find a lab that has equipment_id=2 AND equipment_id=3, how do I write it? I tried "SELECT lab_id FROM lab_equipment WHERE equipment_id=2 AND equipment_id=3" but it doesn't work, for obvious reasons. I've tried to query the main labs table as such "SELECT labs.lab_id FROM labs, lab_equipment WHERE labs.lab_id=lab_equipment.lab_id AND lab_equipment.equipment_id=2 AND lab_equipment.equipment_id=3" but that doesnt' work either. Any ideas? Thanks!!

John

Simon Mundy · May 19, 2003, 05:00 PM

Originally posted by John LaPlante:
I have a many-to-many junction table that matches our computer labs to the equipment that is contained in each lab. The table only has 2 fields, lab_id and equipment_id. I'm having a problem composing a sql query to find a lab that has specific equipment. For example, if I want to find a lab that has equipment_id=2 AND equipment_id=3, how do I write it? I tried "SELECT lab_id FROM lab_equipment WHERE equipment_id=2 AND equipment_id=3" but it doesn't work, for obvious reasons. I've tried to query the main labs table as such "SELECT labs.lab_id FROM labs, lab_equipment WHERE labs.lab_id=lab_equipment.lab_id AND lab_equipment.equipment_id=2 AND lab_equipment.equipment_id=3" but that doesnt' work either. Any ideas? Thanks!!

John

SELECT lab_id FROM lab_equipment WHERE equipment_id=2 OR equipment_id=3

John LaPlante · May 19, 2003, 05:10 PM

thanks for your post...but won't this give me labs that have either equipment? I need a query that will tell me which labs have BOTH sets of equipment...

Simon Mundy · May 19, 2003, 06:26 PM

Originally posted by John LaPlante:
thanks for your post...but won't this give me labs that have either equipment? I need a query that will tell me which labs have BOTH sets of equipment...

Ahhh. I see what you mean, sorry.

You need to create joins for as many items of equipment as you wish to compare. That is, if you wanted equip=1 and equip=2 then you must create 2 joins of the same table. That way you can create a query similar to this:

SELECT e1.lab_id FROM labs AS e1
LEFT JOIN labs AS e2
ON e1.lab_id = e2.lab_id
WHERE e1.equipment_id = 1 AND e2.equipment_id = 2

So in effect, you are now comparing a multi-dimensional array of your table to see where the intersection is of these equipment values. Just keep adding joins for each extra piece of equipment you want

SELECT e1.lab_id FROM labs AS e1
LEFT JOIN labs AS e2 ON e1.lab_id = e2.lab_id
LEFT JOIN labs AS e3 ON e1.lab_id = e3.lab_id
WHERE e1.equipment_id = 1 AND e2.equipment_id = 2 AND e3.equipment_id = 3;

Does that help? I found this handy bit of code on the MySQL searchable documentation - there's some gold in there if you've got some time and patience.

John LaPlante · May 19, 2003, 06:39 PM

You are a life-saver! I remember reading something about this, but couldn't see the relevance. Thanks so much. MacNN to the rescue again!

Thanks so much,

John

Arkham_c · May 20, 2003, 10:05 AM

I believe you could also do:

SELECT lab_id FROM lab_equipment WHERE equipment_id=2
INTERSECT
SELECT lab_id FROM lab_equipment WHERE equipment_id=3

Simon Mundy · May 20, 2003, 04:50 PM

Originally posted by Arkham_c:
I believe you could also do:

SELECT lab_id FROM lab_equipment WHERE equipment_id=2
INTERSECT
SELECT lab_id FROM lab_equipment WHERE equipment_id=3

I couldn't find INTERSECT anywhere in the MySQL manual and that query returned a syntax error

Arkham_c · May 20, 2003, 05:03 PM

Originally posted by Simon Mundy:
I couldn't find INTERSECT anywhere in the MySQL manual and that query returned a syntax error

MySQL is not a full SQL implementation. I didn't realize that it did not include INTERSECT. It works fine with PostgreSQL or Oracle or Sybase.

You could also do this:

SELECT lab_id FROM lab_equipment WHERE equipment_id=2 AND lab_id IN (
SELECT lab_id FROM lab_equipment WHERE equipment_id=3)

I verified that this works with PostgreSQL on my laptop, but I don't have MySQL installed to test with.

Simon Mundy · May 21, 2003, 05:02 AM

Originally posted by Arkham_c:
MySQL is not a full SQL implementation. I didn't realize that it did not include INTERSECT. It works fine with PostgreSQL or Oracle or Sybase.

You could also do this:

SELECT lab_id FROM lab_equipment WHERE equipment_id=2 AND lab_id IN (
SELECT lab_id FROM lab_equipment WHERE equipment_id=3)

I verified that this works with PostgreSQL on my laptop, but I don't have MySQL installed to test with.

Nope... didn't work either. There is a UNION command for MySQL 4.x but I haven't had time to explore it yet.

Arkham_c · May 21, 2003, 09:50 AM

Originally posted by Simon Mundy:
Nope... didn't work either. There is a UNION command for MySQL 4.x but I haven't had time to explore it yet.

UNION won't help in this case. UNION combines the results of two queries, not the intersection of the results of two queries.

If you find yourself outgrowing MySQL, you might consider PostgreSQL as a next step. It's a full SQL implementation and supports transactions out of the box (you can get transactions with MySQL too I believe, but it's not included by default).