SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Help reversing DOES NOT EXIST query		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

eljapo4
Yak Posting Veteran

United Kingdom
94 Posts
Posted - 10/15/2012 :  03:58:34  Show Profile  Reply with Quote
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

Saudi Arabia
257 Posts
Posted - 10/15/2012 :  04:17:56  Show Profile  Reply with Quote
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
Go to Top of Page

eljapo4
Yak Posting Veteran

United Kingdom
94 Posts
Posted - 10/15/2012 :  05:05:04  Show Profile  Reply with Quote
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?
Edited by - eljapo4 on 10/15/2012 05:21:31
Go to Top of Page

malpashaa
Constraint Violating Yak Guru

Saudi Arabia
257 Posts
Posted - 10/15/2012 :  05:41:36  Show Profile  Reply with Quote
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
Go to Top of Page

eljapo4
Yak Posting Veteran

United Kingdom
94 Posts
Posted - 10/15/2012 :  05:47:26  Show Profile  Reply with Quote
excellent thank you
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000