Professional Web Applications Themes

(simple?) query dilemma--help please - MySQL

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 ...

  1. #1

    Default (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.



    Bosconian Guest

  2. #2

    Default Re: (simple?) query dilemma--help please

    >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 Burditt Guest

  3. #3

    Default Re: (simple?) query dilemma--help please

    ditt" <gordonb.hazztburditt.org> wrote in message
    news:11nt0b311odu734corp.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.


    Bosconian Guest

Similar Threads

  1. Simple query, very low performance
    By Bernhard Kornberger in forum MySQL
    Replies: 3
    Last Post: July 6th, 12:27 PM
  2. Count GROUP BY query dilemma
    By Bosconian in forum MySQL
    Replies: 5
    Last Post: December 23rd, 07:03 AM
  3. simple (?) query--help
    By Bosconian in forum MySQL
    Replies: 2
    Last Post: December 20th, 12:43 AM
  4. Simple Query for Search
    By amystephenson in forum Coldfusion - Getting Started
    Replies: 2
    Last Post: May 12th, 01:13 PM
  5. simple query....
    By rodger in forum Microsoft SQL / MS SQL Server
    Replies: 6
    Last Post: July 2nd, 01:17 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139