Query to get records matching another table?

Using SQL Server 2008, I have abc and xyz table

abc

abc_id  | xyz_id1 | xyz_id2
---------------------------
 1     |  foo123  |  foo125
 2     |  foo127  |  foo129

xyz table

xyzid      | abc_id | location   | sequence_id
------------------------------------------
foo123     |  1     | park       | 1
foo124     |  1       mall       | 2
foo125     |  1     | park       | 3
foo127     |  2     | restaurant | 1
foo128     |  2     | lake       | 2
... -- several xyz records for order 2
foo130     |  2     | mall       | 5

I need to get all the abc_id where its xyz_id1's location is equal to xyz_id2's loca开发者_运维百科tion. (park == park). xyz_id1 and xyz_id2 will always be the min and max sequence number in xyz table, so there'll probably make use of max(sequence_id) somewhere in the query.

In this example, it would return '1'. (this would return many records, not just a scalar value).

My code has some business logic I'd rather not share. Could someone provide some assistance? I'm pretty sure I need some subquery or temporary table?

SELECT abc_id
FROM abc
INNER JOIN xyz1 ON abc.xyz_id1=xyz1.xyzid
INNER JOIN xyz2 ON abc.xyz_id2=xyz2.xyzid
WHERE xyz1.location=xyz2.location

This way you avoid the GROUPING.

Join the table twice, once for each xyz_id:

select  *
from    abc
join    xyz as xyz1
on      xyz1.xyzid = abc.xyz_id1
join    xyz as xyz2
on      xyz2.xyzid = abc.xyz_id2
where   xyz1.location = xyz2.location
        and xyz1.location = 'park'

SELECT abc_id FROM abc a1 INNER JOIN xyz x1 ON a1.xyz_id1 = x1.xyzid INNER JOIN xyz x2 ON a1.xyz_id1 = x2.xyzid WHERE x1.location = x2.location