Professional Web Applications Themes

Query Challenge - Help Appreciated - Microsoft SQL / MS SQL Server

/* I'm struggling w/ my approach to a query. I have two entity tables, beer and meta, and a many to many table between them w/ a value field. I p xml sent via a varchar parameter into a table variable that contains the meta_id and a value field. I need a query that returns all the distinct beer records that have a record in beer_meta for every record in my table variable w/ string containing value fields. I would like a solution that works w/ any number of records in the table variable and does not have to use ...

  1. #1

    Default Query Challenge - Help Appreciated

    /*
    I'm struggling w/ my approach to a query.

    I have two entity tables, beer and meta, and a many to many table between
    them w/ a value field.
    I p xml sent via a varchar parameter into a table variable that
    contains the meta_id and a value field.
    I need a query that returns all the distinct beer records that have a
    record in beer_meta for every
    record in my table variable w/ string containing value fields. I would
    like a solution that works w/ any number
    of records in the table variable and does not have to use iteration. Is
    this possible? TIA~ PJ
    */

    /* BEGIN SETUP */
    create table beer ( beer_id int )
    create table meta ( meta_id int )
    create table beer_meta
    (
    beer_id int ,
    meta_id int ,
    value varchar(50)
    )

    declare i int
    set i = 1
    while ( i <= 3 )
    begin
    insert beer ( beer_id ) values ( i )
    insert meta ( meta_id ) values ( i )
    set i = i + 1
    end

    insert beer_meta ( beer_id, meta_id, value )
    values ( 1, 1, 'Pilsner')
    insert beer_meta ( beer_id, meta_id, value )
    values ( 1, 2, 'USA')
    insert beer_meta ( beer_id, meta_id, value )
    values ( 1, 3, 'great tasting beer')
    insert beer_meta ( beer_id, meta_id, value )
    values ( 2, 1, 'Lager')
    insert beer_meta ( beer_id, meta_id, value )
    values ( 2, 3, 'mellow blend')
    insert beer_meta ( beer_id, meta_id, value )
    values ( 3, 1, 'Stout')
    insert beer_meta ( beer_id, meta_id, value )
    values ( 3, 2, 'USA')
    insert beer_meta ( beer_id, meta_id, value )
    values ( 3, 3, 'mixed blend beer')
    /* END SETUP */

    go
    declare search table
    (
    meta_id int ,
    value varchar(50)
    )

    insert search ( meta_id, value )
    values ( 2, '%USA%' )
    insert search ( meta_id, value )
    values ( 3, '%beer%' )

    /*
    *** NEEDED QUERY ***

    ======== DESIRED RESULTS ========
    beer_id
    1
    3

    beer records 1 and 3 both have beer_meta records for meta_id 2 contains
    'USA'
    and meta_id 3 contains 'beer'
    */

    delete search

    insert search ( meta_id, value )
    values ( 1, '%Stout%')
    insert search ( meta_id, value )
    values ( 3, '%blend%')

    /*
    *** NEEDED QUERY ***

    ======== DESIRED RESULTS ========
    beer_id
    3

    only beer record 3 has beer_meta records for meta_id 1contains 'Stout'
    and meta_id 3 contains 'blend'
    */

    drop table beer
    drop table meta
    drop table beer_meta


    PJ Guest

  2. #2

    Default Re: Query Challenge - Help Appreciated

    Do:

    SELECT b1.beer_id
    FROM beer_meta b1
    INNER JOIN search s1
    ON s1.meta_id = b1.meta_id
    AND PATINDEX(s1.value, b1.value) > 0 -- or use LIKE
    GROUP BY b1.beer_id
    HAVING COUNT(b1.meta_id) = (SELECT COUNT(*) FROM search) ;

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Guest

  3. #3

    Default Re: Query Challenge - Help Appreciated

    ah, nice...that's just what i came up w/...
    ?: does it make a difference whether or not i predetermine the # of records
    in search?

    declare search_count int
    select search_count = count(*) from search
    ....
    having count(*) = search_count

    in other words, will the query run count(*) from search once or for every
    row scan?

    thx! PJ

    "oj" <com> wrote in message
    news:phx.gbl... [/ref]
    between [/ref]
    Is 
    >
    >[/ref]


    PJ Guest

  4. #4

    Default Re: Query Challenge - Help Appreciated

    Are OJ and pj the same? :)
    "PJ" <com> wrote in message
    news:#phx.gbl... 
    records [/ref]
    like [/ref]
    > between [/ref][/ref]
    would [/ref]
    > Is [/ref][/ref]
    contains 
    > >
    > >[/ref]
    >
    >[/ref]


    Nikhil Guest

  5. #5

    Default Re: Query Challenge - Help Appreciated

    having clause is done on the resultset prior to returning it to the caller,
    hence, count(*) is done only once.

    --
    -oj
    RAC v2.2 & QALite!
    http://www.rac4sql.net


    "PJ" <com> wrote in message
    news:#phx.gbl... 
    records [/ref]
    like [/ref]
    > between [/ref][/ref]
    would [/ref]
    > Is [/ref][/ref]
    contains 
    > >
    > >[/ref]
    >
    >[/ref]


    oj Guest

  6. #6

    Default Re: Query Challenge - Help Appreciated

    ah, good to know...a where clause for aggregates so to speak...thx again!

    "oj" <com> wrote in message
    news:u3s%phx.gbl... 
    caller, 
    > records [/ref]
    every [/ref]
    > like 
    > > between [/ref][/ref]
    a [/ref]
    > would [/ref][/ref]
    iteration. [/ref]
    > contains [/ref][/ref]
    'Stout' 
    > >
    > >[/ref]
    >
    >[/ref]


    PJ Guest

  7. #7

    Default Re: Query Challenge - Help Appreciated

    yup...ya welcome.

    --
    -oj
    RAC v2.2 & QALite!
    http://www.rac4sql.net


    "PJ" <com> wrote in message
    news:#phx.gbl... 
    > caller, 
    > > records [/ref]
    > every [/ref][/ref]
    bm.value [/ref][/ref]
    that [/ref][/ref]
    have 
    > > would [/ref]
    > iteration. 
    > > contains [/ref]
    > 'Stout' 
    > >
    > >[/ref]
    >
    >[/ref]


    oj 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. Help from anyone would be appreciated
    By Joe Picko in forum Macromedia Director Lingo
    Replies: 0
    Last Post: July 15th, 02:28 PM
  3. Comments appreciated! :)
    By Ryan Lee in forum Photography
    Replies: 3
    Last Post: July 3rd, 12:18 PM
  4. QUERY CHALLENGE
    By PJ in forum Microsoft SQL / MS SQL Server
    Replies: 4
    Last Post: July 2nd, 09:35 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