Aaron_H Aaron_H - 5 months ago 6
SQL Question

Integrate nested SELECT query when querying for objects in same table?

Say I have a table Table1 constructed similar to below, where * is Key

ID* GROUPID* ATTR1 ATTR2 (...additional columns)
-- ------- ----- -----
1 100 45 89
2 100 37 27
3 101 69 19
4 102 37 22


And given a pair of values for ATTR1 and ATTR2, I want my query to return a list of all rows that belong to the GroupID for a given ATTR1 and ATTR2's GROUPID. Assume for each pair of ATTR1 and ATTR2 will have zero or one GroupID.

Is there a way to formulate this query to not have a nested query (and therefore speed it up)? Here is the query I have so far:

SELECT [GROUPID], [ATTR1], [ATTR2]
FROM [TABLE1]
WHERE [GROUPID] = (
SELECT TOP 1 GROUPID
FROM [TABLE1]
WHERE (ATTR1 = @attr1) and (ATTR2 = @attr2)
);

Answer

Your query should be fine. However, you can guarantee one execution of the subquery by moving it to the FROM clause:

SELECT t1.GROUPID, t1.ATTR1, t1.ATTR2
FROM TABLE1 t1 JOIN
     (SELECT TOP 1 GROUPID
      FROM [TABLE1]
      WHERE (ATTR1 = @attr1) and (ATTR2 = @attr2)
     ) tg
     ON t1.GROUPID = tg.GROUPID;

For performance, you want two indexes: TABLE1(ATTR1, ATTR2, GROUPID), and TABLE1(GROUPID).

Comments