Thread: join table with empty fields and default

Hi, suppose I have two tables

table User
{
    id integer,
    name text,
    address text
}

Table UserWork
{
    userid integer references User(id),
    work text
}

Suppose I have three users

id    name    address
1     Tony      main street
2     Peter     Big ave
3     Richard  Loop Blvd

And two of them work

UserWork
userid        work
1               programming
3               studying

I would like to join table user and userwork, where if a user has a
work, I list it. If he does not, I give it some default value "no work"

Join User and UserWork
id    name    address              work
1     Tony      main street        programming
2     Peter     Big ave              no work
3     Richard  Loop Blvd          studying

How can I write the sql?

Thanks,

qq

On Sat, Jul 22, 2006 at 12:47:42PM -0700, quickcur@yahoo.com wrote:
> I would like to join table user and userwork, where if a user has a
> work, I list it. If he does not, I give it some default value "no work"

You could use an outer join and COALESCE.

http://www.postgresql.org/docs/8.1/interactive/tutorial-join.html
http://www.postgresql.org/docs/8.1/interactive/queries-table-expressions.html#QUERIES-JOIN
http://www.postgresql.org/docs/8.1/interactive/functions-conditional.html#AEN12639

Example:

SELECT u.id, u.name, u.address, COALESCE(uw.work, 'no work') AS work
FROM "user" AS u
LEFT OUTER JOIN userwork AS uw ON uw.userid = u.id;

--
Michael Fuhr

Thank you for your post. To make things a little bit complicated:

Suppose I have another table UserInformation

UserInfomation
{
    userid integer referenes user(id),
     mothername text
}

I would like methername also appear in the final join. Suppose each
user must have a mothername.

Could you please whow me the sql again?

Thanks again,

qq



Michael Fuhr wrote:
> On Sat, Jul 22, 2006 at 12:47:42PM -0700, quickcur@yahoo.com wrote:
> > I would like to join table user and userwork, where if a user has a
> > work, I list it. If he does not, I give it some default value "no work"
>
> You could use an outer join and COALESCE.
>
> http://www.postgresql.org/docs/8.1/interactive/tutorial-join.html
> http://www.postgresql.org/docs/8.1/interactive/queries-table-expressions.html#QUERIES-JOIN
> http://www.postgresql.org/docs/8.1/interactive/functions-conditional.html#AEN12639
>
> Example:
>
> SELECT u.id, u.name, u.address, COALESCE(uw.work, 'no work') AS work
> FROM "user" AS u
> LEFT OUTER JOIN userwork AS uw ON uw.userid = u.id;
>
> --
> Michael Fuhr
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly

On Sat, Jul 22, 2006 at 01:38:38PM -0700, quickcur@yahoo.com wrote:
> Suppose I have another table UserInformation
>
> UserInfomation
> {
>     userid integer referenes user(id),
>      mothername text
> }
>
> I would like methername also appear in the final join. Suppose each
> user must have a mothername.
>
> Could you please whow me the sql again?

Same query as before but with an additional join for the new table.
See the documentation links in my previous response for information
about join types, syntax, and examples.

What have you tried, what results were you expecting, and what
results did you get?  If you're getting the wrong results then let's
investigate so we can understand why they're wrong.

--
Michael Fuhr