Have Geek Brain

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 searches for all records with a specific parent. I want to compare these results with Table 1 and return all records that were not searched.

eg. Table 1:

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 these results (from table 1):

2, Charles, US
3, Clare, CA
0


a source to share


2 answers 


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


+1


a source


Your search returns all rows in table2 where accountID = 1

To return all rows that were not returned in the search, you will find all rows in Table 1 that have an ID other than 1, or have no matching rows in Table2.



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

Sounds simple?

0


a source






More articles:


All Articles