How to return the rows that doesn't match two tables?

[mkjame]

Hi everyone!

I am actually coding a php script and I want to return all the values that doesn't have a relation between two tables.

Basically I have two tables, one is called users, the other one is called agents, the structure of the users table is the following:
ID (auto_increment) | username (email address) | password

the structure of the agents table is the following
ID (auto_increment) | EmailAddress | city | state | country

with

SELECT * FROM dbname.users AS users, dbname.agents AS agents WHERE agents.EmailAddress = users.username

it will return me all the results that matches the two tables. So this works.

But my goal is to display the results that doesn't match, so I tried to do:

SELECT * FROM dbname.users AS users, dbname.agents AS agents WHERE agents.EmailAddress != users.username

but it doesn't display me the expected results.

There is something wrong in my query, but where?

thanks in advance

[amitpatel66]

Hi everyone!

I am actually coding a php script and I want to return all the values that doesn't have a relation between two tables.

Basically I have two tables, one is called users, the other one is called agents, the structure of the users table is the following:
ID (auto_increment) | username (email address) | password

the structure of the agents table is the following
ID (auto_increment) | EmailAddress | city | state | country

with "SELECT * FROM dbname.users AS users, dbname.agents AS agents WHERE agents.EmailAddress = users.username"

it will return me all the results that matches the two tables. So this works.

But my goal is to display the results that doesn't match, so I tried to do:
"SELECT * FROM dbname.users AS users, dbname.agents AS agents WHERE agents.EmailAddress != users.username" but it doesn't display me the expected results.

There is something wrong in my query, but where?

thanks in advance

Try this:

SELECT * FROM agents WHERE emailaddress NOT IN (SELECT username from users)
UNION
SELECT * from users WHERE username NOT IN (SELECT emailaddress FROM agents)
 

Note: The no of columns and its datatypes in both the tables should be same to be used in UNION

[mkjame]

Try this:

SELECT * FROM agents WHERE emailaddress NOT IN (SELECT username from users)
UNION
SELECT * from users WHERE username NOT IN (SELECT emailaddress FROM agents)
 

Note: The no of columns and its datatypes in both the tables should be same to be used in UNION

Thank you for the reply. See my problem is that in one table (dbname.agents) I have 72 rows and in the other table (dbname.users) I have 85 rows. I want to eliminate "automatically" the 13 rows difference and I thought the way to do it is to compare the existing email address in dbname.agents with dbname.users by using a common value which is the email.

Any ideas?

Thank you in advance for your help!!

[mwasif]

This will give you those records which are present in users but not in agents.

SELECT * FROM dbname.users AS users 
LEFT JOIN dbname.agents AS agents ON users.username = agents.EmailAddress
WHERE agents.EmailAddress IS NULL