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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

eljapo4
Posting Yak Master

United Kingdom
100 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
264 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
Posting Yak Master

United Kingdom
100 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
264 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
Posting Yak Master

United Kingdom
100 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  
 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