pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedsimple join problem
7+ messages / 5 participants
[nested] [flat]
* simple join problem
@ 2003-02-19 18:51 Matthew Nuzum <cobalt@bearfruit.org>
0 siblings, 3 replies; 7+ messages in thread
From: Matthew Nuzum @ 2003-02-19 18:51 UTC (permalink / raw)
To: pgsql-sql
Sorry for the simple question, but I'm struggling with a join.
I'm creating a view that will show data from 4 tables. The problem is, I
want the view to show a record for every entry in the "users" table, even if
there is no matching entry all or some of the other tables.
Right now my view only shows records that have data in all 4 tables. I know
I've had this problem before and I know there's simple syntax, but I've only
done it with two tables in the join and I (apparently) can't remember the
correct syntax.
Can anyone demonstrate the correct syntax for joining several tables in this
way?
Here's my view definition:
SELECT
users.uid, users.loginid, users."password", users.title,
users.firstname, users.middlename, users.lastname, users.suffix,
users.organization, users.job_title, users_address.address1,
users_address.address2, users_address.address3, users_address.city,
users_address.state, users_address.zip, users_address.country,
users_email.email, users_phone.phone
FROM (((users
LEFT JOIN users_address ON ((users.uid = users_address.uid)))
LEFT JOIN users_email ON ((users.uid = users_email.uid)))
LEFT JOIN users_phone ON ((users.uid = users_phone.uid)))
WHERE (((users_address."primary" = 't'::bool)
AND (users_email."primary" = 't'::bool))
AND (users_phone."primary" = 't'::bool));
I doubt you need the following information, but if you do, here are the
table definitions:
Table "users"
Column | Type | Modifiers
--------------+-----------------------+-----------------------------
uid | integer | not null default nextval(...
loginid | character varying(12) | not null
password | character varying(64) | not null
title | character varying(10) |
firstname | text | not null
middlename | text |
lastname | text |
suffix | character varying(10) |
organization | text |
job_title | text |
Primary key: users_pkey
Table "users_address"
Column | Type | Modifiers
-------------+---------+-----------------------------
uaid | integer | not null default nextval(...
uid | integer |
primary | boolean | default 't'
description | text |
address1 | text |
address2 | text |
address3 | text |
city | text |
state | text |
zip | text |
country | text |
Primary key: users_address_pkey
Table "users_email"
Column | Type | Modifiers
-------------+---------+-----------------------------
ueid | integer | not null default nextval(...
uid | integer |
email | text | not null
primary | boolean | default 't'
description | text |
Primary key: users_email_pkey
Table "users_phone"
Column | Type | Modifiers
-------------+---------+-----------------------------
upid | integer | not null default nextval(...
uid | integer |
phone | text | not null
primary | boolean | default 't'
description | text |
Primary key: users_phone_pkey
My View is be:
View "users_detail"
Column | Type | Modifiers
--------------+-----------------------+-----------
uid | integer |
loginid | character varying(12) |
password | character varying(64) |
title | character varying(10) |
firstname | text |
middlename | text |
lastname | text |
suffix | character varying(10) |
organization | text |
job_title | text |
address1 | text |
address2 | text |
address3 | text |
city | text |
state | text |
zip | text |
country | text |
email | text |
phone | text |
Matthew Nuzum
www.bearfruit.org
cobalt@bearfruit.org
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: simple join problem
@ 2003-02-19 20:08 Richard Huxton <dev@archonet.com>
parent: Matthew Nuzum <cobalt@bearfruit.org>
2 siblings, 0 replies; 7+ messages in thread
From: Richard Huxton @ 2003-02-19 20:08 UTC (permalink / raw)
To: Matthew Nuzum <cobalt@bearfruit.org>; pgsql-sql
On Wednesday 19 Feb 2003 6:51 pm, Matthew Nuzum wrote:
> Sorry for the simple question, but I'm struggling with a join.
>
> I'm creating a view that will show data from 4 tables. The problem is, I
> want the view to show a record for every entry in the "users" table, even
> if there is no matching entry all or some of the other tables.
>
> Right now my view only shows records that have data in all 4 tables. I
> know I've had this problem before and I know there's simple syntax, but
> I've only done it with two tables in the join and I (apparently) can't
> remember the correct syntax.
>
> Can anyone demonstrate the correct syntax for joining several tables in
> this way?
>
> Here's my view definition:
> SELECT
> users.uid, users.loginid, users."password", users.title,
> users.firstname, users.middlename, users.lastname, users.suffix,
> users.organization, users.job_title, users_address.address1,
> users_address.address2, users_address.address3, users_address.city,
> users_address.state, users_address.zip, users_address.country,
> users_email.email, users_phone.phone
> FROM (((users
> LEFT JOIN users_address ON ((users.uid = users_address.uid)))
> LEFT JOIN users_email ON ((users.uid = users_email.uid)))
> LEFT JOIN users_phone ON ((users.uid = users_phone.uid)))
> WHERE (((users_address."primary" = 't'::bool)
> AND (users_email."primary" = 't'::bool))
> AND (users_phone."primary" = 't'::bool));
If there isn't data in all tables users_email.primary can't be "t" can it? Try
adding OR IS NULL inside the brackets and see if that does what you want.
--
Richard Huxton
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: simple join problem
@ 2003-02-19 20:25 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Matthew Nuzum <cobalt@bearfruit.org>
2 siblings, 0 replies; 7+ messages in thread
From: Stephan Szabo @ 2003-02-19 20:25 UTC (permalink / raw)
To: Matthew Nuzum <cobalt@bearfruit.org>; +Cc: pgsql-sql
On Wed, 19 Feb 2003, Matthew Nuzum wrote:
> Sorry for the simple question, but I'm struggling with a join.
>
> I'm creating a view that will show data from 4 tables. The problem is, I
> want the view to show a record for every entry in the "users" table, even if
> there is no matching entry all or some of the other tables.
>
> Right now my view only shows records that have data in all 4 tables. I know
> I've had this problem before and I know there's simple syntax, but I've only
> done it with two tables in the join and I (apparently) can't remember the
> correct syntax.
>
> Can anyone demonstrate the correct syntax for joining several tables in this
> way?
>
> Here's my view definition:
> SELECT
> users.uid, users.loginid, users."password", users.title,
> users.firstname, users.middlename, users.lastname, users.suffix,
> users.organization, users.job_title, users_address.address1,
> users_address.address2, users_address.address3, users_address.city,
> users_address.state, users_address.zip, users_address.country,
> users_email.email, users_phone.phone
> FROM (((users
> LEFT JOIN users_address ON ((users.uid = users_address.uid)))
> LEFT JOIN users_email ON ((users.uid = users_email.uid)))
> LEFT JOIN users_phone ON ((users.uid = users_phone.uid)))
> WHERE (((users_address."primary" = 't'::bool)
> AND (users_email."primary" = 't'::bool))
> AND (users_phone."primary" = 't'::bool));
The where clause undoes the LEFT JOIN.
Maybe something like:
SELECT ...
FROM users LEFT JOIN (select * from users_address where primary='t') AS
users_address ON (users.uid=users_address.uid) ...
I'd thought about just changing the WHERE clause elements to something
like:
(users_address.uaid IS NULL OR users_address."primary" = 't'::bool)
but that'll do the wrong thing if there are matching address but none are
primary (it shouldn't happen presumably, but I don't see anything that
stops it in the table descriptions -- I also don't know if there's an
intention of having multiple primary addresses which I guess could happen
unless primary is part of the pkey for those tables - which would prevent
multiple secondaries, so I assume it isn't)
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: simple join problem
@ 2003-02-19 20:49 Josh Berkus <josh@agliodbs.com>
parent: Matthew Nuzum <cobalt@bearfruit.org>
2 siblings, 1 reply; 7+ messages in thread
From: Josh Berkus @ 2003-02-19 20:49 UTC (permalink / raw)
To: Matthew Nuzum <cobalt@bearfruit.org>; pgsql-sql
Matthew,
> Can anyone demonstrate the correct syntax for joining several tables in this
> way?
Actually, there's several possible answers to your problem. Here's one:
>
> Here's my view definition:
> SELECT
> users.uid, users.loginid, users."password", users.title,
> users.firstname, users.middlename, users.lastname, users.suffix,
> users.organization, users.job_title, users_address.address1,
> users_address.address2, users_address.address3, users_address.city,
> users_address.state, users_address.zip, users_address.country,
> users_email.email, users_phone.phone
> FROM (((users
> LEFT JOIN users_address ON ((users.uid = users_address.uid AND
users_address.primary = TRUE)))
> LEFT JOIN users_email ON ((users.uid = users_email.uid and
users_email.primary = TRUE)))
> LEFT JOIN users_phone ON ((users.uid = users_phone.uid and
users_phone.primary = TRUE))) ;
It's a neat trick that join conditions can be *any* expression that evaluates
to a boolean value.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: simple join problem
@ 2003-02-19 21:15 Matthew Nuzum <cobalt@bearfruit.org>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 7+ messages in thread
From: Matthew Nuzum @ 2003-02-19 21:15 UTC (permalink / raw)
To: josh@agliodbs.com; 'Matthew Nuzum' <cobalt@bearfruit.org>; pgsql-sql
> >
> > Here's my view definition:
> > SELECT
> > users.uid, users.loginid, users."password", users.title,
> > users.firstname, users.middlename, users.lastname, users.suffix,
> > users.organization, users.job_title, users_address.address1,
> > users_address.address2, users_address.address3, users_address.city,
> > users_address.state, users_address.zip, users_address.country,
> > users_email.email, users_phone.phone
> > FROM (((users
> > LEFT JOIN users_address ON ((users.uid = users_address.uid AND
> users_address.primary = TRUE)))
> > LEFT JOIN users_email ON ((users.uid = users_email.uid and
> users_email.primary = TRUE)))
> > LEFT JOIN users_phone ON ((users.uid = users_phone.uid and
> users_phone.primary = TRUE))) ;
>
> It's a neat trick that join conditions can be *any* expression that
> evaluates
> to a boolean value.
That is a neat trick. Thanks for the info everyone. I feel better knowing
that even though it was a simple problem it's not quite as obvious as I
thought.
--
Matthew Nuzum
www.bearfruit.org
cobalt@bearfruit.org
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: simple join problem
@ 2003-02-20 04:02 mallah@trade-india.com
parent: Matthew Nuzum <cobalt@bearfruit.org>
0 siblings, 1 reply; 7+ messages in thread
From: mallah@trade-india.com @ 2003-02-20 04:02 UTC (permalink / raw)
To: cobalt@bearfruit.org; +Cc: josh@agliodbs.com; pgsql-sql
Good to see that you got the answer to ur problem.
But i have a question to u.
Why at all are you keeping 4 tables and joining them too.
why not have a single table with all the merged columns?
In my place i too have a database stuructue similar to you but lately
i realized the other option would have been better. becoz for every
datamining query i am having to join multiple table which is not very favourable
to performance.
i know it may not be the proper forum to discuss this.
regds
mallah.
>> >
>> > Here's my view definition:
>> > SELECT
>> > users.uid, users.loginid, users."password", users.title,
>> > users.firstname, users.middlename, users.lastname, users.suffix, users.organization,
>> > users.job_title, users_address.address1,
>> > users_address.address2, users_address.address3, users_address.city,
>> > users_address.state, users_address.zip, users_address.country,
>> > users_email.email, users_phone.phone
>> > FROM (((users
>> > LEFT JOIN users_address ON ((users.uid = users_address.uid AND
>> users_address.primary = TRUE)))
>> > LEFT JOIN users_email ON ((users.uid = users_email.uid and
>> users_email.primary = TRUE)))
>> > LEFT JOIN users_phone ON ((users.uid = users_phone.uid and
>> users_phone.primary = TRUE))) ;
>>
>> It's a neat trick that join conditions can be *any* expression that evaluates
>> to a boolean value.
>
> That is a neat trick. Thanks for the info everyone. I feel better knowing that even though it
> was a simple problem it's not quite as obvious as I thought.
>
> --
> Matthew Nuzum
> www.bearfruit.org
> cobalt@bearfruit.org
>
>
>
> ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9'
> the postmaster
-----------------------------------------
Get your free web based email at trade-india.com.
"India's Leading B2B eMarketplace.!"
http://www.trade-india.com/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: simple join problem
@ 2003-02-20 04:25 Matthew Nuzum <cobalt@bearfruit.org>
parent: mallah@trade-india.com
0 siblings, 0 replies; 7+ messages in thread
From: Matthew Nuzum @ 2003-02-20 04:25 UTC (permalink / raw)
To: mallah@trade-india.com; +Cc: pgsql-sql
It may not be too off topic...
This database structure allows me to have several means of contact for my
each of my customers. An insert trigger keeps exactly one email address,
phone number and mailing address marked as the primary one for that account.
I use a view to give my easy access to the customer's primary contact
information.
Previously, I used a table structure like this:
users
==========
x|username
|password
|firstname
|lastname
|billing_email
|other_email
|phone
|fax
|tollfree
|cellphone
|pager
|billing_address1
|billing_address2
|billing_address3
|billing_town
|billing_state
|billing_country
|billing_zip
|other_address1
|other_address2
|other_address3
|other_state
|other_country
|other_zip
As you can see, there are a lot of wasted fields. Most of my customers have
just one address, phone number and e-mail on file however, some have
several.
By shifting to this structure, my data is more normal(ized). There are no
limits as to the depth of contact information I can have about my customers.
--
Matthew Nuzum
www.bearfruit.org
cobalt@bearfruit.org
>
> Good to see that you got the answer to ur problem.
>
> But i have a question to u.
>
> Why at all are you keeping 4 tables and joining them too.
> why not have a single table with all the merged columns?
>
> In my place i too have a database stuructue similar to you but lately
> i realized the other option would have been better. becoz for every
> datamining query i am having to join multiple table which is not very
> favourable
> to performance.
>
> i know it may not be the proper forum to discuss this.
>
> regds
> mallah.
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2003-02-20 04:25 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-02-19 18:51 simple join problem Matthew Nuzum <cobalt@bearfruit.org>
2003-02-19 20:08 ` Richard Huxton <dev@archonet.com>
2003-02-19 20:25 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-02-19 20:49 ` Josh Berkus <josh@agliodbs.com>
2003-02-19 21:15 ` Matthew Nuzum <cobalt@bearfruit.org>
2003-02-20 04:02 ` mallah@trade-india.com
2003-02-20 04:25 ` Matthew Nuzum <cobalt@bearfruit.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox