mysql intersect help

[p***@gmail.com]

Hi,
I am facing problem while excuting a mysql query. My problem is as
follows-
I have two tables object_1 and object_2.
The structure of object_1 is

object_id name address
------------ ---------- ------------
1 abc xyz
2 pqr xyz
3 lmn zzz

The structure of object_2 is

object_id tag_id
-------------- ----------
1 2
1 3
2 2

object_id is the foreign key in the table object_2

I want to find all the rows from object_1 whose tag_id is 2 and 3.
ie my result row will be

object_id name address
------------ ---------- ------------
1 abc xyz

So technically what i want to do is an intersect.But since mysql
doesnt support intersect i am not able to do it
Can anyone suggest how to go about it?
Thanks

[lark]

Post by p***@gmail.com
Hi,
I am facing problem while excuting a mysql query. My problem is as
follows-
I have two tables object_1 and object_2.
The structure of object_1 is
object_id name address
------------ ---------- ------------
1 abc xyz
2 pqr xyz
3 lmn zzz
The structure of object_2 is
object_id tag_id
-------------- ----------
1 2
1 3
2 2
object_id is the foreign key in the table object_2
I want to find all the rows from object_1 whose tag_id is 2 and 3.
ie my result row will be
object_id name address
------------ ---------- ------------
1 abc xyz
So technically what i want to do is an intersect.But since mysql
doesnt support intersect i am not able to do it
Can anyone suggest how to go about it?
Thanks

have you tried something like this:

select
object_1.object_id,
object_1.name,
object_1.address
from object_1
where object_1.object_id in (select object_id from object_2 where tag_id
in (2,3) )

[p***@gmail.com]

Hi,
Yes i tried it. It is not working. This is because in essentially
behaves like or

[strawberry]

Post by p***@gmail.com
Hi,
Yes i tried it. It is not working. This is because in essentially
behaves like or

Guys, guys

Stop using this subselect rubbish and read up on JOINS instead

SELECT t1. *
FROM object_1 t1
JOIN object_2 t2a ON t1.object_id = t2a.object_id
AND t2a.tag_id =2
INNER JOIN object_2 t2b ON t1.object_id = t2b.object_id
AND t2b.tag_id =3