Loading ...
Sorry, an error occurred while loading the content.

SELECT query problem

Expand Messages
  • John Warner
    I have two tables TableA and TableB TableA TaskNbr varchar(25) , EmpID varchar(10) TableB TaskNbr varchar(25) , Respond varchar(10) Respond and EmpID are
    Message 1 of 5 , Jun 20, 2012
    • 0 Attachment
      I have two tables TableA and TableB

      TableA
      TaskNbr varchar(25)
      , EmpID varchar(10)

      TableB
      TaskNbr varchar(25)
      , Respond varchar(10)

      Respond and EmpID are actually the same thing an Identifier assigned to employees, they have different names because in the two tables they come from different sources and the name has meaning regarding the source.

      data:

      TableA
      A123, 23
      A234, 45 <--
      B567, 35
      C234, 40
      A567, null <--
      E234, 8

      TableB
      A123, 23
      A234, 29 <--
      B567, 35
      C234, 40
      A567, 10 <--
      B123, 9
      B789, null

      I need all distinct tasks returned, that is if it occurs in A I need it if it occurs in B I need it. Here is the trick that sort of makes a UNION a problem, if there is a NULL in either table for employee (EmpId, Respond) I want the record where the emp is not null if both tables are null then don't care. But if there is a conflict in Employess see row 2, I want the employee from TableA as its feed is more likely to be correct in this field.

      desired result:
      A123, 23
      A234, 45 <--
      B567, 35
      C234, 40
      A567, 10 <--
      E234, 8
      B123, 9
      B789, null

      The order here from the tables is not important the sort (ORDER BY) would be on Employee not the task. Notice table A wins when the Emps don't match, NULL loses to data in either table for Emp and all task numbers are returned regardless of table and if one of the tasks unique to a table had a NULL for Emp then that task still needs to return

      I've UNIONed the tables and then did a row_number partition by clause and that appears to work but I don't really trust the result in all cases. I'm even looking at a cursor worst case but I am sure there is a set based method to achieve this. The good news performance and scaling wise, neither table will ever have more than 100 to 200 qualifying rows (other fields not included here filter the larger list but provide no help to this problem)

      SQL Server 2008R2, Win 2003R2, both 32 bit version.

      Thanks for your suggestions.

      (Arnie, Paul, I hope life is treating you both well this summer!)

      John Warner
    • Farhan Ahmed
      Hi John, Try out this solution... I hope this will work for you.... Declare @T Table (TaskNum Varchar(10),EmpID varchar(10)) Declare @T2 Table (TaskNum
      Message 2 of 5 , Jun 20, 2012
      • 0 Attachment
        Hi John,

        Try out this solution... I hope this will work for you....


        Declare @T Table (TaskNum Varchar(10),EmpID varchar(10))
        Declare @T2 Table (TaskNum Varchar(10),Respond varchar(10))

        Insert into @T
        Select 'A123', '23'
        Union All
        Select 'A234', '45'
        Union All
        Select 'B567', '35'
        Union All
        Select 'C234', '40'
        Union All
        Select 'A567', null
        Union All
        Select 'E234', '8'

        Insert into @T2
        Select 'A123', '23'
        Union All
        Select 'A234', '29'
        Union All
        Select 'B567', '35'
        Union All
        Select 'C234', '40'
        Union All
        Select 'A567', '10'
        Union All
        Select 'B123', '9'
        Union All
        Select 'B789', NULl

        ;With Cte as (
        Select TaskNum,NULL EmpID from @T A
        Union
        Select TaskNum,Respond from @T2
        )
        Select DISTINCT C.TaskNum,ISNULL(T.EmpID,T2.Respond) EmpID from CTE C
        Left join @T T on C.TaskNum=T.TaskNum
        Left Join @T2 T2 on C.TaskNum=T2.TaskNum



        _________________________________________________________________________________________________________
        Regards,

        Farhan Ahmed
        Programmer Analyst
        The Shams Group
        Karachi, Pakistan
        (+92) 345 2523688
        View my Certifications




        To: SQLQueriesNoCode@yahoogroups.com
        From: john@...
        Date: Wed, 20 Jun 2012 04:53:33 -0400
        Subject: [SQLQueriesNoCode] SELECT query problem





        I have two tables TableA and TableB

        TableA
        TaskNbr varchar(25)
        , EmpID varchar(10)

        TableB
        TaskNbr varchar(25)
        , Respond varchar(10)

        Respond and EmpID are actually the same thing an Identifier assigned to employees, they have different names because in the two tables they come from different sources and the name has meaning regarding the source.

        data:

        TableA
        A123, 23
        A234, 45 <--
        B567, 35
        C234, 40
        A567, null <--
        E234, 8

        TableB
        A123, 23
        A234, 29 <--
        B567, 35
        C234, 40
        A567, 10 <--
        B123, 9
        B789, null

        I need all distinct tasks returned, that is if it occurs in A I need it if it occurs in B I need it. Here is the trick that sort of makes a UNION a problem, if there is a NULL in either table for employee (EmpId, Respond) I want the record where the emp is not null if both tables are null then don't care. But if there is a conflict in Employess see row 2, I want the employee from TableA as its feed is more likely to be correct in this field.

        desired result:
        A123, 23
        A234, 45 <--
        B567, 35
        C234, 40
        A567, 10 <--
        E234, 8
        B123, 9
        B789, null

        The order here from the tables is not important the sort (ORDER BY) would be on Employee not the task. Notice table A wins when the Emps don't match, NULL loses to data in either table for Emp and all task numbers are returned regardless of table and if one of the tasks unique to a table had a NULL for Emp then that task still needs to return

        I've UNIONed the tables and then did a row_number partition by clause and that appears to work but I don't really trust the result in all cases. I'm even looking at a cursor worst case but I am sure there is a set based method to achieve this. The good news performance and scaling wise, neither table will ever have more than 100 to 200 qualifying rows (other fields not included here filter the larger list but provide no help to this problem)

        SQL Server 2008R2, Win 2003R2, both 32 bit version.

        Thanks for your suggestions.

        (Arnie, Paul, I hope life is treating you both well this summer!)

        John Warner






        [Non-text portions of this message have been removed]
      Your message has been successfully submitted and would be delivered to recipients shortly.