subquery related - 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

SQL Server 2005 Forums

Transact-SQL (2005)

subquery related

Author

Topic

bsn
Starting Member

5 Posts

Posted - 2007-09-10 : 14:57:05

Hi all,
I am trying to see if there is a better way to execute this.

select parnum,clid from {....}
where avgr = 'y' and altr = 'n' and
clid not in
(select clid from {....} where avgr = 'y' and altr = 'y' and
clid in (select clid from {....} where avgr = 'y' and altr = 'n'))

{....} represents the joins.

There are records with the same clid which have avgr = y and altr = y and also avgr = y and altr = n. they show up as multiple records in the database. I need to exclude such records from the initial selection criteria of avgr = y and altr = n.

Is there any other way of doing this... better way to do this..
Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-10 : 15:14:49

Too little information about data.
Give this mind-reading attempt a shot

select		parnum,
		clid
from		{}
group by	parnum,
		clid
having		min(avgr) = 'y'
		and max(altr) = 'n'

E 12°55'05.25"
N 56°04'39.16"

bsn
Starting Member

5 Posts

Posted - 2007-09-10 : 15:25:24

Here's an example of the data..

parnum clid avgr altr amount
20001 510 Y N 204.00
20002 511 Y Y 124.35
20002 511 Y N 784.14
20003 714 Y N 10.12
20004 874 Y N 457.10
20004 874 Y Y 90.12
20008 004 Y N 14.15

If I made a selection where avgr = y and altr = n then I would get 5 records.
but I want to exclude the ones which have both the YY and YN combinations like the ones with clid 511 and 874.
My end result should be only 3 records in this example.

hope it is clear now.

thanks

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-09-10 : 15:48:12

select * from tablename
where clid in (
select clid
from tablename
where avrg='Y' and altr='N'
except
select clid
from tablename
where avrgr='Y' and (altry='Y' or altr='N')
group by clid
having count(*)>1
)

haven't tested... so not sure if it comes out right

--------------------
keeping it simple...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-10 : 15:54:13

And my query above doesn't do it for you?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-10 : 15:55:01

[code]DECLARE @Sample TABLE (parnum INT, clid CHAR(3), avgr CHAR(1), altr CHAR(1), amount MONEY)

INSERT @Sample
SELECT 20001, '510', 'Y', 'N', 204.00 UNION ALL
SELECT 20002, '511', 'Y', 'Y', 124.35 UNION ALL
SELECT 20002, '511', 'Y', 'N', 784.14 UNION ALL
SELECT 20003, '714', 'Y', 'N', 10.12 UNION ALL
SELECT 20004, '874', 'Y', 'N', 457.10 UNION ALL
SELECT 20004, '874', 'Y', 'Y', 90.12 UNION ALL
SELECT 20008, '004', 'Y', 'N', 14.15

select parnum,
clid
from @sample
group by parnum,
clid
having min(avgr) = 'y'
and max(altr) = 'n'[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-10 : 15:55:37

[code]parnum clid
20008 004
20001 510
20003 714[/code]

E 12°55'05.25"
N 56°04'39.16"

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-09-10 : 16:08:20

I misread parnum as unique, watching The Omen is doing something with my vision

--------------------
keeping it simple...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-10 : 16:10:50

The original or the remake?

E 12°55'05.25"
N 56°04'39.16"

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-09-10 : 16:25:37

remake... is the original better or same?

--------------------
keeping it simple...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-10 : 16:43:22

A matter of opinion. Original was breath-taking then, but today it feels slow and "been there done that".

E 12°55'05.25"
N 56°04'39.16"

bsn
Starting Member

5 Posts

Posted - 2007-09-10 : 17:03:02

The query that Peso gave works okay with the small example here but when I try the same thing with my big query with all the joins, it gives me different results.
The initial query that i posted gives me the appropriate results.. I wanted to see if there was a different way to do it.
it essentially implies A-(B intersection C)

select parnum,clid from {....}
where avgr = 'y' and altr = 'n' ( this here is the entire selection where avgr =y and altr = n)
and clid not in
(select clid from {....} where avgr = 'y' and altr = 'y' and
clid in (select clid from {....} where avgr = 'y' and altr = 'n')) (this is the exclusion of the ones which have both the YY and YN combination)

Please let me know if you have any other ideas.

thanks

quote:
Originally posted by Peso

parnum clid
20008 004
20001 510
20003 714

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-10 : 17:29:40

Please post sample that my query do NOT work for, and I will give it another shot.
Make sure you post all possible combinations this time, and also your expected output based on the sample date you provide.

select		parnum,
		clid
from		@sample
group by	parnum,
		clid
having		min(avgr) = 'y'
		and min(altr) = max(altr)

E 12°55'05.25"
N 56°04'39.16"

bsn
Starting Member

5 Posts

Posted - 2007-09-10 : 17:33:10

Could you please explain how your query gives me the functionality of A - (B intersection C)... May be I am missing your idea..

quote:
Originally posted by Peso

Please post sample that my query do NOT work for, and I will give it another shot.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-10 : 17:37:01

So CLID having both YN and YY are not fetched.
But CLID having only YN or only YY are fetched?

What about NY and NN?

E 12°55'05.25"
N 56°04'39.16"

bsn
Starting Member

5 Posts

Posted - 2007-09-10 : 17:54:40

No I need to fetch only the ones with Avgr = Y and Altr = N

But there are some with both YN and YY which need to be excluded. I dont have to worry about NY and NN...

Thanks

quote:
Originally posted by Peso

So CLID having both YN and YY are not fetched.
But CLID having only YN or only YY are fetched?

What about NY and NN?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-10 : 18:19:27

Try this

DECLARE	@Sample TABLE (parnum INT, clid CHAR(3), avgr CHAR(1), altr CHAR(1), amount MONEY)

INSERT	@Sample
SELECT	20001, '510', 'Y', 'N', 204.00 UNION ALL
SELECT	20001, '510', 'Y', 'N', 104.00 UNION ALL
SELECT	20002, '511', 'Y', 'Y', 124.35 UNION ALL
SELECT	20002, '511', 'Y', 'N', 784.14 UNION ALL
SELECT	20011, '511', 'Y', 'N', 784.14 UNION ALL
SELECT	20003, '714', 'Y', 'N',  10.12 UNION ALL
SELECT	20004, '874', 'Y', 'N', 457.10 UNION ALL
SELECT	20004, '874', 'Y', 'Y',  90.12 UNION ALL
SELECT	20008, '004', 'Y', 'N',  14.15

SELECT		parnum,
		clid,
		avgr,
		altr,
		amount
FROM		(
			SELECT		parnum,
					clid,
					avgr,
					altr,
					amount,
					DENSE_RANK() OVER (PARTITION BY clid ORDER BY avgr, altr DESC) AS RecID
			FROM		@Sample
		) AS z
WHERE		RecID = 1
		AND avgr = 'y'
		AND altr = 'n'

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-10 : 18:35:17

[code]SELECT s.parnum,
s.clid,
s.avgr,
s.altr,
s.amount
FROM @Sample AS s
INNER JOIN (
SELECT clid
FROM @Sample
GROUP BY clid
HAVING MIN(case when avgr = 'y' and altr = 'n' then 1 else 0 end) = 1
) AS c ON c.clid = s.clid[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources