In Postgres, find out if a value in one row exists as a value in another row, but two different columns - Postgresql

SabrinaSampaioSim227o
March 30, 2023
300 views
1 vote
2 Answers

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?

Tags: postgresql

Answers

- KaushikNarayan
- March 30, 2023 at 11:26 pm
- 0 votes
0
```
SELECT * FROM db."fTable2" INNER JOIN db."fTable1" ON db."fTable2"."idParent" = db."fTable1"."idWit"
```
JOINs are what you need.
Login or Signup to reply.

- Zegarek
- March 31, 2023 at 12:12 am
- 0 votes
0
I’m guessing you had something like this: demo
```
create schema db;
create table db."fTable1" (
  "idParent" int, 
  "idWit" int);
insert into db."fTable1" values 
(11,21),
(12,22),
(13,23),
(14,11),--this one's "idWit" matches the first row's "idParent"
(15,25);
```
Did something along the lines of:
```
select * from db."fTable1" where db."fTable1"."idParent"=db."fTable1"."idWit";
```
But that checked "idParent" and "idWit" from the exact same row, so it found nothing. So you did something like this:
```
select * 
from db."fTable1"
where exists (
  select * 
  from db."fTable1"
  where (db."fTable1"."idParent" = db."fTable1"."idWit"));
```
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:
```
create table db."fTable2" as table db."fTable1";
```
And now it works, because it’s now taking rows from db."fTable1" and looking for matching rows in db."fTable2". Thing is, you could’ve aliased db."fTable1" to state explicitly you’re referring to the same table twice, without having to clone it:
```
select * 
from db."fTable1" as t1
where exists (
  select * 
  from db."fTable1" as t2
  where t2."idParent"=t1."idWit"); --acts as if it was a whole other table now
```
Or, as @Kaushik Narayan pointed out, you could join the table with itself:
```
select * 
from db."fTable1" as t1 
inner join db."fTable1" as t2
on t2."idParent"=t1."idWit";
```
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.
Login or Signup to reply.