How can I use the AND statement to compare two tables?

I'm trying to write a statement that will check 2 tables and compare the 2 and only show the users who are not on table2. For example: table1 has user1, user2, user3, and user4 AND table2 has user1, user3, and user4 then it would only show user2 in the list.

I was able to write something as basic as to only show member_id='1' but I don't how to make it wor开发者_如何学Gok the way I would like. I'm hoping you understand my question. I'll include the short code that deals with this issue...

$sql->db_Select(DB_TABLE_ROSTER_MEMBERS, "*", "member_id='1' AND
                               (member_status like 'Team Member%'
                                OR member_status like 'Squad Leader%'
                                OR member_status like 'Recruiter%'
                                OR member_status like 'New Recruit%'$customArgs)
                               ORDER BY member_application_date DESC");

the syntax for this can be located here- http://wiki.e107.org/index.php?title=Db#db_Select.28.29 I'm guessing I just need to make another select DB and just have it included in the above so it would be something like

member_id != $member_id

where $member_id would be something like $member_id = $row['member_id'];

---

You need to make a LEFT JOIN and check where table2.member_id IS NULL.

---

First, AND is an operator ;)

Second, there are many relational operators that imply logical AND e.g. intersect, join, product (SQL CROSS JOIN), restriction (SQL WHERE, extension (SQL AS clause) and matching/difference (SQL MATCH or EXISTS or IN).

Third, from your first sentence I rather think you are looking for relational operators that imply logical NOT i.e. non-existential qualification. Again, there are many options e.g. minus (SQL EXCEPT), not matching/semi-difference (SQL NOT EXISTS or NOT IN but bizarrely not NOT MATCH!); personally, I find OUTER JOIN testing for nulls to be the most odd (not to mention not relational)!

Fourth, your relational/SQL product of choice will likely not support and/or equally optimize all the different options from a given relational algebra or SQL Standard.

My personal choice for semi-difference is NOT EXISTS: the name clearly implies non-existential qualification, the predicates are located close together in SQL code, is widely supported and usually well optimized:

SELECT * 
  FROM table1
 WHERE NOT EXISTS (
                   SELECT * 
                     FROM table2
                    WHERE table2.member_id = table1.member_id  -- predicates close together
                  );