Find records from one table which exist in another
Hi there, I need your help.
I’ve got the following two SQL tables (in MySQL):
Phone_number
: number rows 5.340.996Phone_book
: number rows 24.947
How do I find out which calls were made by people whose phone_number is in the Phone_book?
I tried this query, but as you may have 49.930 rows if in the Phone_book
if I’ve 24.947 total rows ?
Any help would be much appreciated.
SELECT
FORMAT(
SUM(top_1000),
0,
'de_DE'
) AS top_1000,
FORMAT(
SUM(top_250),
0,
'de_DE'
) AS top_250,
FORMAT(
SUM(top_100),
0,
'de_DE'
) AS top_100,
FORMAT(
SUM(top_50),
0,
'de_DE'
) AS top_50,
FORMAT(
SUM(top_25),
0,
'de_DE'
) AS top_25,
FORMAT(
SUM(top_10),
0,
'de_DE'
) AS top_10,
FORMAT(
SUM(less_10),
0,
'de_DE'
) AS less_10
FROM
(
SELECT
CASE
WHEN SUM(taken) / COUNT(u.MonthYear) > 1000 THEN
1
ELSE
0
END AS top_1000,
CASE
WHEN SUM(taken) / COUNT(u.MonthYear) BETWEEN 250
AND 1000 THEN
1
ELSE
0
END AS top_250,
CASE
WHEN SUM(taken) / COUNT(u.MonthYear) BETWEEN 100
AND 249 THEN
1
ELSE
0
END AS top_100,
CASE
WHEN SUM(taken) / COUNT(u.MonthYear) BETWEEN 50
AND 99 THEN
1
ELSE
0
END AS top_50,
CASE
WHEN SUM(taken) / COUNT(u.MonthYear) BETWEEN 25
AND 49 THEN
1
ELSE
0
END AS top_25,
CASE
WHEN SUM(taken) / COUNT(u.MonthYear) BETWEEN 10
AND 24 THEN
1
ELSE
0
END AS top_10,
CASE
WHEN SUM(taken) / COUNT(u.MonthYear) <= 10 THEN
1
ELSE
0
END AS less_10
FROM
Phone_number u
JOIN Phone_book tmp ON (u.phone_number = tmp.phone_number)
GROUP BY
u.phone_number,
YEAR (Month)
) x;
+----------+---------+---------+--------+--------+--------+---------+
| top_1000 | top_250 | top_100 | top_50 | top_25 | top_10 | less_10 |
+----------+---------+---------+--------+--------+--------+---------+
| 49.930 | 1.467 | 254 | 104 | 73 | 84 | 828 |
+----------+---------+---------+--------+--------+--------+---------+
1 row in set
mysql>