Thursday, March 15, 2012 12:30 PM
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.
Thursday, March 15, 2012 12:34 PM
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....
Thursday, March 15, 2012 12:40 PM
INNER JOIN will not work here because BANumber is not unique.So if I use JOIN it will work like CROSS JOIN.
Thursday, March 15, 2012 12:44 PMModerator
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.
Thursday, March 15, 2012 12:47 PM
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
Thursday, March 15, 2012 12:56 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.
- Marked As Answer by Stephanie LvModerator Friday, March 16, 2012 4:40 AM