SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    408
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    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.
    Code:
    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>

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by Miguel61 View Post
    I tried this query, but as you may have 49.930 rows if in the `Phone_book` if I've 24.947 total rows ?
    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

  3. #3
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    408
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    thank you!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •