EXISTS Problem - 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

General SQL Server Forums

New to SQL Server Programming

EXISTS Problem

Author

Topic

myworldntl
Starting Member

14 Posts

Posted - 2010-08-05 : 11:06:53

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

547 Posts

Posted - 2010-08-05 : 12:04:58

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

myworldntl
Starting Member

14 Posts

Posted - 2010-08-05 : 12:18:43

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?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-08-05 : 12:51:27

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.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-08-05 : 12:56:00

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
)

myworldntl
Starting Member

14 Posts

Posted - 2010-08-05 : 13:01:37

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

myworldntl
Starting Member

14 Posts

Posted - 2010-08-05 : 13:02:29

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 :-)

myworldntl
Starting Member

14 Posts

Posted - 2010-08-06 : 06:03:35

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?

myworldntl
Starting Member

14 Posts

Posted - 2010-08-06 : 06:22:47

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
)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-08-06 : 11:03:41

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources