Post reply

Horrible Nested Queries

  • August 5, 2011 at 6:43 am

    #241195

    Hi guys, This is an SQL2000 question so no RANK() function or INTERSECT solutions please.

    I am suffering Friday Brain fade.

    I have two tables.

    Table 1 contains three fields: Staff_seq_number, Staff_ID, Staff_Team

    Table 2 contains two fields: Staff_ID, Staff_team.

    Staff_ID in table 2 will always exist in table 1.

    There may be more than one record with the same staff_ID in table 1

    There may be 0 or 1 records in table 1 that match staff_ID and Staff_team in table 2.

    Currently the join criteria is simply on Staff_ID. The dataset gets aggregated (count) on Staff_Seq_number so in some cases I am double counding the Table 2 records because I find more than one match in Table 1.

    The join criteria needs to be :

    If you find a match on both Staff_ID and Staff_Team then use that record

    If not then take the first record in table 1 that matches on Staff_ID.

    It would be easy in SQL2005 because I would simply rank the records on a join and only take the top ranked result for each table2 record but I can't work it out in SQL 2000.

    I have tried setting the criteria as a derived table and a co-related sub query and can't get either to return consistent results. I am sure it's possible, but my brains is fried.

  • August 5, 2011 at 7:01 am

    #1364319

    It's probably easier to change your aggregate to a distinct count.

    Count(DISTINCT Staff_Seq_Num)

    You may also need to use another field if that doesn't give you the correct counts.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • August 5, 2011 at 7:10 am

    #1364327

    Sorry, wasn't clear enough in the original post/

    The aggregate is actually on another field (Table2.contact_ID) but the grouping is by Staff_seq_num

    so I would still be getting duplicate records.

  • August 5, 2011 at 7:13 am

    #1364332

    It's very eay in sql2000 and I will be more than happy to help you write a query to do this if you will provide a ddl and sample data insert script for setting up the case.

    Please refer to link in my signature to find out how to do so.

    Also, if you will specify exactly what you want to see in resultset that will help too.

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • August 5, 2011 at 8:41 am

    #1364396

    Thanks for the offer Eugene,

    I will try to do that on Monday (almost day end here now...)

    I will need to strip the current query down to the offending tables and some sample data.

    for the time being, I am creating a derived table which is the min(Staff_seq_num) and Staff_ID and then using that to link back the full table. It's not right because activity get allocated to the wrong team, but at least it is not being duplicated.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply