Subquery help 1

[guitarzan]

Following is a simplified version of something I'm struggling with at the moment. Let's say I have the following two tables. tblMain has some information, and its Primary key is "id". tblComments has a field called recid which matches tblMain.id. In this sample, Tom has 3 comments, Pete has 1, and Joe has none.

[b]tblMain:[/b]
id       Name
=============
1        Tom
2        Joe
3        Pete

[b]tblComments:[/b]
id       recid      CommentText
===============================
1        1          Tom's first comment        
2        1          Tom's second comment
3        1          Tom's third comment
4        3          Pete's only comment

I would like to create a query that returns all the records in tblMain, plus a column that contains "Yes" or "No" depending on whether that record has any "comment" records in tblComments, like this:

id       Name       HasComments
===============================
1        Tom        Yes
2        Joe        No
3        Pete       Yes

Can someone help with the nested select needed?

 

[PHV]

One way (from many ...):
SELECT M.id, M.Name, 'Yes' As HasComments
FROM tblMain AS M INNER JOIN tblComments AS C ON M.id = C.recid
GROUP BY M.id, M.Name
UNION ALL SELECT M.id, M.Name, 'No'
FROM tblMain AS M LEFT JOIN tblComments AS C ON M.id = C.recid
WHERE C.recid Is Null
ORDER BY 1

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[guitarzan]

Thanks PHV, that's a great solution.

I have another question, more for my own education than to solve this problem (which you helped solve for me)... since there are many solutions to this, is there a way to use a nested select statement, something like this;

SELECT M.id, M.Name, xxxxxxxx FROM (table names/multiple join statements/where clause etc)

where xxxxxxxxx is another SELECT statement that returns the Yes or No (or alternately, return the count of records)

id       Name       TotalComments
===============================
1        Tom        3
2        Joe        0
3        Pete       1

 

[PHV]

Perhaps something like this:
SELECT M.id, M.Name, Count(C.recid) AS TotalComments
FROM tblMain AS M LEFT JOIN tblComments AS C ON M.id = C.recid
GROUP BY M.id, M.Name

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[guitarzan]

Excellent... I was trying something similar, but not being used to aggregate functions (yet), I missed the Group By...

Thanks very much!