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

Aren't you getting the result you're looking for? Btw, I think the join on pg might unnecesary; it will only filter results if there is no pgid=1. — Mosty Mostacho
– Mosty Mostacho, Commented Apr 21, 2012 at 4:24

Erwin Brandstetter · Accepted Answer · 2012-04-21 17:56:08Z

3

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.

edited Apr 21, 2012 at 17:56

answered Apr 21, 2012 at 13:42

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jsmithy Over a year ago

That worked just like you said Erwin. I had to change some of the table names because they were too long and I wanted to keep it simple. I didn't realize that I created an IN table name. The query works perfect now thanks.

Collectives™ on Stack Overflow

Postgresql Query with joins

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related