How do i count this mysql UNION query

Question

This is my query, i'm kinda still getting a hang of mysql joins. i would like to get the count of this query....

SELECT *
FROM `users`
WHERE id = '4'
UNION
SELECT *
FROM `users`
WHERE id IN 
(SELECT group_id 
FROM `users` 
WHERE id = '4')
AND status = '1'

The answers below should suffice then, but as Jim has commented, should the second part of the union be `WHERE Level IN (SELECT level FROM...` and not `WHERE id IN (SELECT level FROM...` ? — Matt, Nov 10 '17 at 21:31

score 0 · Accepted Answer · answered Nov 10 '17 at 21:22

0

You can count the subquery

  select count(*) from (

  SELECT *
  FROM `users`
  WHERE id = '4'
  UNION
  SELECT *
  FROM `users`
  WHERE id IN 
  (SELECT level 
  FROM `users` 
  WHERE id = '4')
  AND status = '1') t

answered Nov 10 '17 at 21:22

ScaisEdge

131,976
10
91
107

score 0 · Answer 2 · answered Nov 10 '17 at 21:29

I agree with scaisEdge on the code to use. However, what are you trying to do here in the subquery? This seems to look for ID, but you selected 'Level' for the IN statement? Unless LEVEL is the same as ID, it won't do anything.

SELECT *
FROM `users`
WHERE id IN 
(SELECT level 
FROM `users` 
WHERE id = '4')
AND status = '1'

How do i count this mysql UNION query

2 Answers2