CODEWITHSUNDEEP
mysqljoin
jerrygarciuh
jerrygarciuh

Reputation: 21988

Combining these two queries into a JOIN?

These two queries work:

SELECT
u.id,
u.email,
COUNT(*) as Current
FROM
users u,
rounds cur
WHERE
cur.user = u.id
AND u.email = '[email protected]'
GROUP BY cur.user;

SELECT
u.id,
u.email,
COUNT(*) as old
FROM
users u,
rounds__20120311_010951 old
WHERE
old.user = u.id
AND u.email = '[email protected]'
GROUP BY old.user;

But I really want to do this:

SELECT
u.id,
u.email,
COUNT(old.*) as March11,
COUNT(cur.*) as Current
FROM
users u,
rounds cur,
rounds__20120311_010951 old
WHERE
old.user = u.id
AND cur.user = u.id
AND u.email = '[email protected]'
GROUP BY old.user, cur.user

I think the syntax error is from this COUNT(old.*) but I don't know for sure.

Is it possible to combine those queries?

Upvotes: 0

Views: 65

Answers (3)

bfavaretto
bfavaretto

Reputation: 71908

Use GROUP BY u.id, u.email. Then, in your select, use COUNT(old.id) and COUNT(cur.id).

Upvotes: 2

Alex L
Alex L

Reputation: 6492

Maybe it should looks like:

SELECT
  u.id,
  u.email,
  COUNT(old.user) as March11,
  COUNT(cur.user) as Current
FROM users u
LEFT JOIN rounds cur ON cur.user=u.id
LEFT JOIN rounds__20120311_010951 old ON old.user=u.id
WHERE u.email = '[email protected]'
GROUP BY u.id;

Upvotes: 1

Devart
Devart

Reputation: 121902

You also may use this query -

SELECT
  u.id,
  old.cnt as March11,
  cur.cnt as Current
FROM
  users u
LEFT JOIN (SELECT user, COUNT(*) cnt FROM rounds GROUP BY user) cur
  ON u.id = cur.user
LEFT JOIN (SELECT user, COUNT(*) cnt FROM rounds__20120311_010951 GROUP BY user) old
  ON u.id = old.user
 GROUP BY
   u.id

LEFT JOIN will show (all) records from users table and all related records from two joined tables.

Upvotes: 1

Related Questions