[RESOLVED] SQL query for 2 tables

I have two tables in one SQL database.
Both have the columns firstname and lastname.
I want to show (echo) only the names that don't appear in both.
Can I do this with an SQL query or will I have to sort them in PHP?

You should be able to do that with an SQL query that you can execute from within your PHP code.

What type of SQL are you using? If it's not MySQL, you can use a full outer join, as this example depicts.

If it's MySQL, the above method is not supported, and I'm not sure how you'd go about it. I think this question may be better suited for the Database Development section of the forum. They seem to be good at this SQL stuff...

Thanks I am using MySQL and I will query the forum link given

Quote:

---

Originally Posted by LingoOutsider

I have two tables in one SQL database.
Both have the columns firstname and lastname.
I want to show (echo) only the names that don't appear in both.
Can I do this with an SQL query or will I have to sort them in PHP?

---

If your criteria is only first name and last name, you need to be sure that each first name, last name combination is unique. If they are then you can get what you need from a FULL OUTER JOIN and in the case of MySql, you can use a subquery:

Code:

---

SELECT * FROM table1 WHERE firstName + lastName NOT IN (SELECT firstName + lastName FROM table2)

UNION


SELECT * FROM table2 WHERE firstName + lastName NOT IN (SELECT firstName + lastName FROM table1)

---

A full outer join is more or less equivalent to a LEFT JOIN ... UNION ... RIGHT JOIN. The RIGHT JOIN can also be a second LEFT JOIN with the table order swapped. However, if you have duplicate records in your tables, this will not give the same results as a proper full outer join.