dcsimg
Results 1 to 9 of 9

Thread: [RESOLVED] If you wouldn't mind helping me with a query...

  1. #1

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Sitting in an empty room trying to forget the past
    Posts
    4,107

    Resolved [RESOLVED] If you wouldn't mind helping me with a query...

    I've gotten pretty far with this on my own (sql queries aren't my strong suit) but I need to code one more criterion and it's too specific to google so I hope you guys can help.

    I have a table called xtblEmpAssignmentJob. It stores information on a sale our salespeople have made. It has a foreign key to our Jobs table.
    A record in the table will say whether the salesperson IsPrimary or not. So 1 means yes and 0 means no.
    You can only have one record IsPrimary = 1 and multiple records IsPrimary = 0. You can have a job with all records IsPrimary = 0.
    I want to join to this table in such a way that the join returns one record. If there is an IsPrimary =1 record I want that one and it will give me the employeeID that is primary.
    If there is no IsPrimary = 1 record, I want one record returned to represent the one or more records existing with IsPrimary = 0. I don't care to have an employeeID.

    Here is my join:
    Code:
    select *
    FROM            Jobs 
    LEFT OUTER JOIN xtblEmpAssignmentJob AS ea ON ea.JobControl = Jobs.Control AND (IsPrimary = 1 OR (IsPrimary = 0 AND EmpAssignmentJobControl = 
            (
               SELECT MAX([EmpAssignmentJobControl]) 
               FROM [dbo].[xtblEmpAssignmentJob] z 
               WHERE z.JobControl = Jobs.Control
            )))
    This is eliminating multiple IsPrimary = 0 records and only returning me one, but I am still getting the IsPrimary = 1 as well. I want either the IsPrimary record or if there is no IsPrimary then one of the not IsPrimary...ugh hope this makes sense!
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  2. #2

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Sitting in an empty room trying to forget the past
    Posts
    4,107

    Re: If you wouldn't mind helping me with a query...

    Oh, I think I got it!

    Code:
    LEFT OUTER JOIN xtblEmpAssignmentJob AS ea ON ea.JobControl = Jobs.Control AND (IsPrimary = 1 OR (IsPrimary = 0 AND EmpAssignmentJobControl = 
            (
               SELECT top 1 ([EmpAssignmentJobControl]) 
               FROM [dbo].[xtblEmpAssignmentJob] z 
               WHERE z.JobControl = Jobs.Control order by jobcontrol, IsPrimary desc
            )))
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  3. #3

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Sitting in an empty room trying to forget the past
    Posts
    4,107

    Re: If you wouldn't mind helping me with a query...

    It looks good. I took out the order by jobcontrol (realizing that was redundant) and just left it order by IsPrimary desc. Sorry to have posted but sometimes you're drawing a blank and then a few minutes later you have an idea!
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  4. #4
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,452

    Re: [RESOLVED] If you wouldn't mind helping me with a query...

    Yeah, that was my first instinct... a select top 1, with an order by using the IsPrimary..


    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  5. #5

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Sitting in an empty room trying to forget the past
    Posts
    4,107

    Re: [RESOLVED] If you wouldn't mind helping me with a query...

    Thanks for the validation! That means a lot coming from you.
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  6. #6
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,852

    Re: [RESOLVED] If you wouldn't mind helping me with a query...

    I would personally use Row_Number() Over (Partition on JobControl, Order by IsPrimary desc) so that Row=1 will always be the one you want. I would do this into a TEMP TABLE so that the join was to something pre-built.

    That Row_Number() function becomes a column in the TEMP TABLE.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  7. #7

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Sitting in an empty room trying to forget the past
    Posts
    4,107

    Re: [RESOLVED] If you wouldn't mind helping me with a query...

    Of course you personally would...!
    Are you saying I may not get the right results the way I am doing it?
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  8. #8
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,852

    Re: [RESOLVED] If you wouldn't mind helping me with a query...

    No - I think you method will work. Using a temp table and Row_Number() allows the "work" of identifying the "first entry" all done at once. I just appreciate the economy of that.

    If you ever had TWO records with IsPrimary=1 then this would not actually work. Actually you can get rid of all the IsPrimary=1 and IsPrimary=0 - don't you always just want to Jobcontrol that is TOP 1?

    Code:
    (IsPrimary = 1 OR (IsPrimary = 0 AND EmpAssignmentJobControl = 
            (
               SELECT top 1 ([EmpAssignmentJobControl]) 
               FROM [dbo].[xtblEmpAssignmentJob] z 
               WHERE z.JobControl = Jobs.Control order by jobcontrol, IsPrimary desc
            )))

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  9. #9

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Sitting in an empty room trying to forget the past
    Posts
    4,107

    Re: [RESOLVED] If you wouldn't mind helping me with a query...

    No, if there's a primary sales person I want his row and if there is no primary salesperson I just want a row to know that forecast data has been created for this sale. One record or more, it doesn't matter.
    I appreciate that you appreciate economy. It's just easier for me to maintain it if I worked the logic out in my own head!
    Thanks.
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width