Is it possible to put three separate SELECTs together in a single SQL query where two of the SELECTS grab data for the first SELECT?

Question

If I worded the initial question poorly hopefully this description clears it up. I have a SQL query that is a SELECT like so

select tester as TESTNAME, testing as TESTNAMETWO, test as TESTNAMETHREE
from DATABASE.table master
inner join DATABASETWO.table2 notmaster
on master.column = notmaster.column2
where master.number = 55555
and notmaster.column3 in (~~~DATA I'M MISSING AND NEED~~~)

Now my issue is that I need to get a list of integers (The DATA IM MISSING at the bottom of the query) during this SQL query and I believe I'll need two SELECTS to do it in addition to the initial SELECT. I have two other tables I'd need to work with, first I'll need to use an id (The ID can be the 55555 in the initial query) to grab a value from the first table like so

select secondValue as identifier from THEDB.THETABLE foo
where foo.id = 55555

Then using that value we just got, I'll look for a match in the second table, then grab a value from the row it found a match on like so.

select valuesNeeded as listofvalues from THEOTHERDB.TABLE bar
where bar.value = identifier

That value we grabbed called listofvalues will be the list of integers I'll need in the first SELECT, so once that is grabbed, I can use that value in the initial SELECT during the IN part. Is there a way to do this in strictly SQL and in one query? I can make code changes, but just changing the SQL query itself is much easier for me currently. Thanks for any help/pointers!

Answer 1

You can use the WITH feature to create "temporary" tables that can be used later.

Something like this maybe?

WITH identifier AS (
  select secondValue as identifier 
  from THEDB.THETABLE foo
  where foo.id = 55555
), valuelist AS (
  select valuesNeeded as listofvalues 
  from THEOTHERDB.TABLE bar
  where bar.value in (SELECT identifier FROM identifier)
)
select tester as TESTNAME, testing as TESTNAMETWO, test as TESTNAMETHREE
from DATABASE.table master
inner join DATABASETWO.table2 notmaster on master.column = notmaster.column2
where master.number = 55555
  and notmaster.column3 in (SELECT listofvalues FROM valuelist)

You can also do sub queries -- functionally the same as above:

select tester as TESTNAME, testing as TESTNAMETWO, test as TESTNAMETHREE
from DATABASE.table master
inner join DATABASETWO.table2 notmaster on master.column = notmaster.column2
where master.number = 55555
  and notmaster.column3 in (
    select valuesNeeded as listofvalues 
    from THEOTHERDB.TABLE bar
    where bar.value in (
      select secondValue as identifier 
      from THEDB.THETABLE foo
      where foo.id = 55555
    )
  )

But the best way to this in SQL is with joins

select tester as TESTNAME, testing as TESTNAMETWO, test as TESTNAMETHREE
from DATABASE.table master
join DATABASETWO.table2 notmaster on master.column = notmaster.column2
join THEOTHERDB.TABLE bar on notmaster.column3 = bar.valuesNeeded
join THEDB.THETABLE foo on foo.id = 5555 AND bar.value = foo.secondValue
where master.number = 55555