Find records from one table which exist in another

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.996
  • Phone_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> 

You group by phonenumber and year, so if the number of rows in the result set is higher than the number of phonenumbers, then for some phonenumbers you’ll have multiple years

thank you!