Home Join tables/get values via foreignkeys going from bottom to top (using a where-clause IN array)? (See example)

Questions

Join tables/get values via foreignkeys going from bottom to top (using a where-clause IN array)? (See example)

January 12, 2022

With three example tables:

A: pk_a, name  
B: pk_b, name, fk_a  
C: pk_c, name, fk_b

How is it possible to go from bottom to top/parent or join/get the corresponding table A and B rows using only pk_c values?

Using asyncpg (python) it was not possible to use WHERE pk_c IN (value1..valueN) – so far I only have this:

conn.fetch("SELECT * FROM C WHERE pk_c = any($1::int[])", data_list)

and not competent enough to know where to go from here. Using left joins I can see how to go from top to bottom (atleast using two tables, not so sure about the SQL with three tables or several left joins), but not from bottom and up and at the same time getting several values in WHERE.
Could someone help me get going with this?

Thanks for your time and help.

>Solution :

Assuming by using only pk_c values means "I only have C ID values" and not "I’m not allowed to mention any primary key column other than C’s anywhere in the SQL", you can just do the joins (any order)

SELECT *
FROM
  a 
  INNER JOIN b ON a.pk_a = b.fk_a
  INNER JOIN c ON b.pk_b = c.fk_b
WHERE
  c.pk_c IN (...)

If you’re strugging to use IN with your query library that’s more like a limitation of the library than any fault in SQL: IN expects a list of values and they cannot be parameterized as a single list. You cannot say:

WHERE x IN (@someParameter)

And then pack an array into @someparameter in your client side code, unless the library you use specifically supports seeing an array and splitting it out/modifying the SQL to be like this (for a 3-long array):

WHERE x IN (@someParameter1,@someParameter2,@someParameter3)

And then providing value array[0] into @someparameter1, array[1] into @someparameter2.

If the library of choice doesn’t do that (or you’re not using a library), you can

do it yourself – concatenate repeatedly a new parameter placeholder onto your IN list, and provide a value for it from the array
pass the data to the DB another way, such as turning it into a JSON array string, sending a single string parameter to the DB (with the json inside) and getting the DB to unpack the JSON into a list of values
take the dangerous approach of "it’s an array of ints; i’ll concat them in directly because in this case it’s not an SQL injection hack risk" – don’t do it with an array of strings though..