mysql - items count, but data from 2 other tables

Question

I have 3 tables:

First one named "deaths", that contain id, name, date, editdate,

Second one named "listFriend", that contain names,

Third one named "listEnemy", that contain names

What i want is to get how many records in "deaths" table is matching names from friend and enemy tables.

What i wrote is 2 query

SELECT count(name) as enemies FROM `deaths` WHERE name IN( SELECT name FROM `listEnemy`)
SELECT count(name) as friends FROM `deaths` WHERE name IN( SELECT name FROM `listFriend`)

Its working how it should, but i would like to have it in one query, to get both values by just one request

score 0 · Answer 1 · answered Mar 18 '22 at 07:03

0

You can LEFT JOIN the tables and count how many times the join condition worked

SELECT sum(case when e.name is not null then 1 else 0 end) as enemies,
       sum(case when f.name is not null then 1 else 0 end) as friends
FROM deaths d
LEFT JOIN listEnemy e ON e.name = d.name
LEFT JOIN listFriend f ON f.name = d.name

answered Mar 18 '22 at 07:03

juergen d

201,996
37
293
362

getting error #1104 - The SELECT would examine more than MAX_JOIN_SIZE rows; – Grucha881 Mar 18 '22 at 08:04
https://stackoverflow.com/a/12928048/575376 – juergen d Mar 18 '22 at 08:55

mysql - items count, but data from 2 other tables

1 Answers1