0

Is it possible to do this query WITHOUT using a subselect?

tables

  • user:
  • id
  • name

value_type_1

  • id
  • user_id
  • value

value_type_2

  • id
  • user_id
  • value

I would like a sql query that would return:

id | name | Count(value_type_1) | Count(value_type_2)
1    foo     5                     2
2    bar     3                     7
n    etc..   x                     y
Community
  • 1
  • 1
o0ONeoNO0o
  • 3
  • 1

1 Answers1

1

Yes.

You can use a join

select user.id, user.name, count(distinct value_type_1.id),count(distinct value_type_2.id)
from user
   left join value_type_1 on user.id = value_type_1.user_id
   left join value_type_2 on user.id = value_type_2.user_id
group by user.id, user.name
podiluska
  • 50,950
  • 7
  • 98
  • 104
  • This answer is correct, with the reasonable assumption that "id" is unique on each table. The comment on the below solution is wrong. Although the query might generate a cross product, the distinct undoes that. – Gordon Linoff Aug 22 '12 at 13:16
  • I have used a join, but missed the distinct. Thank you, that was a big help! – o0ONeoNO0o Aug 22 '12 at 17:45