Professional Web Applications Themes

Guru Advice Needed for Query on Interesting Data Scenario - Microsoft SQL / MS SQL Server

1. Are you looking for something like : SELECT Id FROM Table1 t1 INNER JOIN CodePairs c1 ON (c1.Code1 = t1.Code OR c1.Code2 = t1.Code) GROUP BY Id HAVING COUNT(DISTINCT Code) >= 2 2. I am not sure if I understood your narrative or not. Are you referring to a "comparison" of columns along the lines of : WHERE c2.Division = COALESCE(NULLIF(t1.Division, 0), c2.Division) AND c2.SubDivision = COALESCE(NULLIF(t1.SubDivision, 0), c2.SubDivision)) AND c2.Section = COALESCE(NULLIF(t1.Section, 0), c2.Section) ... In any case, I stringly suspect that the tables are not designed correctly. It looks like a cooked-up structure which is forced to ...

  1. #1

    Default Re: Guru Advice Needed for Query on Interesting Data Scenario

    1. Are you looking for something like :

    SELECT Id
    FROM Table1 t1
    INNER JOIN CodePairs c1
    ON (c1.Code1 = t1.Code
    OR c1.Code2 = t1.Code)
    GROUP BY Id
    HAVING COUNT(DISTINCT Code) >= 2

    2. I am not sure if I understood your narrative or not. Are you referring to
    a "comparison" of columns along the lines of :

    WHERE c2.Division = COALESCE(NULLIF(t1.Division, 0), c2.Division)
    AND c2.SubDivision = COALESCE(NULLIF(t1.SubDivision, 0), c2.SubDivision))
    AND c2.Section = COALESCE(NULLIF(t1.Section, 0), c2.Section)
    ...

    In any case, I stringly suspect that the tables are not designed correctly.
    It looks like a cooked-up structure which is forced to include some
    significant details without much integrity. Without a clear understanding of
    the business model & data requirements, it is even harder to suggest a
    formal DDL, though.

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Guest

  2. #2

    Default Re: Guru Advice Needed for Query on Interesting Data Scenario

    Thanks for ur helpful reply. But there are some issues:

    1. This would only give me Table1.ID field. I also need the corresponding
    CodePairId that it hits against in the Codepairs table. Also the same record
    from Table1 could hit against multiple code pairs, in which case ur query
    would not give all the results.

    2. You're right , I do need to work out the specs a bit and see. Please
    ignore this issue for now.

    Thanks again!
    JGP

    "Anith Sen" <com> wrote in message
    news:O#$phx.gbl... 
    to 
    c2.SubDivision)) 
    correctly. 
    of 


    Chumma Guest

  3. #3

    Default Re: Guru Advice Needed for Query on Interesting Data Scenario

    Thanks Celko! You nailed it. I know us database newbies can be pretty
    irritating so I appreciate your patience even more.

    Back to the issue. I got the results that I needed, but there's some tricky
    filtering that needs to be done to get the final resultset, as follows:

    1. For each particular value of D.id, keep only records which have the most
    non-wildcard matches across Division, SubDivision, Section and SubSection.

    2. Filter results of previous step by finding the first non-wildcard match
    starting from the lowest level. That is, first non-wildcard match at
    subsection level; If only wild-card value exists at subsection level, then
    look for non-wildcard match at section level..and so on upto Division level.

    Hence you should endup with just one record for each (D1.id, Code1, Code2)
    combo.

    So, based on DDL from your previous posting and data as follows:

    Row1: INSERT INTO Foobar VALUES('A', 1, 1, 1, 1, 333)
    Row2: INSERT INTO Foobar VALUES('A', 1, 1, 1, 1, 444)
    Row3: INSERT INTO Foobar VALUES('A', 1, 1, 1, 1, 555)
    Row4: INSERT INTO Foobar VALUES('B', 1, 2, 1, 1, 555)
    Row5: INSERT INTO Foobar VALUES('B', 1, 2, 1, 1, 666)

    Row1: INSERT INTO CodePairs VALUES(1, NULL, NULL, NULL, 333, 444, 'X')
    Row2: INSERT INTO CodePairs VALUES(1, 1, NULL, NULL, 333, 444, 'L')
    Row3: INSERT INTO CodePairs VALUES(1, 1, 1, NULL, 333, 444, 'E')
    Row4: INSERT INTO CodePairs VALUES(1, 1, NULL, 1, 333, 444, 'P')
    Row5: INSERT INTO CodePairs VALUES(1, 1, NULL, 1, 555, 777, 'W')
    Row6: INSERT INTO CodePairs VALUES(1, 2, NULL, NULL, 555, 777, 'Q')
    Row7: INSERT INTO CodePairs VALUES(1, 2, NULL, 1, 555, 777, 'R')
    Row8: INSERT INTO CodePairs VALUES(1, NULL, 1, NULL, 555, 666, 'B')
    Row9: INSERT INTO CodePairs VALUES(1, 1, NULL, NULL, 555, 666, 'A')


    The query output for:


    SELECT D.id, P.*
    FROM Codepairs AS P, DoubleFoobar AS D
    WHERE COALESCE (P.division, D.division) = D.division
    AND COALESCE (P.subdivision, D.subdivision) = D.subdivision
    AND COALESCE (P.section, D.section) = D.section
    AND COALESCE (P.subsection, D.subsection) = D.subsection
    AND COALESCE (P.code1, D.code1) = D.code1
    AND COALESCE (P.code2, D.code2) = D.code2


    is:

    ID Div SubDiv Sec SubSec Cd1 Cd2 Action
    A 1 1 1 NULL 333 444 E
    A 1 1 NULL NULL 333 444 L
    A 1 1 NULL 1 333 444 P
    A 1 NULL NULL NULL 333 444 X
    B 1 NULL 1 NULL 555 666 B
    B 1 1 NULL NULL 555 666 A


    Based on the filtering that I mentioned above, the final resultset should
    be:

    ID Cd1 Cd2 Action
    A 333 444 P
    B 555 666 B

    Do you have any ideas?
    Thanks,
    Chumma


    Chumma Guest

  4. #4

    Default Re: Guru Advice Needed for Query on Interesting Data Scenario

    >> Thanks Celko! You nailed it. I know us database newbies can be pretty
    irritating so I appreciate your patience even more. <<

    Actually, I am rude to everybody. If I had any friends, you could ask
    them :)

    I have to get to a meeting right now, so let me get back with you. But
    a quick idea: use a case expression to give points for each non-null in
    the four columns with more points for lower levels, then return the
    row(s) with the highest scores in each group.

    --CELKO--
    ===========================
    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Joe Guest

  5. #5

    Default Re: Guru Advice Needed for Query on Interesting Data Scenario

    Thanks again. I'll give it a shot and post the results.
    -Chumma


    Chumma Guest

  6. #6

    Default Re: Guru Advice Needed for Query on Interesting Data Scenario


    SELECT D.id, P.*,
    (CASE WHEN P.subsection IS NOT NULL THEN 8 ELSE 0 END
    + CASE WHEN P.section IS NOT NULL THEN 4 ELSE 0 END
    + CASE WHEN P.subdivision IS NOT NULL THEN 2 ELSE 0 END
    + CASE WHEN P.division IS NOT NULL THEN 1 ELSE 0 END)
    AS score
    FROM Codepairs AS P, DoubleFoobar AS D
    WHERE COALESCE (P.division, D.division) = D.division
    AND COALESCE (P.subdivision, D.subdivision) = D.subdivision
    AND COALESCE (P.section, D.section) = D.section
    AND COALESCE (P.subsection, D.subsection) = D.subsection
    AND COALESCE (P.code1, D.code1) = D.code1
    AND COALESCE (P.code2, D.code2) = D.code2;

    Now look for the MAX(score) within each id.

    --CELKO--
    ===========================
    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Joe Guest

Similar Threads

  1. Flash Guru needed
    By Launchfire in forum Macromedia Flash Ad Development
    Replies: 3
    Last Post: June 17th, 04:55 PM
  2. advice needed... cf write data to xml file for use in flash app thatuses xml file
    By Donna Casey in forum Coldfusion Flash Integration
    Replies: 7
    Last Post: November 30th, 10:38 PM
  3. Control Guru Needed
    By Joe in forum ASP.NET Building Controls
    Replies: 0
    Last Post: January 20th, 02:43 AM
  4. FLash GURU needed (paying job)
    By Jon Fabris in forum Macromedia Flash
    Replies: 0
    Last Post: October 14th, 03:02 PM
  5. guru advice needed on temporary table problem
    By maxhodges in forum Microsoft SQL / MS SQL Server
    Replies: 4
    Last Post: August 21st, 01:44 AM

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