Help reversing DOES NOT EXIST query

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)

Help reversing DOES NOT EXIST query

Author

Topic

eljapo4
Posting Yak Master

100 Posts

Posted - 2012-10-15 : 03:58:34

hi everyone, I have the following query that is excluding any items in the HeatPumps, Cylinders & SolarThermal from my PriceList Table where a ModelNumber exists in the PriceList table. Now I need to reverse the below query and find any model numbers that exist in HeatPumps, Cylinders & SolarThermal tables but that are NOT in the PriceList table. Can someone show me how to do this please?


SELECT [PriceID]
      ,[DRModelNumber]
  FROM [DR_HeatPumpCalc].[dbo].[tbl_lkupPriceList]
  LEFT OUTER JOIN [DR_HeatPumpCalc].[dbo].[tbl_lkupHeatPumps] ON [dbo].[tbl_lkupPriceList].[DRModelNumber] = [dbo].[tbl_lkupHeatPumps].[ModelNumber]
  LEFT OUTER JOIN [DR_HeatPumpCalc].[dbo].[tbl_lkupCylinders] ON [dbo].[tbl_lkupPriceList].[DRModelNumber] = [dbo].[tbl_lkupCylinders].[ModelNumber]
  LEFT OUTER JOIN [DR_HeatPumpCalc].[dbo].[tbl_lkupSolarThermal] ON [dbo].[tbl_lkupPriceList].[DRModelNumber] = [dbo].[tbl_lkupSolarThermal].[ModelNumber]
  WHERE [tbl_lkupHeatPumps].[ModelNumber] IS NULL
  AND	[tbl_lkupCylinders].[ModelNumber] IS NULL
  AND   [tbl_lkupSolarThermal].[ModelNumber] IS NULL
  AND   [tbl_lkupSolarThermal].[HydraulicPack] IS NULL
  
  ORDER BY [DRModelNumber]

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2012-10-15 : 04:17:56

Try something like this:


SELECT DISTINCT H.ModelNumber
  FROM DR_HeatPumpCalc.dbo.tbl_lkupHeatPumps AS H
       INNER JOIN
       DR_HeatPumpCalc.dbo.tbl_lkupCylinders AS C
       ON C.ModelNumber = H.ModelNumber
       INNER JOIN
       DR_HeatPumpCalc.dbo.tbl_lkupSolarThermal AS S
       ON S.ModelNumber = H.ModelNumber
 WHERE NOT EXISTS(SELECT *
                    FROM DR_HeatPumpCalc.dbo.tbl_lkupPriceList AS P
                   WHERE P.DRModelNumber = H.ModelNumber);

For us, there is only the trying. The rest is not our business. ~T.S. Eliot

Muhammad Al Pasha

eljapo4
Posting Yak Master

100 Posts

Posted - 2012-10-15 : 05:05:04

hi thanks for this but the INNER JOINS for Cylinders and SolarThermal ModelNumbers should be on the DRModelNumber in the Pricelist Table. So If I split the query into 3 separate queries:


SELECT DISTINCT H.ModelNumber
  FROM DR_HeatPumpCalc.dbo.tbl_lkupHeatPumps AS H
       --INNER JOIN DR_HeatPumpCalc.dbo.tbl_lkupCylinders AS C ON C.ModelNumber = H.ModelNumber
       --INNER JOIN DR_HeatPumpCalc.dbo.tbl_lkupSolarThermal AS S ON S.ModelNumber = H.ModelNumber
 WHERE NOT EXISTS(SELECT *
                    FROM DR_HeatPumpCalc.dbo.tbl_lkupPriceList AS P
                   WHERE P.DRModelNumber = H.ModelNumber)
                   
SELECT DISTINCT C.ModelNumber
  FROM DR_HeatPumpCalc.dbo.tbl_lkupCylinders AS C
       --INNER JOIN DR_HeatPumpCalc.dbo.tbl_lkupSolarThermal AS S ON S.ModelNumber = H.ModelNumber
 WHERE NOT EXISTS(SELECT *
                    FROM DR_HeatPumpCalc.dbo.tbl_lkupPriceList AS P
                   WHERE P.DRModelNumber = C.ModelNumber)
                   
SELECT DISTINCT S.ModelNumber
  FROM DR_HeatPumpCalc.dbo.tbl_lkupSolarThermal AS S
 WHERE NOT EXISTS(SELECT *
                    FROM DR_HeatPumpCalc.dbo.tbl_lkupPriceList AS P
                   WHERE P.DRModelNumber = S.ModelNumber)

I can see the all the missing ModelNumbers in the PriceList table but how do go about joining each of these queries into 1 statement?

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2012-10-15 : 05:41:36

Use UNION like this:


SELECT DISTINCT H.ModelNumber
  FROM DR_HeatPumpCalc.dbo.tbl_lkupHeatPumps AS H
 WHERE NOT EXISTS(SELECT *
                    FROM DR_HeatPumpCalc.dbo.tbl_lkupPriceList AS P
                   WHERE P.DRModelNumber = H.ModelNumber)

 UNION                   

SELECT DISTINCT C.ModelNumber
  FROM DR_HeatPumpCalc.dbo.tbl_lkupCylinders AS C
 WHERE NOT EXISTS(SELECT *
                    FROM DR_HeatPumpCalc.dbo.tbl_lkupPriceList AS P
                   WHERE P.DRModelNumber = C.ModelNumber)

 UNION                   
                   
SELECT DISTINCT S.ModelNumber
  FROM DR_HeatPumpCalc.dbo.tbl_lkupSolarThermal AS S
 WHERE NOT EXISTS(SELECT *
                    FROM DR_HeatPumpCalc.dbo.tbl_lkupPriceList AS P
                   WHERE P.DRModelNumber = S.ModelNumber);

For us, there is only the trying. The rest is not our business. ~T.S. Eliot

Muhammad Al Pasha

eljapo4
Posting Yak Master

100 Posts

Posted - 2012-10-15 : 05:47:26

excellent thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources