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
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 EXISTS Problem
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

myworldntl
Starting Member

United Kingdom
14 Posts

Posted - 08/05/2010 :  11:06:53  Show Profile  Reply with Quote
Hi Guys, i think i posted this in the wrong area previously so am re-posting in "New to SQL Server Programming", as i very much am...


Hi Guys, i can't seem to get the results i want here using the EXISTS clause, any ideas...?

I know that there are 5 records here that should be returned, basically i need to compare the 2 tables and then only show the full details from the one table on the difference between these 2 tables.

If i use a right join with these same conditions then i get the rows back, but obviously only the field on which i have created the joins, what i need is to be able to get all columns back but based on this retrieved 'compare' datam, any ideas as i could do this Simples in Oracle, but unfortunately im not on Oracle atm and dont know how i'd do this in SQL Server?

Thanks in advance, i'd really appreciate any and all help you could offer.

The below is what i currently have

SELECT
*
FROM
dbo.Rate a
WHERE
EXISTS
(
SELECT

ChargeTypeId, BusinessUnitId, BrochureId, CustomerId, ProductId, ServiceId, ZoneId, BasisId, UnitId, ConditionId, ConditionValue, RateValue, RateValue2, ChargeMinAmount, ChargeMaxAmount, RateBandId, RateBandType, IsLive, QuoteId, Status
FROM
(
SELECT
ChargeTypeId, BusinessUnitId, BrochureId, CustomerId, ProductId, ServiceId, ZoneId, BasisId, UnitId, ConditionId, ConditionValue, RateValue, RateValue2, ChargeMinAmount, ChargeMaxAmount, RateBandId, RateBandType, IsLive, QuoteId, Status
FROM
dbo.Rate
EXCEPT
SELECT
ChargeTypeId, BusinessUnitId, BrochureId, CustomerId, ProductId, ServiceId, ZoneId, BasisId, UnitId, ConditionId, ConditionValue, RateValue, RateValue2, ChargeMinAmount, ChargeMaxAmount, RateBandId, RateBandType, IsLive, QuoteId, Status
FROM
dbo.STG_Rate
) c
WHERE
a.ChargeTypeId = c.ChargeTypeId
AND a.BusinessUnitId = c.BusinessUnitId
AND a.BrochureId = c.BrochureId
AND a.CustomerId = c.CustomerId
AND a.ProductId = c.ProductId
AND a.ServiceId = c.ServiceId
AND a.ZoneId = c.ZoneId
AND a.BasisId = c.BasisId
AND a.UnitId = c.UnitId
AND a.ConditionId = c.ConditionId
AND a.ConditionValue = c.ConditionValue
AND a.RateValue = c.RateValue
AND a.RateValue2 = c.RateValue2
AND a.ChargeMinAmount = c.ChargeMinAmount
AND a.ChargeMaxAmount = c.ChargeMaxAmount
AND a.RateBandId = c.RateBandId
AND a.RateBandType = c.RateBandType
AND a.IsLive = c.IsLive
AND a.QuoteId = c.QuoteId
AND a.Status = c.Status
)

jcelko
Esteemed SQL Purist

USA
547 Posts

Posted - 08/05/2010 :  12:04:58  Show Profile  Visit jcelko's Homepage  Reply with Quote
Shouldn't this work?

SELECT Rates.*
FROM Rates
WHERE key_col
IN (SELECT key_col
FROM (SELECT .. FROM Rates
EXCEPT
SELECT .. STG_Rates));

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Go to Top of Page

myworldntl
Starting Member

United Kingdom
14 Posts

Posted - 08/05/2010 :  12:18:43  Show Profile  Reply with Quote
The problem i have is that the key is a composite key, and SQL server (unlike Oracle) doesnt allow you do use multiple columns in the IN clause, such as:

SELECT * FROM
Table_1
WHERE
(
col_a, col_b, col_c
)
IN
(
SELECT
col_a, col_b, col_c
FROM
Table_2
)

Unless someone knows of a way to be able to do this is SQL Server?
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 08/05/2010 :  12:51:27  Show Profile  Reply with Quote
It's a little difficult to tell what you want exactly. If you can post some DDL. DML and expected output it should be pretty simple to come up with a solution. Here is a link that might help with that:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Just looking over your code, I think you can change the EXISTS clause to a JOIN. But, there is probably a better way. But, again hard to tell since we no nothing of the key structure and output desired.
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 08/05/2010 :  12:56:00  Show Profile  Reply with Quote
Just a guess:
SELECT
	a.*
FROM
	dbo.Rate a
INNER JOIN
	(
		SELECT
			ChargeTypeId, 
			BusinessUnitId, 
			BrochureId, 
			CustomerId, 
			ProductId, 
			ServiceId, 
			ZoneId, 
			BasisId, 
			UnitId, 
			ConditionId, 
			ConditionValue, 
			RateValue, 
			RateValue2, 
			ChargeMinAmount, 
			ChargeMaxAmount, 
			RateBandId, 
			RateBandType, 
			IsLive, 
			QuoteId, 
			Status
		FROM
			dbo.Rate
			
		EXCEPT

		SELECT
			ChargeTypeId, 
			BusinessUnitId, 
			BrochureId, 
			CustomerId, 
			ProductId, 
			ServiceId, 
			ZoneId, 
			BasisId, 
			UnitId, 
			ConditionId, 
			ConditionValue, 
			RateValue, 
			RateValue2, 
			ChargeMinAmount, 
			ChargeMaxAmount, 
			RateBandId, 
			RateBandType, 
			IsLive, 
			QuoteId, 
			Status
		FROM
			dbo.STG_Rate
	) c
WHERE
	a.ChargeTypeId = c.ChargeTypeId
	AND a.BusinessUnitId = c.BusinessUnitId
	AND a.BrochureId = c.BrochureId
	AND a.CustomerId = c.CustomerId
	AND a.ProductId = c.ProductId
	AND a.ServiceId = c.ServiceId
	AND a.ZoneId = c.ZoneId
	AND a.BasisId = c.BasisId
	AND a.UnitId = c.UnitId
	AND a.ConditionId = c.ConditionId
	AND a.ConditionValue = c.ConditionValue
	AND a.RateValue = c.RateValue
	AND a.RateValue2 = c.RateValue2
	AND a.ChargeMinAmount = c.ChargeMinAmount
	AND a.ChargeMaxAmount = c.ChargeMaxAmount
	AND a.RateBandId = c.RateBandId
	AND a.RateBandType = c.RateBandType
	AND a.IsLive = c.IsLive
	AND a.QuoteId = c.QuoteId
	AND a.Status = c.Status
)

Edited by - Lamprey on 08/05/2010 12:56:44
Go to Top of Page

myworldntl
Starting Member

United Kingdom
14 Posts

Posted - 08/05/2010 :  13:01:37  Show Profile  Reply with Quote
Ok cheers, i'm uk based so im heading off soon, but in relation to the join, yes this does work and only returns the 5 rows, but, i only seem to get it to work on a right join, which means i only get data from the joined columns, not the rest of the columns which i need, as they then just come out as NULL's :-/


Basically i have effective dates etc in there which i need to not use in the EXCEPT comparison, but once i've found these records, i then need to use that data to retreive all the columns from the table for those given records...

I thought a Join or an IN statement would do this, but so far no luck :-/

Instead of posting DML etc seeing as it's a customer table/database, i'll simplify the code so it's maybe easier to see what im after, thanks :-)


SELECT
col_a, col_b, col_c, col_d, col_e, col_f, col_g
FROM
dbo.Rate a
WHERE
EXISTS
(
SELECT
col_a, col_b, col_c
FROM
(
SELECT
col_a, col_b, col_c
FROM
dbo.Rate
EXCEPT
SELECT
col_a, col_b, col_c
FROM
dbo.STG_Rate
) c
WHERE
a.col_a = c.col_a
AND a.col_b = c.col_b
AND a.col_c = c.col_c
)

That's not returning anything, if i was to right join this, then i'd get back data for the cols a, b, c, but d - g would come out null
Go to Top of Page

myworldntl
Starting Member

United Kingdom
14 Posts

Posted - 08/05/2010 :  13:02:29  Show Profile  Reply with Quote
ah sorry,i was writing that comment probably as you were posting that last one, thanks i'll take a look at that one and see if it has the desired effect :-)
Go to Top of Page

myworldntl
Starting Member

United Kingdom
14 Posts

Posted - 08/06/2010 :  06:03:35  Show Profile  Reply with Quote
Nope, that returns no rows :-/

I've also tried a full outer join (returns everything but the 5 records it should return are all NULL.

Just tried a RIGHT OUTER JOIN and didnt work, and have tried making the EXCLUDE bit a derived table instead and just using the WHERE clause to join to see if that would work, and nope, nothing seems to work the way you'd want/expect?

Not sure what to do with this now unfortunately?
Go to Top of Page

myworldntl
Starting Member

United Kingdom
14 Posts

Posted - 08/06/2010 :  06:22:47  Show Profile  Reply with Quote
Ok, the below statmemt kind of works...

This brings back all 5 rows that it should, but only the columns specified here...

If i use SELECT *, or specify the other columns that i want bringing back as well, then no data is returned, which defeats the point of doing this, as i need to do the excludes for certain columns to find which ones i need brining back, and then i need to bring back all columns once i've found which ones i need :-(


SELECT
-- a.*
ChargeTypeId,
BusinessUnitId,
BrochureId,
CustomerId,
ProductId,
ServiceId,
ZoneId,
BasisId,
UnitId,
ConditionId,
ConditionValue,
RateValue,
RateValue2,
ChargeMinAmount,
ChargeMaxAmount,
RateBandId,
RateBandType,
IsLive,
QuoteId,
Status
FROM
dbo.Rate a
, (
SELECT
ChargeTypeId,
BusinessUnitId,
BrochureId,
CustomerId,
ProductId,
ServiceId,
ZoneId,
BasisId,
UnitId,
ConditionId,
ConditionValue,
RateValue,
RateValue2,
ChargeMinAmount,
ChargeMaxAmount,
RateBandId,
RateBandType,
IsLive,
QuoteId,
Status
FROM
dbo.Rate

EXCEPT

SELECT
ChargeTypeId,
BusinessUnitId,
BrochureId,
CustomerId,
ProductId,
ServiceId,
ZoneId,
BasisId,
UnitId,
ConditionId,
ConditionValue,
RateValue,
RateValue2,
ChargeMinAmount,
ChargeMaxAmount,
RateBandId,
RateBandType,
IsLive,
QuoteId,
Status
FROM
dbo.STG_Rate
)
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 08/06/2010 :  11:03:41  Show Profile  Reply with Quote
Per my post at 12:51:27, if you can provide some DDL, DML and expected output we can produce a query that'll work. The stuff you are posting isn't making sense to me. Does doing thje EXCEPT product the 5 rows from Rate that you want? If so, this query should work. Otherwise I think we are going to need DDL, DML and expected output:
SELECT 
	A.*
FROM 
	dbo.Rate a
LEFT OUTER JOIN
	dbo.STG_Rate AS c
	ON a.ChargeTypeId = c.ChargeTypeId
	AND a.BusinessUnitId = c.BusinessUnitId
	AND a.BrochureId = c.BrochureId
	AND a.CustomerId = c.CustomerId
	AND a.ProductId = c.ProductId
	AND a.ServiceId = c.ServiceId
	AND a.ZoneId = c.ZoneId
	AND a.BasisId = c.BasisId
	AND a.UnitId = c.UnitId
	AND a.ConditionId = c.ConditionId
	AND a.ConditionValue = c.ConditionValue
	AND a.RateValue = c.RateValue
	AND a.RateValue2 = c.RateValue2
	AND a.ChargeMinAmount = c.ChargeMinAmount
	AND a.ChargeMaxAmount = c.ChargeMaxAmount
	AND a.RateBandId = c.RateBandId
	AND a.RateBandType = c.RateBandType
	AND a.IsLive = c.IsLive
	AND a.QuoteId = c.QuoteId
	AND a.Status = c.Status
WHERE
	c.ChargeTypeId IS NULL -- Any column from STG_Rate will work
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.1 seconds. Powered By: Snitz Forums 2000