This should work right???? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

This should work right????

Author

Topic

2revup
Posting Yak Master

112 Posts

Posted - 2014-08-01 : 05:09:04

I am trying to find something that does not match in 2 tables, this should be easy, now this works fine.

 
Select * from Shop_Import SI
where SI.productID not in (SELECT productref FROM Shop_Import_Cats)

But this is the SQL I need to use, but it just does not return trhe 1 result that the above does:

Select 'Insert into Shop_Import_Cats values (' + CAST(p.ProductID as varchar) + ',' + CAST(c.CategoryID as varchar) + ',' + CAST(SI.ProductID as varchar) + ',getdate())'
  from NB_Store_Products p
  join NB_Store_ProductLang pl on p.ProductID=pl.ProductID
  join Shop_Import SI on p.ProductRef=SI.ProductID
  join NB_Store_CategoryLang CL on SI.Primary_Category=CL.CategoryName
  join NB_Store_Categories C on CL.CategoryID=C.CategoryID
  join Shop_Import_Cats SIC on SI.ProductID=SIC.productref
  where SI.ProductID not in (SELECT SIC.productref FROM Shop_Import_Cats SIC)

What am I doing wrong?

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2014-08-01 : 08:37:28

not gettting what exactly your looking for ?
Please post some test data and your expected result set.

Vijay is here to learn something from you guys.

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-08-01 : 08:45:30

a little bit more would have helped, but I think you are looking to fin everything that is in Shop_import table that does not have a corresponding record in the Shop_import_cats table.

This should work, but I did not create test tables and try it out
Select 'Insert into Shop_Import_Cats values (' + CAST(p.ProductID as varchar) + ','
+ CAST(c.CategoryID as varchar) + ',' + CAST(SI.ProductID as varchar) + ',getdate())'
from NB_Store_Products p
inner join NB_Store_ProductLang pl on p.ProductID=pl.ProductID
inner join Shop_Import SI on p.ProductRef=SI.ProductID
inner join NB_Store_CategoryLang CL on SI.Primary_Category=CL.CategoryName
inner join NB_Store_Categories C on CL.CategoryID=C.CategoryID
left join Shop_Import_Cats SIC on SI.ProductID=SIC.productref
where SIC.productref is null

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-08-01 : 09:04:26

These two filters are mutually exclusive

join Shop_Import_Cats SIC on SI.ProductID = SIC.productref
  where SI.ProductID not in (SELECT SIC.productref FROM Shop_Import_Cats SIC)

First line says you want the correlation between the two tables over the denoted columns.
Second says the correlation must not exist.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-08-01 : 09:09:42

[code]MERGE dbo.Shop_Import_Cats AS tgt
USING (
SELECT p.ProductID AS ProdID,
c.CategoryID AS CatID,
si.ProductID,
GETDATE()) AS theDate
FROM dbo.NB_Store_Products AS p
INNER JOIN dbo.NB_Store_ProductLang AS pl ON pl.ProductID = p.ProductID
INNER JOIN dbo.Shop_Import AS si ON si.ProductID = p.ProductRef
INNER JOIN dbo.NB_Store_CategoryLang AS cl ON cl.CategoryName = si.Primary_Category
INNER JOIN dbo.NB_Store_Categories AS c ON c.CategoryID = cl.CategoryID
) AS src ON src.ProductID = tgt.ProductRef
WHEN NOT MATCHED BY TARGET
THEN INSERT (
ColA,
ColB,
ColC,
ColD
)
VALUES (
src.ProdID.
src.CatID.
src.ProductID,
src.theDate
);[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-08-01 : 09:12:56

[code]SELECT DISTINCT 'INSERT DBO.SHOP_IMPORT_CATS VALUES (' + CAST(p.ProductID AS VARCHAR(12))
+ ', ' + CAST(c.CategoryID AS VARCHAR(12))
+ ', ' + CAST(si.ProductID AS VARCHAR(12))
+ ', GETDATE())'
FROM dbo.NB_Store_Products AS p
INNER JOIN dbo.NB_Store_ProductLang AS pl ON pl.ProductID = p.ProductID
INNER JOIN dbo.Shop_Import AS si ON si.ProductID = p.ProductRef
INNER JOIN dbo.NB_Store_CategoryLang AS cl ON cl.CategoryName = si.Primary_Category
INNER JOIN dbo.NB_Store_Categories AS c ON c.CategoryID = cl.CategoryID
LEFT JOIN dbo.Shop_Import_Cats AS sic ON sic.ProductRef = si.ProductID
WHERE sic.ProductRef IS NULL;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

2revup
Posting Yak Master

112 Posts

Posted - 2014-08-01 : 18:57:31

Thanks guys this is what worked!

SELECT 'Insert into Shop_Import_Cats values ('+ CAST(coalesce(p.ProductID,' ') AS VARCHAR) + ','+ CAST(coalesce(c.CategoryID,' ') AS VARCHAR) + ','+ CAST(coalesce(SI.ProductID,' ') AS VARCHAR) + ',getdate())'
from (
select ProductID,Primary_Category /* insert any additional required columnx here */
from Shop_Import SI
where SI.ProductID not in (SELECT SIC.productref FROM Shop_Import_Cats SIC)
) SI
left join NB_Store_Products P on SI.ProductID = p.ProductRef
left join NB_Store_ProductLang pl on p.ProductID = pl.ProductID
left join NB_Store_CategoryLang CL on SI.Primary_Category = CL.CategoryName
left join NB_Store_Categories C on CL.CategoryID = C.CategoryID
left join Shop_Import_Cats SIC on SI.ProductID = SIC.productref

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources