skip to Main Content

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


  1. 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.
  2. 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.
Please signup or login to give your own answer.
Back To Top
Search