CODEWITHSUNDEEP
phpmysql
Hintswen
Hintswen

Reputation: 4007

compare results with another table in PHP/MySQL

I have 2 tables...

Table1:
ID, Name, Country
Table2:
ID, accountID, parent

table1.id = table2.acountID

My script does a search for all records with a particular parent. I want to compare those results with table1 and return all entries that wern't in the search.

eg. Table1:

1, Bill, AU
2, Charles, US
3, Clare, CA

Table2:

1, 1, Mary
2, 1, William
3, 2, Henry

Search (select * from table2 WHERE accountID='1') returns:

1, 1, Mary
2, 1, William

and I want to get this results (from table1):

2, Charles, US
3, Clare, CA

Upvotes: 0

Views: 911

Answers (2)

thomasrutter
thomasrutter

Reputation: 117323

Your search is returning all rows in Table2 where accountID = 1

To return all rows that were not returned in the search, you would find all rows in Table1 which have an ID other than 1, or do not have any matching rows in Table2.

SELECT
  ID
FROM
  Table1
WHERE
  ID <> 1
  OR NOT EXIST (SELECT * FROM Table2 WHERE accountID = 1)

Seems simple?

Upvotes: 0

Alex Martelli
Alex Martelli

Reputation: 881567

SELECT * FROM table1 WHERE ID NOT IN
  (SELECT * FROM table2 WHERE accountID = '1')

Upvotes: 1

Related Questions