Display 1 record instead of 2 record

1

Hi all!,

These are my tables:
CREATE TABLE #A ( StorerKey int NOT NULL, Sku varchar(30) NOT NULL, LOT1 varchar(10) NULL,LOT2 varchar(10) NULL,LOT3 varchar(10) NULL,LOT4 varchar(10) NULL,LOT15varchar(10) NULL,LOT6 varchar(10) NULL ) INSERT INTO #A VALUES (1,123,2,1,12,1.5,1.511,CP)

CREATE TABLE #B ( StorerKey int NOT NULL, Sku varchar(30) NOT NULL, LOT15 varchar(10) NULL,LOT16 varchar(10) NULL,LOT17 varchar(10) NULL,LOT18 varchar(10) NULL,LOT19varchar(10) NULL,LOT20 varchar(10) NULL ,LOT21 varchar(10) NULL,LOT22 varchar(10) NULL,LOT23 varchar(10) NULL,LOT24 varchar(10) NULL,LOT25 varchar(10) NULL,LOT26 varchar(10) NULL) INSERT INTO #B VALUES (1,123,1,1,1,1,1,1,1,1,1)

I need to retrieve different fields from two tables.

If I use query 1 to generate from table A, I get 1 record

SELECT #A.STOREKEY,#A.SKU,#A.LOT1,#A.LOT2,#A.LOT3,#A.LOT4,#A.LOT5,#A.LOT6
FROM #A WHERE #A.lot7='001' AND #A.sku='123' and #A.STORERKEY='1'

If I use query 2 to generate from table B, I get 1 record also

SELECT #B.STORERKEY,#B.SKU,#B.LOT15,#B.LOT16,#B.LOT17,#B.LOT18,#B.LOT19,#B.LOT20,#B.LOT21,#B.LOT22,#B.LOT23,#B.LOT24
FROM #B WHERE #B.sku='123' and #B.STORERKEY='1'

But when I used query 3, I get 2 record instead (Like Results)

SELECT #A.STOREKEY,#A.SKU,#A.LOT1,#A.LOT2,#A.LOT3,#A.LOT4,#A.LOT5,#A.LOT6,#B.STORERKEY,#B.SKU,#B.LOT15,#B.LOT16,#B.LOT17,#B.LOT18,#B.LOT19,#B.LOT20,#B.LOT21,#B.LOT22,#B.LOT23,#B.LOT24
FROM #A TA
JOIN #B TB ON TA.SKU=TB.SKU WHERE #A.lot7='001' AND #A.sku='123' and #A.STORERKEY='1'

I just need the results from 2 tables to combine into 1 record (Like the desired record)

image
image831×347 7.91 KB

What's wrong with my query 3?=(

Thank you in advance!

2

You should work a lot cleaner, there are many typo's in your example. It sounds strange in this world of machine learing and AI but the sql-compiler doesn't correct them.

You miss lot7 in your #A table, is it storerkey or storekey? I corrected the typo's and created this example for you. You can also use SELECT DISTINT instead of SELECT but your query should return 1 row if both tables have 1 row for sku and storerkey. You should check that first. I think you should join the table with sku and storekey so

FROM #A INNER JOIN #B on #A.SKU=#B.SKU AND #A.StoreKey=#B.StoreKey

This is an example:

CREATE TABLE #A ( StoreKey int NOT NULL, Sku varchar(30) NOT NULL, LOT1 varchar(10) NULL,LOT2 varchar(10) NULL,LOT3 varchar(10) NULL,LOT4 varchar(10) NULL,LOT5 varchar(10) NULL,LOT6 varchar(10) NULL )
INSERT INTO #A VALUES (1,123,2,1,12,1.5,1.511,'CP')

CREATE TABLE #B (
StorerKey int NOT NULL, Sku varchar(30) NOT NULL, LOT15 varchar(10) NULL,
LOT16 varchar(10) NULL,LOT17 varchar(10) NULL,LOT18 varchar(10) NULL,
LOT19 varchar(10) NULL,LOT20 varchar(10) NULL ,LOT21 varchar(10) NULL,
LOT22 varchar(10) NULL,LOT23 varchar(10) NULL,LOT24 varchar(10) NULL,
LOT25 varchar(10) NULL,LOT26 varchar(10) NULL)
INSERT INTO #B VALUES (1,123,1,1,1,1,1,1,1,1,1,1,1,1);

SELECT #A.STOREKEY,#A.SKU,#A.LOT1,#A.LOT2,#A.LOT3,#A.LOT4,#A.LOT5,#A.LOT6
FROM #A WHERE #A.lot1=2 AND #A.sku='123' and #A.STOREKEY=1;

SELECT #A.STOREKEY,#A.SKU,#A.LOT1,#A.LOT2,#A.LOT3,#A.LOT4,#A.LOT5,#A.LOT6,#B.STORERKEY,#B.SKU,#B.LOT15,#B.LOT16,#B.LOT17,#B.LOT18,#B.LOT19,#B.LOT20,#B.LOT21,#B.LOT22,#B.LOT23,#B.LOT24
FROM #A
JOIN #B ON #A.SKU=#B.SKU WHERE #A.lot1=2 AND #A.sku='123' and #A.STOREKEY=1;

3

Hi,

Thank you for the reply.
Sorry for the confusion.
But what if my next SKU is 456 and lot1 is not 2?
I need to generate the list of SKU with lot7='001' AND storerkey='1'.

Thank you
Best Regards

4

Use SELECT DISTINCT to get only unique rows or provide an working example with the results you get so I can help you to get the disired Results.

5

I am still getting two records even I used SELECT DISTINCT.
Below is my query:

CREATE TABLE SKU(
[StorerKey] nvarchar NOT NULL,
[Sku] nvarchar NOT NULL,
[SKUGROUP] nvarchar NOT NULL,
[BUSR1] nvarchar NULL,
[BUSR4] nvarchar NULL,
[BUSR5] nvarchar NULL,
[NetWgt] [float] NOT NULL,
[BUSR7] nvarchar NULL)
[Itemclass] nvarchar NULL,
INSERT INTO SKU VALUES
(MHA,1051378,HY,40,0.666,0.7,001),
(MHA,1055257,HY ,40,0.666,0.7,001),
(MHA,1062152,CP,12,1.491,1.5,001),
(MHA,1062928,BV,40,1.659,1.75,001),
(MHA,1063229,RU,12.5,0.745,0.75,001),
(MHA,1063987,HY,40,0.666,0.7,001);

CREATE TABLE [SkuInfo](
[Storerkey] nvarchar NOT NULL,
[Sku] nvarchar NOT NULL,
[ExtendedField02] nvarchar NULL,
[ExtendedField03] nvarchar NULL)
INSERT INTO SKU VALUES
(MHA,1051378,FR,HENNESSY),
(MHA,1055257,FR,HENNESSY),
(MHA,1062152,,),
(MHA,1062928,PL,BELVEDERE),
(MHA,1063229,FR,DOM RUINART),
(MHA,1063987,,);

Query 1 and 2 give me 1 record each but when I used query 3, it give me 2 records.

SELECT SKU,BUSR4,BUSR1,BUSR5,BUSR7,NetWgt,SKUGroup FROM SKU WHERE Itemclass='001' AND STORERKEY='MHA'

SELECT SKU, ExtendedField02,ExtendedField03 FROM SKUINFO WHERE STORERKEY='MHA' and SKU in ( SELECT SKU FROM SKU WHERE Itemclass='001' AND STORERKEY='MHA')

SELECT SKU.SKU,SKU.BUSR4,SKU.BUSR1,SKU.BUSR5,SKU.BUSR7,SKU.NetWgt,SKU.SKUGroup,SKUINFO.ExtendedField02,SKUINFO.ExtendedField03 FROM SKU SKU WHERE SKU.Itemclass='001' AND SKU.STORERKEY='MHA'
JOIN
SKUINFO SKUINFO ON SKU.SKU = SKUINFO.SKU WHERE SKU.itemclass='001' and SKU.STORERKEY='MHA'

Thank you

6

The example you provide is not correct. I cannot use it.

7

Sorry for the troubles and confusion. I am new to SQL. I updated the CREATE table

CREATE TABLE SKU(
[StorerKey] nvarchar NOT NULL,
[Sku] nvarchar NOT NULL,
[SKUGROUP] nvarchar NOT NULL,
[BUSR1] nvarchar NULL,
[BUSR4] nvarchar NULL,
[BUSR5] nvarchar NULL,
[NetWgt] [float] NOT NULL,
[BUSR7] nvarchar NULL)
[Itemclass] nvarchar NULL,
INSERT INTO SKU VALUES
(MHA,1051378,HY,NULL,NULL,40,0.666,0.7,001),
(MHA,1055257,HY,NULL,NULL,40,0.666,0.7,001),
(MHA,1062152,CP,NULL,NULL,12,1.491,1.5,001),
(MHA,1062928,BV,NULL,NULL,40,1.659,1.75,001),
(MHA,1063229,RU,NULL,NULL,12.5,0.745,0.75,001),
(MHA,1063987,HY,NULL,NULL,40,0.666,0.7,001);

CREATE TABLE [SkuInfo](
[Storerkey] nvarchar NOT NULL,
[Sku] nvarchar NOT NULL,
[ExtendedField02] nvarchar NULL,
[ExtendedField03] nvarchar NULL)
INSERT INTO SKU VALUES
(MHA,1051378,FR,HENNESSY),
(MHA,1055257,FR,HENNESSY),
(MHA,1062152,NULL,NULL),
(MHA,1062928,PL,BELVEDERE),
(MHA,1063229,FR,DOM RUINART),
(MHA,1063987,NULL,NULL);

SELECT SKU,BUSR4,BUSR1,BUSR5,BUSR7,NetWgt,SKUGroup FROM SKU WHERE Itemclass='001' AND STORERKEY='MHA'

SELECT SKU, ExtendedField02,ExtendedField03 FROM SKUINFO WHERE STORERKEY='MHA' and SKU in ( SELECT SKU FROM SKU WHERE Itemclass='001' AND STORERKEY='MHA')

SELECT SKU.SKU,SKU.BUSR4,SKU.BUSR1,SKU.BUSR5,SKU.BUSR7,SKU.NetWgt,SKU.SKUGroup,SKUINFO.ExtendedField02,SKUINFO.ExtendedField03 FROM SKU SKU WHERE SKU.Itemclass='001' AND SKU.STORERKEY='MHA'
JOIN
SKUINFO SKUINFO ON SKU.SKU = SKUINFO.SKU WHERE SKU.itemclass='001' and SKU.STORERKEY='MHA'

8

I worked this out:

CREATE TABLE SKU(
[StorerKey] nvarchar(25) NOT NULL,
[Sku] nvarchar(25) NOT NULL,
[SKUGROUP] nvarchar(25) NOT NULL,
[BUSR1] nvarchar(25) NULL,
[BUSR4] nvarchar(25) NULL,
[BUSR5] nvarchar(25) NULL,
[NetWgt] [float] NOT NULL,
[BUSR7] nvarchar(25) NULL,
[Itemclass] nvarchar(25) NULL);

SELECT * FROM SKU;

INSERT INTO SKU VALUES
('MHA','1051378','HY',NULL,NULL,40,0.666,0.7,'001'),
('MHA','1055257','HY',NULL,NULL,40,0.666,0.7,'001'),
('MHA','1062152','CP',NULL,NULL,12,1.491,1.5,'001'),
('MHA','1062928','BV',NULL,NULL,40,1.659,1.75,'001'),
('MHA','1063229','RU',NULL,NULL,12.5,0.745,0.75,'001'),
('MHA','1063987','HY',NULL,NULL,40,0.666,0.7,'001');

CREATE TABLE [SkuInfo](
[Storerkey] nvarchar(25) NOT NULL,
[Sku] nvarchar(25) NOT NULL,
[ExtendedField02] nvarchar(25) NULL,
[ExtendedField03] nvarchar(25) NULL);

INSERT INTO SKUInfo VALUES
('MHA','1051378','FR','HENNESSY'),
('MHA','1055257','FR','HENNESSY'),
('MHA','1062152',NULL,NULL),
('MHA','1062928','PL','BELVEDERE'),
('MHA','1063229','FR','DOM RUINART'),
('MHA','1063987',NULL,NULL);

SELECT * FROM SKUInfo;

SELECT SKU,BUSR4,BUSR1,BUSR5,BUSR7,NetWgt,SKUGroup
FROM SKU
WHERE Itemclass='001' AND STORERKEY='MHA';

SELECT SKU, ExtendedField02,ExtendedField03
FROM SKUINFO
WHERE STORERKEY='MHA' AND SKU IN ( SELECT SKU
FROM SKU
WHERE Itemclass='001' AND STORERKEY='MHA'
);

SELECT SKU.SKU,SKU.BUSR4,SKU.BUSR1,SKU.BUSR5,SKU.BUSR7,SKU.NetWgt,SKU.SKUGroup, SkuInfo.ExtendedField02, SkuInfo.ExtendedField03
FROM SKU
INNER JOIN SKUInfo
ON SKU.SKU = SKUINFO.SKU
WHERE SKU.itemclass='001' and SKU.STORERKEY='MHA'

9

how about trying DISTINCT

why 2 records are coming is a different story

SELECT 
       DISTINCT 
         #a.storekey,
         #a.sku,
         #a.lot1,
         #a.lot2,
         #a.lot3,
         #a.lot4,
         #a.lot5,
         #a.lot6,
         #b.storerkey,
         #b.sku,
         #b.lot15,
         #b.lot16,
         #b.lot17,
         #b.lot18,
         #b.lot19,
         #b.lot20,
         #b.lot21,
         #b.lot22,
         #b.lot23,
         #b.lot24
FROM   #a TA
       JOIN #b TB
         ON TA.sku = TB.sku
WHERE  #a.lot7 = '001'
       AND #a.sku = '123'
       AND #a.storerkey = '1'