CODEWITHSUNDEEP
mysqlsyntax
user1904273
user1904273

Reputation: 4764

MYSQL QUERY SYNTAX: left join properly exclude matches

I am trying to get a MYSQL query right and it is not doing what I want.

Have table of people, table of tasks and table of people linked to tasks. This is for a page that lets users assign people to tasks. Tables have userid fields since different users are working with different groups of people and/or tasks. For each task, I want to populate a list of people the user can assign to the task that excludes any people already assigned.

Tables are as follows: people

id|person|userid

tasks

id|task|userid

taskperson

id|personid|taskid

The query I am working with is:

SELECT p.*,p.id as pid 
FROM `people` p 
LEFT JOIN `taskperson` tp 
on p.id = tp.personid 
WHERE p.person LIKE '%$str%' AND p.userid = '1'AND tp.personid NOT IN
(SELECT tp.personid FROM `taskperson` tp WHERE userid = '1' AND tp.taskid = '33' ) 
GROUP BY p.id

If you remove the NOT IN clause it will give you a list of people but does not exclude those already assigned.

If you include the NOT IN clause, when there are no rows in the table, taskperson for the user in question, ie no people have been assigned to the task, it does not show any people ie returns zero rows. It should show all people in that case.

Can anyone see out where I need to change this?

Thanks for any suggestions

Upvotes: 1

Views: 285

Answers (4)

Raunak
Raunak

Reputation: 17

What I surmise according to your question...

CREATE TABLE PEOPLE(ID INTEGER,PERSON VARCHAR(20),USERID INTEGER); CREATE TABLE TASKS(ID INTEGER,TASK VARCHAR(20),USERID INTEGER); CREATE TABLE TASKPERSON(ID INTEGER,PERSONID INTEGER,TASKID INTEGER);

INSERT INTO PEOPLE VALUES(1,"RAHUL",1); INSERT INTO TASKS VALUES(1,NULL,1); INSERT INTO TASKPERSON VALUES(1,1,NULL);

INSERT INTO PEOPLE VALUES(1,"RAUNAK",1); INSERT INTO TASKS VALUES(1,"R",1); INSERT INTO TASKPERSON VALUES(1,1,33);

INSERT INTO PEOPLE VALUES(2,"PARTH",2); INSERT INTO TASKS VALUES(2,"P",2); INSERT INTO TASKPERSON VALUES(2,2,22);

INSERT INTO PEOPLE VALUES(3,"KANISHK",3); INSERT INTO TASKS VALUES(3,"K",3); INSERT INTO TASKPERSON VALUES(3,3,33);

INSERT INTO PEOPLE VALUES(4,"HEENA",4); INSERT INTO TASKS VALUES(4,NULL,4); INSERT INTO TASKPERSON VALUES(4,4,NULL);

When not including "NOT IN", you will retrieve the list of those people who are not allocated.

SELECT P.*,P.ID FROM PEOPLE P LEFT JOIN TASKPERSON TP ON P.ID=TP.PERSONID
WHERE P.USERID=1 AND TP.PERSONID IN(SELECT TP.PERSONID FROM TASKPERSON TP WHERE TP.PERSONID=1 AND TP.TASKID=33)
GROUP BY P.ID

Upvotes: 0

Strawberry
Strawberry

Reputation: 33935

I'm not sure, but perhaps this is what you're after...

SELECT DISTINCT p.*
  FROM people p 
  LEFT 
  JOIN taskperson tp 
    ON tp.personid = p.id
   AND taskid = 33 
 WHERE p.person LIKE '%$str%' 
   AND p.userid = 1
   AND tp.personid IS NULL;

Upvotes: 0

valex
valex

Reputation: 24134

I think the issue here is in the condition AND tp.personid NOT IN (....) if tp.personid is NULL then this condition is ALWAYS FALSE. So you don't need LEFT JOIN at all.

SELECT p.*,p.id as pid 
FROM `people` p 
WHERE p.person LIKE '%$str%' AND p.userid = '1'AND p.id NOT IN
(SELECT tp.personid FROM `taskperson` tp WHERE userid = '1' AND tp.taskid = '33' )

Upvotes: 0

Paul Stanley
Paul Stanley

Reputation: 4098

I think you need IS NULL.

SELECT p.*,p.id as pid 
FROM `people` p 
LEFT JOIN `taskperson` tp 
on p.id = tp.personid 
WHERE p.person LIKE '%$str%' AND p.userid = '1' AND tp.personid  IS NULL
GROUP BY p.id

Judging from your removal of the NOT IN clause, this will exclude people that have already been assigned. WHERE tp.personid IS NULL will show only those people that do not have a match from the LEFT JOIN.

Upvotes: 1

Related Questions