compare results with another table in PHP/MySQL

Question

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

Alex Martelli · Accepted Answer · 2009-05-22 05:05:17Z

1

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

answered May 22, 2009 at 5:05

Alex Martelli

887k175 gold badges1.3k silver badges1.4k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Hintswen Over a year ago

perfect! (or atleast it looks perfect). I assume it is cause it reads how I want it to :)

thomasrutter · Accepted Answer · 2009-05-22 05:12:54Z

0

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?

answered May 22, 2009 at 5:12

thomasrutter

118k31 gold badges153 silver badges170 bronze badges

Collectives™ on Stack Overflow

compare results with another table in PHP/MySQL

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related