Postgres sql query difficult join

Question

Here are the tables I have:

Table A which has entries with "item" and "grade" fields
Table B which has entries with A.id
Tuple table B-C

I want all the A entries that have item= "x" and grade = "y" And all the C entries that are associated with a B entry that is associated with an A entry that has item = "x" and grade = "y"

For example

A table:

    A.item = "x", A.Grade = "y", A.id = 1
    A.item = "x", A.Grade = "y", A.id = 2
    A.item = "x", A.Grade = "y", A.id = 3
    A.item = "r", A.Grade = "z", A.id = 4

B Table

   B.AID = 1, B.id = 10
   B.AID = 1, B.id = 11
   B.AID = 2, B.id = 13
   B.AID = 3, B.id = 14
   B.AID = 4, B.id = 15

B-C Tuple Table

   BID = 10, CID = 20
   BID = 11, CID = 20
   BID = 13, CID = 20
   BID = 15, CID = 21

The query should return all the entries in the A table and the entry 20 but not 21 in the C table because C.id = 21 is only tupled with a B that is associated with an A that does not meet the item and grade requirements.

Answer 1

The associations, while sounding complicated in written form, are just a simple join among three tables: a joins to b joins to c.

You identify how the columns need to be joined: "a B entry that is associated with an A entry", and looking at the columns sounds like you want to join on b.aid = a.id. Similarly for b and c.

SELECT ...
FROM
    a
    JOIN b ON b.aid = a.id
    JOIN b_c ON b_c.bid = b.id
WHERE
    ...

This constructs the original dataset before it was split into the three normalised tables.

The next step is to filter by the given conditions. You only want rows where " item = "x" and grade = "y"", so add those to WHERE clause prefixing them with the table name, which is optional in this case):

WHERE
    a.item = 'x'
    AND a.grade = 'y'

Finally, you can pick which columns you really need, in the SELECT clause. I'm gussing SELECT b_c.cid would do. Though if you also have a c table you might want to join on that table, too, and select columns from it.