Postgresql Query with joins

Question

I have 6 tables that I'm trying to connect together in 1 query; however, I can't quite figure out how to write the query.

Here are my tables in bold and the columns that they contain.

pg
pgid

ipg
ipgid
roid
pgid

ug
ugid

iug
iuid
roid
ugid

ro
roid
inid

in
inid

My goal is to output the inid field given certain criteria from the pg and ug tables. I want to find all the inid entries where pg.pgid=1 and ug.ugid=2. This involves a a lot of joins and I think some select in statments but I'm not familiar enough with how to piece that all together.

Here is what I have so far...

SELECT inid
FROM in
INNER JOIN ro  ON in.inid  = ro.inid
INNER JOIN iug ON ro.roid  = iug.roid
INNER JOIN ug  ON iug.ugid = ug.ugid
INNER JOIN ipg ON ro.roid  = ipg.roid
INNER JOIN pg  ON ipg.pgid = pg.pgid
WHERE pg.pgid = 1
  AND ug.ugid = 2

Answer 1

You cannot name your table in, that is a reserved word in every SQL standard and in PostgreSQL. You will get a syntax error if you try. You can still force the system to accept it if you double quote the name CREATE TABLE "in" ... but then you have to remember to double-quote it every time you use it and you will get confusing error messages. In short: don't do that.
I renamed the table tbl_in and gave it an alias in the query.

Also, inid is ambiguous as SELECT item. Either use USING as join condition (then only one column inid is in the result set) or table-qualify the column name.

Otherwise the query looks fine:

SELECT i.inid    -- ambiguous without table-qualification
FROM   tbl_in i  -- renamed table
JOIN   ro  ON i.inid   = ro.inid
JOIN   iug ON ro.roid  = iug.roid
JOIN   ug  ON iug.ugid = ug.ugid
JOIN   ipg ON ro.roid  = ipg.roid
JOIN   pg  ON ipg.pgid = pg.pgid
WHERE  pg.pgid = 1
AND    ug.ugid = 2;

---

If you have foreign key constraints between pg and ipg and between ug and iug, then you can simplify:

SELECT i.inid    -- ambiguous without table-qualification
FROM   tbl_in i  -- renamed table
JOIN   ro  ON i.inid   = ro.inid
JOIN   iug ON ro.roid  = iug.roid
JOIN   ipg ON ro.roid  = ipg.roid
WHERE  ipg.pgid = 1
AND    iug.ugid = 2;

... because once found in iug / ipg the values are guaranteed to be present in up / pg.