3

I need to make three select queries over three different tables, using the outputs of each select query, the catch is each one gives multiple results. Here is how I do it.

Select "Title", "Num" from "Table" where "Id" in (

    Select "Id" from "Table2" where "Id"    in (

        select distinct "Id" from "Table3" where foo-clause         
           )

)

İt only gives me the result of one of the results. How can I make each one use multiple inputs?

1 Answer 1

2

You need to join the 3 tables together into one select rather then use the other two tables as part of a where clause. I have provided an example of joining all 3 together and having a condition for the third table.

SELECT a."Title", a."Num"
FROM   "Table" a,
       "Table2" b,
       "Table3" c
WHERE  a."Id" = b."Id"
AND    a."Id" = c."Id"
AND    c.foo-clause = foo-clause
3
  • Is it possible to use a "WHERE this=that and this1=that1" command instead of foo-clause? Commented Apr 5, 2018 at 19:13
  • @EvsizTospaa The last part of the where clause was just an example based on your question and you can replace c.foo-clause = foo-clause with any other condition or conditions as needed for your query. The main thing to remember is to keep the part of the where clause that is the link between each table so you get the correct results.
    – Joe W
    Commented Apr 5, 2018 at 19:15
  • The query works fine if I use only two lines in the where part. However, when I add the third line, i get an "Invalid Table Name" error ORA-00903 Commented Apr 6, 2018 at 10:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.