CODEWITHSUNDEEP
phpsql
user1022585
user1022585

Reputation: 13651

mySQL combining multiple queries

I'm running some sql code from my PHP site/game to find the players friends and enemies online. Both are pulling data from the same tables.

// friends online
SELECT COUNT(*) FROM contacts c 
    INNER JOIN online o ON o.playerID = c.playerID 
    WHERE c.myID=".$id." AND c.type='friends'

// enemies online
SELECT COUNT(*) FROM contacts c 
    INNER JOIN online o ON o.playerID = c.playerID 
    WHERE c.myID=".$id." AND c.type='enemies'

Is there a way to combine these together and get two counts at the end? Would that be faster?

Upvotes: 0

Views: 59

Answers (2)

Avitus
Avitus

Reputation: 15968

This will yield the type and the count:

type  count
friends  0
enemies   2


select c.type, COUNT(*) as 'count' FROM contacts c 
    INNER JOIN online o ON o.playerID = c.playerID 
    WHERE c.myID=".$id." AND c.type in ('friends','enemies')
group by c.type;

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1271003

Use conditional aggregation. The general form is:

SELECT SUM(CASE WHEN c.type = 'friends' THEN 1 ELSE 0 END) as num_friends,
       SUM(CASE WHEN c.type = 'enemies' THEN 1 ELSE 0 END) as num_enemies       
FROM contacts c INNER JOIN
     online o 
     ON o.playerID = c.playerID 
WHERE c.myID = ".$id.";

Yes, this is faster than two queries. If you have lots of types, you can add AND type in ('friends', 'enemies').

Your query is not using the online table, unless it is being used for filtering. If this is not necessary, then remove it from the query.

Upvotes: 3

Related Questions