locked
Need suggestion on sql join RRS feed

  • Question

  • Hello everybody,

    I want to outer join 2 tables, expected result as below, any suggestion to accomplish this, with many thank!

    Table A

    Staff_ID |  Staff_Name
    --------------------------
    0001      | John
    0002      | Mary
    0003      | SAM
    --------------------------

    Table B

    Pro_ID   |  Staff_ID | Pro_Name  | Qty
    --------------------------------
    P0001    | 0001       | Pro_A        | 10
    P0002    | 0002       | Pro_B        | 8
    P0003    | 0003       | Pro_C        | 5

    -----------------------------------------------

    Expected result table

    Staff_ID   |   Staff_Name  | Pro_ID  | Pro_Name | Qty
    -----------------------------------------------------
    0001       | John          | P0001   | Pro_A    | 10
    0001       | John          | P0002   | Pro_B    | 0 (or null)
    0001       | John          | P0003   | Pro_C    | 0 (or null)
    0002       | Mary          | P0001   | Pro_A    | 0 (or null)
    0002       | Mary          | P0002   | Pro_B    | 8
    0002       | Mary          | P0003   | Pro_C    | 0 (or null)
    0003       | SAM           | P0001   | Pro_A    | 0 (or null)
    0003       | SAM           | P0002   | Pro_B    | 0 (or null)
    0003       | SAM           | P0003   | Pro_C    | 5

    Monday, July 13, 2020 1:34 AM

All replies

  • Hi rupertlp,

    Could you please try with the query below:

    select qty,a.staffid,pro_id into #Table1  from TableB b inner join Tablea a on a.staffid = b.staffid

    select a.staffid,a.staff_name,b.pro_id,b.pro_name,0 as qty into #Table2
    from TableA a,TableB b


    select a.staffid,a.staff_name,a.pro_id,a.pro_name,b.qty from #Table1 a
    left join #Table2 b on
    a.staffid=b.staffid
    and a.pro_id = b.pro_id

    Thanks

    SSBJ

    Tuesday, July 21, 2020 2:16 PM
  • You can use cross apply.

    DECLARE @tblA TABLE (Staff_ID VARCHAR(10), Staff_Name VARCHAR(100));
    DECLARE @tblB TABLE (Pro_ID VARCHAR(10), Staff_ID VARCHAR(10), Pro_Name VARCHAR(100), Qty INT);
    
    INSERT INTO @tblA VALUES
    ('0001','John'),
    ('0002','Mary'),
    ('0003','SAM')
    
    INSERT INTO @tblB VALUES
    ('P0001','0001','Pro_A',10),
    ('P0002','0002','Pro_B',8),
    ('P0003','0003','Pro_C',5)
    
    
    
    SELECT 
    	A.Staff_ID, A.Staff_Name,
    	B.Pro_ID, B.Pro_Name,
    	CASE WHEN A.Staff_ID = B.Staff_ID THEN B.Qty ELSE NULL END
    FROM @tblA as A
    	CROSS APPLY @tblB as B

    Tuesday, July 21, 2020 3:40 PM
  • Please post query questions in the Transact-SQL forum.

    Tuesday, July 21, 2020 3:41 PM