MySQL Join Clarification

Question

I'm new to MySQL and PHP, I come from an OO background and trying to wrap my head around SQL queries has been a bit frustrating. Right now I'm trying to find all matching IDs within the same table given a user ID and category.

Here's the question I'm trying to answer: Given User A and Category X, what other users also have the same interests in Category X as User A and what are those interests?

Here's the code I have so far:

CREATE TEMPORARY TABLE IF NOT EXISTS t_int_map AS (
SELECT intmap.fb_id, intmap.interest_id
FROM interest_map AS intmap
INNER JOIN interests AS i ON intmap.interest_id =  i.id
WHERE intmap.fb_id = <ID of User A> AND i.category = '<Category User A selects');

SELECT im.fb_id, im.interest_id, i.name
FROM interest_map AS im
INNER JOIN interests AS i ON im.interest_id = i.id
INNER JOIN t_int_map AS t_ 
WHERE t_.interest_id = im.interest_id

This is giving me a result set with all of the interests User A has under Category X as well as the other users who have matching interests under that category. I want to drop all interests that aren't shared with other users. IE: If User A has 10 interests under Category X and shares 2 of those interests with User B and 1 with User C, I want to see only the rows that contain the shared interests (so there would be a total of 6 rows, 3 for User A, 2 for B and 1 for C).

Is it best practice to create a temporary table like this or is there a better way to do it? I would rather not create a temp table but I couldn't get a subselection query to work the subselection was returning more than 1 row. Any and all advice is greatly appreciated, thanks!

Answer 1

I don't think you'll need to use a temporary table. You can use a single select statement. The query below gets all the interest_map and interests records for a specified category and uses EXISTS to limit the results to interests of a specified user.

See: http://dev.mysql.com/doc/refman/5.6/en/exists-and-not-exists-subqueries.html

 DROP TABLE IF EXISTS interest_map;

 DROP TABLE IF EXISTS interests;



 CREATE TABLE interests 
 (
     interest_id INT NOT NULL PRIMARY KEY
     , category VARCHAR(25) NOT NULL
     , description VARCHAR(50) NOT NULL
 );

 CREATE TABLE interest_map 
 (
     fb_id VARCHAR(10) NOT NULL
     , interest_id INT NOT NULL 
     , CONSTRAINT FOREIGN KEY ( interest_id ) REFERENCES interests ( interest_id )
     , CONSTRAINT PRIMARY KEY ( fb_id , interest_id )
 );


 INSERT INTO interests ( interest_id, category, description )
 VALUES 
     ( 1, 'Programming', 'Java' )
     ,( 2, 'Programming', 'PHP' )
     ,( 3, 'Programming', 'C#' )
     ,( 4, 'Database', 'Oracle' )
     ,( 5, 'Database', 'MySQL' )
     ,( 6, 'Database', 'DB2' )
     ,( 7, 'Operating System', 'Linux' )
     ,( 8, 'Operating System', 'Windows' );


 INSERT INTO interest_map  ( fb_id , interest_id )
 VALUES
     ( 'User A', 1 )
     ,( 'User A', 3 )
     ,( 'User B', 1 )
     ,( 'User B', 5 )
     ,( 'User B', 2 )
     ,( 'User B', 7 )
     ,( 'User C', 1 )
     ,( 'User C', 3 )
     ,( 'User C', 4 )
     ,( 'User C', 7 );


 SET @user = 'User A';
 SET @category = 'Programming';

 SELECT 
     m.fb_id 
     , i.interest_id
     , i.description
 FROM interests AS i
     INNER JOIN  interest_map AS m
         ON ( i.interest_id = m.interest_id )
 WHERE i.category = @category  -- get interests in this category
     AND EXISTS (
             SELECT *
             FROM interest_map AS m2
             WHERE m2.fb_id = @user
                 AND m2.interest_id = m.interest_id
         )  -- the exists clause limits results to interests of the specified user
 ORDER BY m.fb_id, i.description;