CODEWITHSUNDEEP
mysqlsql
enchance
enchance

Reputation: 30421

Disregard rows from other users on a relationship table

I've been trying to solve this on my own but I can't seem to get it right. I basically have 2 sample tables: position and position_meta.

Expected result (User ID: 1)

------------------------------------------------
    id  position                      user_id  
------  ----------------------------  ---------
     1  Physical Therapist Assistant          1
     2  Brake Machine Setter             (NULL)
     3  Community Service Manager             1
------------------------------------------------

Instead of

-----------------------------------------------
    id  position                      user_id  
------  ----------------------------  ---------
     1  Physical Therapist Assistant          2
     1  Physical Therapist Assistant          1
     2  Brake Machine Setter             (NULL)
     3  Community Service Manager             1
     3  Community Service Manager             3
-----------------------------------------------

My sample tables

Table: position
-----------------------------------------
    id  position                                
------  ---------------------------------
     1  Physical Therapist Assistant            
     2  Brake Machine Setter                    
     3  Community Service Manager               
-----------------------------------------

Table: position_meta
------------------------------
    id  user_id  position_id  
------  -------  -------------
     1        1             1
     2        2             1
     3        3             3
     4        1             3
------------------------------

As you can see, some users have entries in position_meta while some do not. But is it possible to display all positions whether they have entries in position_meta or not. But if the current User (e.g.user_id 1) has an entry in position_meta it will show it and disregard any other rows of users who might have selected that same position as well (preventing duplicate rows).

If the position_meta entry is from another user_id entirely (not user_id 1) then show NULL or any of the users which own it since I won't be checking for it anyway.

Is this something MySQL can do?

Edit: Here's the query I used which got wrong results:

SELECT p.id, p.position, pm.user_id FROM `position` p
    LEFT JOIN position_meta pm ON p.id = pm.position_id
    ORDER BY p.id

Upvotes: 0

Views: 27

Answers (1)

symcbean
symcbean

Reputation: 48357

Based on you the expected output you provided (it would have been helpful if you had shown the SQL you used to get the your result, even if it was wrong)...

SELECT p.id, p.position, pm.uer_id
FROM position p
LEFT JOIN position_meta pm
ON p.id=pm.position_id
AND pm.user_id=1

Note that the filter expression is part of the join clause, not the where clause.

Upvotes: 1

Related Questions