none
need to get only distinct rows with using CTE

    Question

  • I have 2 Tables, Table A and Table B as like below:

    I need output as like below.

    Please let me know how using select query I can extract the data. There will be no temp table or table variable.

    Thanks,

    Saikat

    Thursday, March 15, 2012 12:30 PM

Answers

  • There was duplicate issue in Table A. Now, it was fixed .Now in the Table A there is only distint rows storing based on BANumber.So I can now get the desired output by simple Inner join with Table A and Table B.

    Thanks Paresh for the attempt but now it was not needed.

    thanks

    Saikat

    • Marked as answer by Stephanie Lv Friday, March 16, 2012 4:40 AM
    Thursday, March 15, 2012 12:56 PM

All replies

  • the below query might help

    select DISTINCT A.banumber,material,BusinessArrangementID
    FROM tableA A
    JOIN tableB B
    ON   A.banumber = b.banumber
    


    Nothing is Permanent... even Knowledge....
    My Blog

    Thursday, March 15, 2012 12:34 PM
  • Hi Surendra,

    INNER JOIN will not work here because BANumber is not unique.So if I use JOIN it will work like CROSS JOIN.

    Thanks,
    Saikat

    Thursday, March 15, 2012 12:40 PM
  • Please, post table schema, sample data in the form of "insert" statements, and the logic to apply in order to get the expected result. This way we do not have to guess data types, column names, etc, and just copying and pasting will be enough to reproduce the problem in our side.


    AMB

    Some guidelines for posting questions...

    Thursday, March 15, 2012 12:44 PM
    Moderator
  • you can use this if help :

    DECLARE @A TABLE(BANUMBER INT,businessassignmentID INT)
    DECLARE @B TABLE(BANUMBER INT,Material VARCHAR(50))
    
    INSERT INTO @A
    SELECT 9077162,5020
    UNION
    SELECT 9077162,5021
    UNION
    SELECT 9077162,5022
    UNION
    SELECT 9077162,5023
    UNION
    SELECT 9077162,5024
    
    
    INSERT INTO @B
    SELECT 9077162,'0000000000000002000885'
    UNION
    SELECT 9077162,'GEN Chemical'
    UNION
    SELECT 9077162,'ST70.31'
    UNION
    SELECT 9077162,'3DT177.91'
    UNION
    SELECT 9077162,'3DT177.91'
    UNION
    SELECT 9077162,'3DT178.91'
    
    
    ;with cte as( select ROW_NUMBER() over (order by  BANUMBER asc) as row_ID,* from @A
    ),cte1 as( select ROW_NUMBER() over (order by  BANUMBER asc) as row_ID,* from @B)
    
    select DISTINCT a.BANUMBER,b.Material,a.businessassignmentID FROM
    cte a
    INNER JOIN cte1 b on a.row_ID = b.row_ID

    Regards,

    Paresh

    Thursday, March 15, 2012 12:47 PM
  • There was duplicate issue in Table A. Now, it was fixed .Now in the Table A there is only distint rows storing based on BANumber.So I can now get the desired output by simple Inner join with Table A and Table B.

    Thanks Paresh for the attempt but now it was not needed.

    thanks

    Saikat

    • Marked as answer by Stephanie Lv Friday, March 16, 2012 4:40 AM
    Thursday, March 15, 2012 12:56 PM