Geek asks and answers

Query to retrieve records matching another table?

Using SQL Server 2008, I have a table abc and xyz

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 abc_id where its location xyz_id1 is equal to the address xyz_id2. (park == park). xyz_id1 and xyz_id2 will always be the minimum and maximum sequence number in the xyz table, so probably the query will use max (sequence_id).

In this example, it will return "1". (this will return a lot of records, not just a scalar value).

There is some business logic in my code that I would prefer not to use. Can anyone help? I'm sure I need a subquery or a temporary table?

+3
source share
3 answers
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 GROUPING.

+2

, 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'
0
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
0

More articles:


All Articles