Get results that are not in another table

[EchoFool]

Hey

 

I have a query i made which isn't work but this is what im trying to do:

 

Get rows where table.ResearchID = 0 and table.RecordID NOT FOUND  in table2

 

But also

 

Get rows where table.ResearchID > 0 and table.ResearchID IS FOUND in table2 and table2.Completed= 1

 

All related to the user in question...

 

But when i tried this in my query it would not work.. this is what i tried:

 

<?php

SELECT *
FROM projects
WHERE
       (projects.ResearchID=0 AND projects.RecordID NOT IN (SELECT ResearchID FROM research WHERE UserID='$UserID')) 
OR 
       (projects.ResearchID>0 AND projects.ResearchID IN (SELECT ResearchID FROM research WHERE UserID='$UserID' AND Completed='1')

?>

 

Currently it still loads rows which are in table research and the projects.ResearchID > 0  which is wrong in my case.

 

Hope you can help correct where i have gone wrong.

[agrant]

try this instead, it's a little cleaner to figure out where your error is coming from:

 

SELECT *
   FROM projects
WHERE projects.ResearchID=0
    AND NOT EXISTS (SELECT 1 FROM research WHERE UserID='$UserID' and research.ResearchID = projects.ResearchID) 
UNION
SELECT *
FROM projects
WHERE projects.ResearchID>0 
     AND EXISTS (SELECT 1 FROM research WHERE UserID='$UserID' AND Completed='1' and research.ResearchID = projects.ResearchID)

 

Also what is the difference between RecordID in projects and ResearchID in research? How are they related? It seems to me the issue is probably in the join in both of the exists/not exists subqueries with a mismatched column.

[EchoFool]

the differnce is ReasearchId is the ID the user needs to have completed in the other table (compelted = 1) and if not then that record is should not load until the ResearchID it requires is completed.

 

If researchid = 0 then it doesn't need to check if the user has completed some other research first

[kickstart]

Hi

 

Possibly use left outer joins like this:-

 

SELECT a.*
FROM projects a
LEFT OUTER JOIN research b
ON a.RecordID = b.ResearchID
AND a.ResearchID = 0
LEFT OUTER JOIN research c
ON a.ResearchID = c.ResearchID
AND a.ResearchID > 0
AND c.Completed = '1'
WHERE UserID='$UserID'
AND (b.ResearchID IS NOT NULL
OR c.ResearchID IS NOT NULL)

 

All the best

 

Keith

[EchoFool]

That didn't work ended up giving the same result as my attempt.