I had to create a secondary table to be able to compare them, like so:
WHERE EXISTS
(SELECT *
FROM db."fTable2"
WHERE (db."fTable2"."idParent" = db."fTable1"."idWit"))
Both idParent and idWit exists in Table1, but comparing both in the same table resulted in nothing, while creating a secondary table fTable2 did the job.
Is there a better way of doing this?
2
Answers
JOINs
are what you need.I’m guessing you had something like this: demo
Did something along the lines of:
But that checked
"idParent"
and"idWit"
from the exact same row, so it found nothing. So you did something like this:But that again found nothing, because the subquery in
exists
is checking the exact same thing: whether there’s a row where within that one row, these two values are the same. So you took them out to another table:And now it works, because it’s now taking rows from
db."fTable1"
and looking for matching rows indb."fTable2"
. Thing is, you could’ve aliaseddb."fTable1"
to state explicitly you’re referring to the same table twice, without having to clone it:Or, as @Kaushik Narayan pointed out, you could
join
the table with itself:That’s a ton of guessing. You’d get clearer answers, quicker, if you showed your
create table
statement, the code you actually tried, the results or errors you were getting and what you expected instead.