Question about SQL query?

Favorite Answer

If I understand the question, you want:
All rows in table1 that don't have matching rows in table2
AND
All rows in table2 that don't have matching rows in table1

So, that sounds like a UNION to me:

SELECT <something>
FROM table1
WHERE <some condition>

UNION ALL

SELECT <something>
FROM table 2
WHERE <some condition>;

But what do we need for a condition here? Well, for table1, you want to know if there is no row in table2 that has matching columns to table1. And for table2, you want the same thing, but for table1.

So a WHERE NOT EXISTS with a nested query seems like the right answer:

SELECT <something>
FROM table1 t1
WHERE NOT EXISTS (select * from table2 t2 where t1.col1 = t2.col1 AND t1.col2 = t2.cols)

UNION ALL

SELECT <something>
FROM table 2 t2
WHERE NOT EXISTS (select * from table2 t2 where t1.col1 = t2.col1 AND t1.col2 = t2.cols);

Yeah, that seems right. Now you just have to replace <something> with what you want to select. It could be * I suppose, but using SELECT * isn't really best practice. (although it's fine in the nested selects).

I didn't test this. May not have the syntax 100% right. Not even sure if MySQL can handle me using t1 and t2 aliases twice in the same query like that. It should, but might not. If you have trouble, get each of the selects to work by itself, then work on combining them.

It is a moderately complicated problem. Just work through it piece by piece until you can put all the pieces together into 1 query that works.

ETA: >> Oh, okay.. I know what it mean.. hehe

Yup, that's how you do it. You bash your head against the wall for 15 minutes (or longer!) until you figure out what the error message means, or why the query doesn't return the rows you expect. Then you figure it out and move to the next step. That's really the only way to learn SQL;-)...Show more

I'd need more detail to help write the query. In particular: how do you match up columns from one table with the other?

But let's assume that we have one key attribute (k) and two tables (a, b), and we want to find cases where values (x or y) don't match up for the same key.

I'd write something like this:

SELECT NVL(a.k, b.k) tbl_key, a.x A_X, b.x B_X, a.y A_Y, b.y B_Y
FROM a FULL OUTER JOIN b ON a.k=b.k
WHERE NVL(a.x,'X') <> NVL(b.x,'X')
OR NVL(a.y, 'X') <> NVL(b.y,'X')

Assuming you pick some value for those 'X' (constant) values that isn't actually present in the database, this will find all cases where "a" is missing the row, or "b" is missing the row, or "a" and "b" are both present BUT have different data in x or y.

If "missing rows" is not an issue, then the (much slower) FULL OUTER JOIN can be replaced with INNER JOIN.

@M...Show more

select a.f1,a.f2,b.f1,b.f2
from table1 a full join table 2 b
on a.f1 = b.f1 and a.f2 = b.f2
where a.f1 is null or b.f1 is null

Look here also: http://www.sql-tutorial.ru/en/book_full_join_and_mysql.html...Show more