Thread: simple join problem
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
defaultnextval(...loginid | character varying(12) | not nullpassword | character varying(64) | not nulltitle
| character varying(10) |firstname | text | not nullmiddlename | 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 nullprimary | 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 nullprimary | 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
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
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)
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 BerkusAglio Database SolutionsSan Francisco
> > > > 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
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/
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.