0

When I run the following query, I would like to include a field that returns the COUNT of the number of records in the sub query. The sub query however has a WHERE clause where a field in this clause must match a field in the parent query's SELECT statement:

SELECT id, firstName, lastName, 
COUNT(
   SELECT userId, address FROM UserDetails WHERE userId = <id_from_outer_select_statement>
)
FROM Users

It isn't clear to me how to apply the id field from the outer SELECT statement to the sub query.

forpas
  • 160,666
  • 10
  • 38
  • 76
Johann
  • 27,536
  • 39
  • 165
  • 279

1 Answers1

2

Give an alias to the table Users and use it to qualify the column id in the subquery:

SELECT u.id, u.firstName, u.lastName, 
  (SELECT COUNT(*) FROM UserDetails WHERE userId = u.id) counter
FROM Users u
forpas
  • 160,666
  • 10
  • 38
  • 76
  • Is there a difference, at least in performance to your solution and the one by b.Muthamizhselvi? His places the COUNT outside the SELECT while yours is inside of it. – Johann Jul 10 '19 at 07:18
  • It is syntactically wrong to use count outside a query like that. – forpas Jul 10 '19 at 07:19
  • I came across this solution which is similar to yours although for MySql (not sure if it applies to Sqlite): https://stackoverflow.com/questions/1973246/how-to-specify-the-parent-query-field-from-within-a-subquery-in-mysql – Johann Jul 10 '19 at 07:34
  • It does apply to sqlite because it is standard sql. It is the same way I used select count(*) as an embedded query. – forpas Jul 10 '19 at 07:36
  • @forpas,Now why you edited your answer?. You posted wrongly as userId = – B.Muthamizhselvi Jul 10 '19 at 10:17
  • Because < was a typo, copy/paste from the OP's question and not copy/paste from someone else's answer. – forpas Jul 10 '19 at 10:19