CODEWITHSUNDEEP
mysql
Surface Dust
Surface Dust

Reputation: 3

Merge two queries into one

I have two queries that I would like to have merged into one.

The first one looks like this; 

SELECT t.name,
       t.score,
       COUNT(m.user_id) AS memberCount
       FROM team AS t, team_member AS m
       WHERE t.team_id = '$id' AND m.team_id = '$id'

the second one looks like this;

SELECT COUNT(t.team_id) AS rank
       FROM team AS t 
       WHERE t.score > (SELECT t.score
                               FROM team AS t 
                               WHERE t.team_id = '$id')

I suspect a JOIN would be part of the solution here, I just dont know how. Any ideas appreciated.

Upvotes: 0

Views: 112

Answers (2)

Griwes
Griwes

Reputation: 9029

You can use UNION:

SELECT t.name,
   t.score,
   COUNT(m.user_id) AS memberCount
   FROM team AS t, team_member AS m
   WHERE t.team_id = '$id' AND m.team_id = '$id'
UNION
SELECT COUNT(t.team_id) AS rank
   FROM team AS t 
   WHERE t.score > (SELECT t.score
                           FROM team AS t 
                           WHERE t.team_id = '$id')

Upvotes: 0

heximal
heximal

Reputation: 10517

you may, e.g., insert your second query right to SELECT clause of first one:

SELECT 
  t.name,
  t.score,
  (SELECT 
    COUNT(t.team_id) AS rank 
  FROM 
    team AS t 
  WHERE 
   t.score > (SELECT t.score FROM team AS t WHERE t.team_id = '$id')
  ) as rank,
  COUNT(m.user_id) AS memberCount
FROM 
  team AS t, team_member AS m
WHERE 
  t.team_id = '$id' AND m.team_id = '$id'

Upvotes: 1

Related Questions