MySQL Query - Many to Many

Question

I need to find match for a user.

Each user has multiple "work category"(like Accounting, Banking, Finance etc) and multiple "preferred work category"(to find matching profile in those specified category)

What is the effective way to find the match using MySQL Query.

Structure of the MyProfile table:

User_Id | Work_Id
1       | 5
1       | 7
1       | 8
2       | 7
2       | 9

Structure of PreferedWork table

User_Id | Work_Id
1       | 7
1       | 9
2       | 1
2       | 8

I have to check against 100,000 profiles and return Only 10 profiles.

EDIT: My Original query (without join):

$getMatchesQry = "
        select ed.Entity_Id as entId,ed.Status as status,ed.First_Name,ed.Profile_Pic_Url,ed.Sex,ed.Current_Lat,ed.Current_Long,ed.community,ed.ethnicity,ed.Fb_Id,ent.Last_Active_Dt_Time,
      YEAR(NOW()) - YEAR(ed.dob) - (RIGHT(NOW(), 5) < RIGHT(ed.DOB, 5)) as age, pr.Orientation
        from entity_details ed,entity ent, preferences pr
        where 
        (3959 * acos( cos( radians('" . $prefRow['Current_Lat'] . "') ) * cos( radians(ed.Current_Lat) ) * cos( radians(ed.Current_Long) - radians('" . $prefRow['Current_Long'] . "') ) + sin( radians('" . $prefRow['Current_Lat'] . "') ) * sin( radians(ed.Current_Lat) ) ) ) <= " . $prefRow['Preference_radius'] . "          
        and
        (" . $pref_sex_str . ")  
        and
        (" . $prefComWord . ") and (" . $prefRelWord . ")
        and
        pr.Orientation  = '" . $prefRow['Orientation'] . "' 
        and             
        '" . $prefRow['min_height'] . "' <= ed.height 
        and 
        ed.height  <= '" . $prefRow['max_height'] . "'                                
        and            
        ed.Entity_Id = ent.Entity_Id and  
        ed.Entity_Id = pr.Entity_Id and              
        ent.status = 1  and  ed.Fb_Id != '" . $prefRow['Fb_Id'] . "'  and 
        ed.Entity_Id NOT IN (select Entity2_Id from likes where Entity1_Id = '" . $entityId . "' and (Like_Flag = '1' OR  Like_Flag = '2' OR  Like_Flag = '3' OR  Like_Flag = '4'))             
        having 
        age BETWEEN '" . $prefRow['Preference_lower_age'] . "' AND '" . $prefRow['Preference_upper_age'] . "' LIMIT 10";

when i add join,

$getMatchesQry = "
        select ed.Entity_Id as entId,ed.Status as status,ed.First_Name,ed.Profile_Pic_Url,ed.Sex,ed.Current_Lat,ed.Current_Long,ed.community,ed.ethnicity,ed.Fb_Id,ent.Last_Active_Dt_Time,
      YEAR(NOW()) - YEAR(ed.dob) - (RIGHT(NOW(), 5) < RIGHT(ed.DOB, 5)) as age, pr.Orientation
        from entity_details ed,entity ent, preferences pr, **pref_occupation ul1, users_occ ul2**
        where 
        (3959 * acos( cos( radians('" . $prefRow['Current_Lat'] . "') ) * cos( radians(ed.Current_Lat) ) * cos( radians(ed.Current_Long) - radians('" . $prefRow['Current_Long'] . "') ) + sin( radians('" . $prefRow['Current_Lat'] . "') ) * sin( radians(ed.Current_Lat) ) ) ) <= " . $prefRow['Preference_radius'] . "          
        and
        (" . $pref_sex_str . ")  
        and
        (" . $prefComWord . ") and (" . $prefRelWord . ")
        and
        pr.Orientation  = '" . $prefRow['Orientation'] . "' 
        and             
        '" . $prefRow['min_height'] . "' <= ed.height 
        and 
        ed.height  <= '" . $prefRow['max_height'] . "'                    
        and
        **( ul1.Occupation_Id = ul2.Work_Id AND ul1.Entity_Id != ul2.Entity_Id AND ul2.Entity_Id = ed.Entity_Id )**
        and            
        ed.Entity_Id = ent.Entity_Id and  
        ed.Entity_Id = pr.Entity_Id and              
        ent.status = 1  and  ed.Fb_Id != '" . $prefRow['Fb_Id'] . "'  and 
        ed.Entity_Id NOT IN (select Entity2_Id from likes where Entity1_Id = '" . $entityId . "' and (Like_Flag = '1' OR  Like_Flag = '2' OR  Like_Flag = '3' OR  Like_Flag = '4'))             
        having 
        age BETWEEN '" . $prefRow['Preference_lower_age'] . "' AND '" . $prefRow['Preference_upper_age'] . "' LIMIT 10"; 

When I add the JOIN (mentioned in bold), i get only the partial result and NOT combining result from original query.

I guess, I messed up AND or other important chaining operator somewhere.

Edit 2:: Getting the correct values now. Anyway to optimise it?

$getMatchesQry = "select DISTINCT ed.Entity_Id as entId,ed.Status as status,ed.First_Name,ed.Profile_Pic_Url,ed.Sex,ed.Current_Lat,ed.Current_Long,ed.community,ed.ethnicity,ed.Fb_Id,ent.Last_Active_Dt_Time,
  YEAR(NOW()) - YEAR(ed.dob) - (RIGHT(NOW(), 5) < RIGHT(ed.DOB, 5)) as age, pr.Orientation
    from entity_details ed, entity ent, preferences pr, pref_occupation ul1, users_occ ul2, 
    where 
    (3959 * acos( cos( radians('" . $prefRow['Current_Lat'] . "') ) * cos( radians(ed.Current_Lat) ) * cos( radians(ed.Current_Long) - radians('" . $prefRow['Current_Long'] . "') ) + sin( radians('" . $prefRow['Current_Lat'] . "') ) * sin( radians(ed.Current_Lat) ) ) ) <= " . $prefRow['Preference_radius'] . "          
    and
    (" . $pref_sex_str . ")  
    and
    (" . $prefComWord . ") and (" . $prefRelWord . ")
    and
    pr.Orientation  = '" . $prefRow['Orientation'] . "' 
    and             
    '" . $prefRow['min_height'] . "' <= ed.height 
    and 
    ed.height  <= '" . $prefRow['max_height'] . "'                    
    and
    ( ( ul1.Occupation_Id = ul2.Work_Id AND ul1.Entity_Id != ul2.Entity_Id AND ul2.Entity_Id = ed.Entity_Id ))        
    and            
    ed.Entity_Id = ent.Entity_Id and  
    ed.Entity_Id = pr.Entity_Id and              
    ent.status = 1  and  ed.Fb_Id != '" . $prefRow['Fb_Id'] . "'  and 
    ed.Entity_Id NOT IN (select Entity2_Id from likes where Entity1_Id = '" . $entityId . "' and (Like_Flag = '1' OR  Like_Flag = '2' OR  Like_Flag = '3' OR  Like_Flag = '4'))           
    having 
    age BETWEEN '" . $prefRow['Preference_lower_age'] . "' AND '" . $prefRow['Preference_upper_age'] . "' LIMIT 10"; 

Answer 1

Your question doesn't seem to be complete but from what I understand, you can try something like this :

SQL Fiddle

MySQL 5.5.32 Schema Setup:

CREATE TABLE MyProfile
    (`User_Id` int, `Work_Id` int)
;

INSERT INTO MyProfile
    (`User_Id`, `Work_Id`)
VALUES
    (1, 5),
    (1, 7),
    (1, 8),
    (2, 7),
    (2, 9),
    (3, 10)
;

CREATE TABLE PreferedWork
    (`User_Id` int, `Work_Id` int)
;

INSERT INTO PreferedWork
    (`User_Id`, `Work_Id`)
VALUES
    (1, 7),
    (1, 9),
    (2, 1),
    (2, 8)
;

Query 1:

SELECT mp.User_ID,
       GROUP_CONCAT(DISTINCT mp.Work_Id ORDER BY mp.Work_Id) AS `work category`,
       GROUP_CONCAT(DISTINCT ifnull(pw.Work_Id,'') ORDER BY pw.Work_Id) AS `preferred work category`
FROM MyProfile mp
LEFT OUTER JOIN PreferedWork pw ON mp.User_ID = pw.User_ID
GROUP BY mp.User_ID

Results:

| USER_ID | WORK CATEGORY | PREFERRED WORK CATEGORY |
|---------|---------------|-------------------------|
|       1 |         5,7,8 |                     7,9 |
|       2 |           7,9 |                     1,8 |
|       3 |            10 |                         |

OR MAY BE YOU ARE LOOKING FOR SOMETHING LIKE THIS :

SELECT mp.User_ID,
FROM MyProfile mp
LEFT OUTER JOIN PreferedWork pw ON mp.User_ID = pw.User_ID
WHERE (mp.Work_Id = '8' or pw.Work_Id = '8')
LIMIT 10

Answer 2

If you want a intersection between the two tables just use INNER JOIN

SELECT MyProfile.User_Id, MyProfile.Work_Id
FROM MyProfile INNER JOIN PreferedWork 
USING (User_Id, Work_Id)

http://www.bitbybit.dk/carsten/blog/?p=71

Answer 3

join the tow tables on user id to get all combinations between work category and preferred work . then filter the lines that has work category equal preferred work .

SELECT p.work_id, p.user_id
FROM MyProfile as p
INNER JOIN PreferedWork as pw on p.user_id = pw.user_id
WHERE p.work_id = pw.work_id

Answer 4

Without knowing more details, you should probable have this instead of what you have now:

MyProfile:

user_id | work_id | preference
1       | 1       | 0
1       | 2       | 0
1       | 3       | 1
2       | 1       | 1
2       | 2       | 0

So here we see that user_id 1 has work_id 1, 2, and 3, but prefers 3. user_id 2 has work_id 1 and 2 and prefers 1