mySQL combining multiple queries

Question

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?

SQL queries traditionally start with SELECT. And, with your rep, you should know to tag the database you are using. — Gordon Linoff
– Gordon Linoff, Commented Feb 1, 2018 at 13:00

Gordon Linoff · Accepted Answer · 2018-02-01 13:01:53Z

3

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.

answered Feb 1, 2018 at 13:01

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Avitus · Accepted Answer · 2018-02-01 13:03:06Z

2

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;

answered Feb 1, 2018 at 13:03

Avitus

16k6 gold badges47 silver badges54 bronze badges

Collectives™ on Stack Overflow

mySQL combining multiple queries

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related