(simple?) query dilemma--help please

This should be easy, but I've been fudging with this for a while so
perhaps
someone can help.

I have 2 tables: fruit, fruitchoices.

The first (fruit) is a read-only lookup table:

fid, fruit

1, apple
2, banana
3, orange
4, peach
5, pear

The second (fruitchoices) table stores the user's selections:

uid, fid

1, 1
1, 3

I need a query that will produce the entire list of fruit along with a
user's choices. For example:

fid, fruit, uid

1, apple, 1
2, banana, null
3, orange, 1
4, peach, null
5, pear, null

Where 'null' is returned for unselected fruits.

The following query will produce such a list, but only when a uid is not
specified:

SELECT f.*, fc.fruit AS fruitchoice
FROM fruit f
LEFT JOIN fruitchoices fc ON fc.fid = f.fid

Adding a where clause will limit the list to only those chosen fruits:

WHERE fc.uid = 1

Output:

1, apple, 1
3, orange, 1

Can someone show me how to output everything while specifying a uid?

Thanks.

>I have 2 tables: fruit, fruitchoices.
>
>The first (fruit) is a read-only lookup table:
>
>fid, fruit
>
>1, apple
>2, banana
>3, orange
>4, peach
>5, pear
>
>The second (fruitchoices) table stores the user's selections:
>
>uid, fid
>
>1, 1
>1, 3
>
>I need a query that will produce the entire list of fruit along with a
>user's choices. For example:
>
>fid, fruit, uid
>
>1, apple, 1
>2, banana, null
>3, orange, 1
>4, peach, null
>5, pear, null
>
>Where 'null' is returned for unselected fruits.
>
>The following query will produce such a list, but only when a uid is not
>specified:
>
>SELECT f.*, fc.fruit AS fruitchoice
>FROM fruit f
>LEFT JOIN fruitchoices fc ON fc.fid = f.fid

SELECT f.*, fc.fruit AS fruitchoice
FROM fruit f
LEFT JOIN fruitchoices fc ON fc.fid = f.fid AND fc.uid = 1;

Gordon L. Burditt

ditt" <gordonb.hazzt@burditt.org> wrote in message
news:11nt0b311odu734@corp.supernews.com...
> >I have 2 tables: fruit, fruitchoices.
> >
> >The first (fruit) is a read-only lookup table:
> >
> >fid, fruit
> >
> >1, apple
> >2, banana
> >3, orange
> >4, peach
> >5, pear
> >
> >The second (fruitchoices) table stores the user's selections:
> >
> >uid, fid
> >
> >1, 1
> >1, 3
> >
> >I need a query that will produce the entire list of fruit along with a
> >user's choices. For example:
> >
> >fid, fruit, uid
> >
> >1, apple, 1
> >2, banana, null
> >3, orange, 1
> >4, peach, null
> >5, pear, null
> >
> >Where 'null' is returned for unselected fruits.
> >
> >The following query will produce such a list, but only when a uid is not
> >specified:
> >
> >SELECT f.*, fc.fruit AS fruitchoice
> >FROM fruit f
> >LEFT JOIN fruitchoices fc ON fc.fid = f.fid
>
> SELECT f.*, fc.fruit AS fruitchoice
> FROM fruit f
> LEFT JOIN fruitchoices fc ON fc.fid = f.fid AND fc.uid = 1;
>
> Gordon L. Burditt

Gordon,

That was too simple (as suspected.)

I was close, but didn't think to add the uid condition to the JOIN clause.

Thanks for the quick response.