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) ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
