Professional Web Applications Themes

QUERY CHALLENGE - Microsoft SQL / MS SQL Server

/* I'm having trouble with a query...and was hoping that someone could shed some light on this for me. TIA~ PJ */ go create table beer ( beer_id int primary key , brand varchar(50) ) go insert beer (beer_id, brand) values (1, 'Bud') insert beer (beer_id, brand) values (2, 'Coors') insert beer (beer_id, brand) values (3, 'Sammy') insert beer (beer_id, brand) values (4, 'Heiny') go create table meta ( meta_id int primary key , field_name varchar(50) , list bit ) go insert meta (meta_id, field_name, list) values (1, 'type', 1) insert meta (meta_id, field_name, list) values (2, 'country', 1) insert ...

  1. #1

    Default QUERY CHALLENGE

    /*
    I'm having trouble with a query...and was hoping that someone could shed
    some light on this for me.
    TIA~ PJ
    */

    go
    create table beer
    (
    beer_id int primary key ,
    brand varchar(50)
    )

    go
    insert beer (beer_id, brand) values (1, 'Bud')
    insert beer (beer_id, brand) values (2, 'Coors')
    insert beer (beer_id, brand) values (3, 'Sammy')
    insert beer (beer_id, brand) values (4, 'Heiny')

    go
    create table meta
    (
    meta_id int primary key ,
    field_name varchar(50) ,
    list bit
    )

    go
    insert meta (meta_id, field_name, list) values (1, 'type', 1)
    insert meta (meta_id, field_name, list) values (2, 'country', 1)
    insert meta (meta_id, field_name, list) values (3, 'alcohol %', 0)
    insert meta (meta_id, field_name, list) values (4, 'distributor', 0)

    go
    create table meta_beer
    (
    beer_id int ,
    meta_id int ,
    value varchar(100) ,
    constraint meta_beer_pk primary key (beer_id, meta_id)
    )

    go
    insert meta_beer (beer_id, meta_id, value) values (1, 1, 'Lager')
    insert meta_beer (beer_id, meta_id, value) values (1, 2, 'USA')
    insert meta_beer (beer_id, meta_id, value) values (1, 3, '.04')
    insert meta_beer (beer_id, meta_id, value) values (2, 1, 'Pilsner')
    insert meta_beer (beer_id, meta_id, value) values (4, 2, 'Holland')
    insert meta_beer (beer_id, meta_id, value) values (4, 3, '.05')

    /*
    DESIRED RESULT:
    I would like to get a row for every beer/meta field combination
    where the list field in meta is equal to 1
    and whether or not a value exists in the many to many table.
    ===
    beer_id metafield_id value
    1 1 Lager
    1 2 USA
    2 1 Pilsner
    2 2 NULL
    3 1 NULL
    3 2 NULL
    4 1 NULL
    4 2 Holland
    */

    drop table beer
    drop table meta
    drop table meta_beer


    PJ Guest

  2. #2

    Default Re: QUERY CHALLENGE

    I've come up with this, but I don't believe this type of query is ansi
    compliant anymore...if someone has a better solution, I would really
    appreciate it....thx.

    select r.beer_id, r.meta_id, mb.value
    from meta_beer mb
    right outer join (select beer_id, meta_id
    from beer, meta
    where list = 1) r on mb.beer_id = r.beer_id and mb.meta_id = r.meta_id
    order by r.beer_id

    "PJ" <pjwalhotmail.com> wrote in message
    news:%233WoWTMQDHA.1612TK2MSFTNGP11.phx.gbl...
    > /*
    > I'm having trouble with a query...and was hoping that someone could shed
    > some light on this for me.
    > TIA~ PJ
    > */
    >
    > go
    > create table beer
    > (
    > beer_id int primary key ,
    > brand varchar(50)
    > )
    >
    > go
    > insert beer (beer_id, brand) values (1, 'Bud')
    > insert beer (beer_id, brand) values (2, 'Coors')
    > insert beer (beer_id, brand) values (3, 'Sammy')
    > insert beer (beer_id, brand) values (4, 'Heiny')
    >
    > go
    > create table meta
    > (
    > meta_id int primary key ,
    > field_name varchar(50) ,
    > list bit
    > )
    >
    > go
    > insert meta (meta_id, field_name, list) values (1, 'type', 1)
    > insert meta (meta_id, field_name, list) values (2, 'country', 1)
    > insert meta (meta_id, field_name, list) values (3, 'alcohol %', 0)
    > insert meta (meta_id, field_name, list) values (4, 'distributor', 0)
    >
    > go
    > create table meta_beer
    > (
    > beer_id int ,
    > meta_id int ,
    > value varchar(100) ,
    > constraint meta_beer_pk primary key (beer_id, meta_id)
    > )
    >
    > go
    > insert meta_beer (beer_id, meta_id, value) values (1, 1, 'Lager')
    > insert meta_beer (beer_id, meta_id, value) values (1, 2, 'USA')
    > insert meta_beer (beer_id, meta_id, value) values (1, 3, '.04')
    > insert meta_beer (beer_id, meta_id, value) values (2, 1, 'Pilsner')
    > insert meta_beer (beer_id, meta_id, value) values (4, 2, 'Holland')
    > insert meta_beer (beer_id, meta_id, value) values (4, 3, '.05')
    >
    > /*
    > DESIRED RESULT:
    > I would like to get a row for every beer/meta field combination
    > where the list field in meta is equal to 1
    > and whether or not a value exists in the many to many table.
    > ===
    > beer_id metafield_id value
    > 1 1 Lager
    > 1 2 USA
    > 2 1 Pilsner
    > 2 2 NULL
    > 3 1 NULL
    > 3 2 NULL
    > 4 1 NULL
    > 4 2 Holland
    > */
    >
    > drop table beer
    > drop table meta
    > drop table meta_beer
    >
    >

    PJ Guest

  3. #3

    Default Re: QUERY CHALLENGE

    You can do this in many ways...

    SELECT b1.beer_id, m1.meta_id, mb1.Value
    FROM beer b1
    CROSS JOIN meta m1
    LEFT OUTER JOIN meta_beer mb1
    ON mb1.beer_id = b1.beer_id
    AND mb1.meta_id = m1.meta_id
    WHERE list = 1
    ORDER BY b1.beer_id, m1.meta_id ;

    -- or you can use a sub-query like:

    SELECT b1.beer_id, m1.meta_id,
    ( SELECT Value
    FROM meta_beer mb1
    WHERE mb1.beer_id = b1.beer_id
    AND mb1.meta_id = m1.meta_id ) AS "value"
    FROM beer b1
    CROSS JOIN meta m1
    WHERE list = 1
    ORDER BY 1, 2

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Sen Guest

  4. #4

    Default Re: QUERY CHALLENGE

    works perfectly...thanks. cross join is the ticket, which is probably much
    better than
    FROM table1, table2

    "Anith Sen" <anithbizdatasolutions.com> wrote in message
    news:%23FhvxoMQDHA.3192TK2MSFTNGP10.phx.gbl...
    > You can do this in many ways...
    >
    > SELECT b1.beer_id, m1.meta_id, mb1.Value
    > FROM beer b1
    > CROSS JOIN meta m1
    > LEFT OUTER JOIN meta_beer mb1
    > ON mb1.beer_id = b1.beer_id
    > AND mb1.meta_id = m1.meta_id
    > WHERE list = 1
    > ORDER BY b1.beer_id, m1.meta_id ;
    >
    > -- or you can use a sub-query like:
    >
    > SELECT b1.beer_id, m1.meta_id,
    > ( SELECT Value
    > FROM meta_beer mb1
    > WHERE mb1.beer_id = b1.beer_id
    > AND mb1.meta_id = m1.meta_id ) AS "value"
    > FROM beer b1
    > CROSS JOIN meta m1
    > WHERE list = 1
    > ORDER BY 1, 2
    >
    > --
    > - Anith
    > ( Please reply to newsgroups only )
    >
    >

    PJ Guest

  5. #5

    Default Re: QUERY CHALLENGE

    >> .. which is probably much better than FROM table1, table2 <<

    That is nothing but a CROSS JOIN, anyway :-)

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Sen Guest

Similar Threads

  1. Need Help! - A real query challenge
    By Leonard B in forum Macromedia ColdFusion
    Replies: 0
    Last Post: April 21st, 07:25 AM
  2. Challenge #33
    By Grant Dixon in forum Adobe Photoshop Elements
    Replies: 60
    Last Post: September 27th, 10:14 PM
  3. Challenge #32
    By Grant Dixon in forum Adobe Photoshop Elements
    Replies: 55
    Last Post: September 19th, 01:54 AM
  4. Challenge #29
    By Grant Dixon in forum Adobe Photoshop Elements
    Replies: 23
    Last Post: August 28th, 11:37 PM
  5. Challenge? dynamic query in store procedure
    By Alan in forum Oracle Server
    Replies: 0
    Last Post: December 8th, 07:10 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