MIrrorMirror MIrrorMirror - 3 years ago 75
SQL Question

turn two sql queries into one with a subquery: top 1 row of query from subquery

I have the following table structure:

wwwID eeeID www1 eee1 property1 property2
1 1 aa2 ddd1 bbb1 adfsdfs
2 1 aa1 ddd1 bbb2 lasdkjfs
3 1 aa3 ddd1 bbb3 doafijsd
4 1 aa1 ddd1 bbb4 doifjdoifs
5 2 bb2 ddd2 ccc1 dofidsjfois
6 2 bb3 ddd2 ccc2 peowrpoerwe
7 2 aa1 ddd2 ccc3 dpofsdopfsd


my first query is:

SELECT DISTINCT eeeID FROM tInf WHERE www1='aa1'


this query returns:

eeeID
1
2


then I pass the results to a second query:

SELECT * FROM tInf WHERE eeeID='" & eeeID & "' LIMIT 1


(TOP 1 in mysql syntax)

final result:

eeeID, eee1, property1, property2:
1, ddd1, bbb1, adfsdfs
2, ddd2, ccc1, dofidsjfois


I tried combining this into one query

SELECT * FROM tInf WHERE eeeID IN (
SELECT DISTINCT eeeID FROM tInf WHERE www='aa1'
) LIMIT 1


but this apparently only brings one total result, not one result per subquery result. Is there any way of doing this in a single query ?

Thank you in advance

CL. CL.
Answer Source

You want one output row for each eeeID group; this is done with GROUP BY.

SQLite allows bare columns in aggregate queries; you can use MIN() to get the row with the smallest value in the group, or just do not use MIN() to get some random row from the group:

SELECT *,
       MIN(wwwID)  -- optional
FROM tInf
WHERE eeeID IN (SELECT eeeID
                FROM tInf
                WHERE www1 = 'aa1')
GROUP BY eeeID;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download