Should have used reply all - solution works and appreciated and now
posted to group.
Can't do the devices.*, rooms.*, bridge.* as I get the error "column ID
is duplicated" - but explicitly naming the columns and using AS on these
is no big deal
The join worked, which is what I'm really thankful for - wasn't sure
about the syntax of adding a join to a join in that way - that's why
it's a novice list.
From: Michael Swierczek [mailto:mike(dot)swierczek(at)gmail(dot)com]
Sent: Friday, November 07, 2008 3:23 PM
To: Keith Turner
Subject: Re: [NOVICE] Inner Join question
The * was a problem because the query analyzer did not know which
table to grab all columns from. If you want all columns from more
than one table, you have to do alias.* for each one. In this case,
select devices.*, rooms.*, bridge.* from ....
If you want devices to always be listed, even with no bridge entries,
you would do a left join to guarantee all rows from the first table
I think this is what you want:
SELECT devices.*, rooms.*, bridge.*
LEFT JOIN bridge on bridge.deviceid=devices.id
LEFT JOIN room on bridge.roomid = rooms.id;
On Fri, Nov 7, 2008 at 3:45 PM, Keith Turner <kturner(at)cloudsystems(dot)com>
> I have three tables
> 1) A list of devices
> 2) List of rooms
> 3) A bridge table that relates the two using each table's ID column
> Any device can be in 0 to All rooms
> I want to create a view that joins both tables that in includes all
> columns from all 3 tables.
> I'm not sure how to link joins in the Postgres SQL syntax. I'm more
> to the =* one.
> I want to do something like
> SELECT (explicit list of all tables columns - didn't like * but was ok
> with actual list)
> bridgeroomid = rooms.id
> so that devices with no associated rooms would have null values for
> those columns but be listed.
> Any advice appreciated.
> Sent via pgsql-novice mailing list (pgsql-novice(at)postgresql(dot)org)
> To make changes to your subscription:
pgsql-novice by date
|Next:||From: gcj||Date: 2008-11-11 15:23:38|
|Subject: Automating Postgres Backup|
|Previous:||From: Michael Swierczek||Date: 2008-11-07 21:23:23|
|Subject: Re: Inner Join question|