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

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.

CodePudding user response：

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..

CodePudding user response：

SELECT
    *
FROM "C"
LEFT JOIN "B" ON "fk_b" = "pk_b"
LEFT JOIN "A" ON "fk_a" = "pk_a"
WHERE
    "pk_c" IN (2, 3, 5)

Because of the LEFT JOIN you will also get a row in the result if there is a row in C that does not have a matching row in "B". Same for the relation from "B" to "A". But since you mention parent/child relations that is probably never the case and then an INNER JOIN would also work.

CodePudding user response：

You join the foreign keys to the primary keys.

Since the criteria are on C, you can start with C.
But if it's only INNER JOIN's, then the order of the joins doesn't really matter.
Postgresql will try to use the fastest methods to get the data anyhow.

SELECT * 
FROM C AS c
JOIN B AS b ON b.pk_b = c.fk_b
JOIN A AS a ON a.pk_a = b.fk_a
WHERE c.pk_c = ANY($1::int[])

If you want all of C, regardless if there's a fk_b or fk_a. Then you can simply use LEFT JOIN.

SELECT * 
FROM C AS c
LEFT JOIN B AS b ON b.pk_b = c.fk_b
LEFT JOIN A AS a ON a.pk_a = b.fk_a
WHERE c.pk_c = ANY($1::int[])