Skip site navigation (1) Skip section navigation (2)

Re: Inner Join question

From: "Michael Swierczek" <mike(dot)swierczek(at)gmail(dot)com>
To: "Keith Turner" <kturner(at)cloudsystems(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Inner Join question
Date: 2008-11-07 21:23:23
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
    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,
you want:
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
are included.

I think this is what you want:
SELECT devices.*, rooms.*, bridge.*
FROM devices
LEFT JOIN bridge on
LEFT JOIN room on bridge.roomid =;


On Fri, Nov 7, 2008 at 3:45 PM, Keith Turner <kturner(at)cloudsystems(dot)com> wrote:
> Hi,
> 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 used
> 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)
> devices,rooms,bridge
> bridgeroomid =
> bridgedeviceid=*
> 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:

In response to

pgsql-novice by date

Next:From: Keith TurnerDate: 2008-11-07 21:38:06
Subject: Inner Join question
Previous:From: Keith TurnerDate: 2008-11-07 20:45:49
Subject: Inner Join question

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group