Two Records into 1 record from 2 tables

1

I wanted to have a record to be same as desired ouput. How should I query it?

Capture
Capture504×543 7.91 KB

2

what have you tried? Seems like a simple join between the 2 tables

3

Hi, and welcome!

It's best to provide sample data that is directly usable rather than in a picture. That way people can start writing SQL right away. For example:


CREATE TABLE #A ( StorerKey int NOT NULL, Sku varchar(30) NOT NULL, LOT1 varchar(10) NULL, LOT2 varchar(10) NULL, LOT3 varchar(10) NULL )
INSERT INTO #A VALUES(1, '123', '12', '0.7', 'Y'), (2, '123', '12', '0.7', 'Y' )

CREATE TABLE #B ( StorerKey int NOT NULL, Sku varchar(30) NOT NULL, LOT4 varchar(10) NULL, LOT5 varchar(10) NULL, LOT6 varchar(10) NULL )
INSERT INTO #B VALUES(1, '123', '2208', 'FR', 'HY'), (2, '123', NULL, NULL, NULL)


SELECT A.StorerKey, A.Sku, A.LOT1, A.LOT2, A.LOT3, B.LOT4, B.LOT5, B.LOT6
FROM #A A
INNER JOIN #B B ON A.StorerKey = B.StorerKey AND A.Sku = B.Sku AND B.LOT4 IS NOT NULL
ORDER BY 1, 2
1 Like