1

Possible Duplicate:
Mysql select where not in table

I need select items from one table (t1) but exclude any results that have id in another table (t2) and have the same project ID. 

SELECT * 
FROM t1 
WHERE project = 10

Need to exclude any rows that are present in t2.

t1.userID = t2.memberID AND t2.projectID = t1.project

Not sure how to put it all together...

Community
  • 1
  • 1
santa
  • 12,234
  • 49
  • 155
  • 255
  • 1
    possible duplicate of [Mysql select where not in table](http://stackoverflow.com/questions/354002/mysql-select-where-not-in-table) and [MySQL: select emails from one table only if not in another table](http://stackoverflow.com/questions/1002728/mysql-select-emails-from-one-table-only-if-not-in-another-table) – Ken White Nov 28 '11 at 21:38
  • I need to make sure that 2 items match in order to exclude: projectID and memberID. If memberID is in t2 but the projectID is different, it can be included. – santa Nov 28 '11 at 21:50
  • The other two posts I linked to give you the information you need to solve your problem. The `AND` in your `WHERE` clause will make sure both items match; if they don't, they won't meet the condition of the `WHERE` and will be included. – Ken White Nov 28 '11 at 21:52

1 Answers1

4
SELECT t1.* 
FROM t1 
LEFT OUTER JOIN t2 ON t1.userID = t2.memberID AND t2.projectID = t1.project
WHERE t1.project = 10 and t2.projectID IS NULL
idstam
  • 2,848
  • 1
  • 21
  • 30
  • faster than NOT IN (select ...) – Moshe L Nov 29 '11 at 07:57
  • My understanding of LEFT OUTER JOIN that it will combine results from t1, combines them with the column names from t2 table and sets the value of every column from the t2 table to NULL. I only want to select values from t1. If the project ID matches in t2 and usedIDs match, then DO NOT select from t1. Am I confused about this?... – santa Nov 29 '11 at 13:18
  • ok, I edited the query to only select the columns from t1. Left outer join sets all columns from the second table to null when there is no match in the join. Those are the rows I remove with the second part of the where clause. – idstam Nov 29 '11 at 13:41