mysql intersect help

mysql intersect help

am 10.05.2007 06:50:35 von prathamesh.deshpande

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

Re: mysql intersect help

am 10.05.2007 16:24:59 von lark

prathamesh.deshpande@gmail.com wrote:
> 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) )




--
lark -- hamzee@sbcdeglobalspam.net
To reply to me directly, delete "despam".

Re: mysql intersect help

am 14.05.2007 07:44:25 von prathamesh.deshpande

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

Re: mysql intersect help

am 14.05.2007 19:03:59 von zac.carey

On May 14, 6:44 am, prathamesh.deshpa...@gmail.com wrote:
> 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