Query help - 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)

Query help

Author

Topic

gorrie
Starting Member

3 Posts

Posted - 2008-12-21 : 20:10:52

Hi
I have a table like that shown below:

Sales_id stockid ACS group_code
128702 13609 8.19 V
128702 25670 8.19 V
128702 26452 8.19 NULL
128703 27203 8.18 NULL
128704 2425 15.79 Z
128704 17550 15.79 NULL
128704 23930 15.79 V
128704 20399 15.79 NULL
128704 20501 15.79 NULL
128704 26091 15.79 NULL
128705 1514 6.36 NULL
128705 30139 6.36 NULL
128706 29649 3.64 Z
128706 27861 3.64 V

How do I:
1. select all the complete sales_id's that have sales only with NULL group_codes (RESULT: only bring up sales_id's 128703, 128705)

2. select all the complete sales_id's that have sales only with V or Z group_codes (RESULT: only bring up sales_id's 128706)

Thanks

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-21 : 21:43:53

How do I:
1. select all the complete sales_id's that have sales only with NULL group_codes (RESULT: only bring up sales_id's 128703, 128705)

Select sales_Id from table t
Where not exists(Select sales_Id from table
Where t.sales_id=sales_Id and group_code
In('V','Z'))

2. select all the complete sales_id's that have sales only with V or Z group_codes (RESULT: only bring up sales_id's 128706)

Select sales_id from table
Where group_code in ('V','Z')

gorrie
Starting Member

3 Posts

Posted - 2008-12-21 : 22:33:45

Thanks sodeep...

Sorry, but need to explain myself alittle clearer..

1. Find sales_id's that have ONLY UNIQUE "NULL" as the group_codes.
So this would only find sales_id's 128703, 128705

2. Find the sales_id's that ONLY have 'V' AND/OR 'Z' group codes.
So this would find 128706

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2008-12-21 : 23:24:02

Try This for your 1st condition

SELECT DISTINCT sales_id FROM table WHERE sales_id NOT IN (SELECT DISTINCT sales_id FROM s WHERE group_code IN('v','z'))

Jai Krishna

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-21 : 23:37:16

try this for 2nd condition

select s.sales_id,s.group_code from urtable s inner join urtable s1 on s1.sales_id = s.sales_id and s1.group_code in ('z')
where s.group_code in ('v','z')

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2008-12-21 : 23:45:38

For 2nd Query,,

select distinct s.sales_id,s.group_code from s inner join s s1 on s1.sales_id = s.sales_id and s1.group_code in ('v','z')
where s.sales_id not in ( select sales_id from s where group_code is null)

I Struggle For Excellence

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2008-12-21 : 23:45:45

2. Find the sales_id's that ONLY have 'V' AND/OR 'Z' group codes.
So this would find 128706

SELECT DISTINCT s1.sales_id from
(SELECT DISTINCT sales_id FROM s WHERE sales_id NOT IN (SELECT sales_id FROM s WHERE group_code IS NULL)) h
CROSS APPLY(SELECT sales_id FROM s WHERE sales_id = h.sales_id AND group_code IN ('v','z')) s1

Jai Krishna

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2008-12-21 : 23:51:13

For 1st Query,

select distinct s.sales_id,s.group_code from table inner join table s1 on s1.sales_id = s.sales_id
where s.sales_id not in (select sales_id from table where group_code in ('v','z'))

I Struggle For Excellence

gorrie
Starting Member

3 Posts

Posted - 2008-12-22 : 00:37:28

Thanks to all!

I have used Jai's solutions successfully.

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2008-12-22 : 02:21:30

Welcome

Jai Krishna

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-22 : 03:09:20

Keep it simple!
Add this sample data and all previous suggestions will fail

SELECT	128702, 13609,  8.19, 'y' UNION ALL
SELECT	128704,  2425, 15.79, 'v' UNION ALL
SELECT	128707,  2425, 15.79, 'y' UNION ALL
SELECT	128706,  2425, 15.79, 'y' UNION ALL

Try this suggestion

DECLARE	@Sample TABLE
	(
		Sales_id INT,
		stockid INT,
		ACS MONEY,
		group_code CHAR(1)
	)

INSERT	@Sample
SELECT	128702, 13609,  8.19, 'V' UNION ALL
SELECT	128702, 25670,  8.19, 'V' UNION ALL
SELECT	128702, 26452,  8.19, NULL UNION ALL
SELECT	128703, 27203,  8.18, NULL UNION ALL
SELECT	128704,  2425, 15.79, 'Z' UNION ALL
SELECT	128704, 17550, 15.79, NULL UNION ALL
SELECT	128704, 23930, 15.79, 'V' UNION ALL
SELECT	128704, 20399, 15.79, NULL UNION ALL
SELECT	128704, 20501, 15.79, NULL UNION ALL
SELECT	128704, 26091, 15.79, NULL UNION ALL
SELECT	128705,  1514,  6.36, NULL UNION ALL
SELECT	128705, 30139,  6.36, NULL UNION ALL
SELECT	128706, 29649,  3.64, 'Z' UNION ALL
SELECT	128706, 27861,  3.64, 'V'

-- Peso (How do I do 1)
SELECT		Sales_ID
FROM		@Sample
GROUP BY	Sales_ID
HAVING		MAX(group_code) IS NULL

-- Peso (How do I do 2)
SELECT		Sales_ID
FROM		@Sample
GROUP BY	Sales_ID
HAVING		MIN(CASE WHEN group_code IN ('V', 'Z') THEN 1 ELSE 0 END) = 1

E 12°55'05.63"
N 56°04'39.26"

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2008-12-22 : 03:29:13

Try This

SELECT DISTINCT sales_id FROM s WHERE sales_id NOT IN (SELECT DISTINCT sales_id FROM s WHERE group_code IN(SELECT DISTINCT group_code FROM s WHERE group_code IS NOT NULL))

Jai Krishna

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-22 : 03:39:51

3 table scans instead of just 1?

E 12°55'05.63"
N 56°04'39.26"

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2008-12-22 : 03:44:02

Its Ok,

Trying differently from urs

Jai Krishna

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-22 : 03:50:30

It's ok.
I just posted my suggestion to give variation and something that work with performance.

E 12°55'05.63"
N 56°04'39.26"

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2008-12-22 : 03:53:48

Ur Suggestions will be followed

Thank U Peso

Jai Krishna

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2008-12-22 : 04:10:16

For 2 Condition

SELECT DISTINCT s1.sales_id from
(SELECT DISTINCT sales_id FROM s WHERE sales_id NOT IN (SELECT DISTINCT sales_id FROM s WHERE group_code IS NULL OR group_code IN(SELECT DISTINCT group_code FROM s WHERE group_code <> 'v' AND group_code <> 'z'))) h
CROSS APPLY(SELECT sales_id FROM s WHERE sales_id = h.sales_id AND group_code IN ('v','z')) s1

Jai Krishna

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2008-12-22 : 04:29:21

select 128709,22,22,'v'

for this input

SELECT Sales_ID
FROM @Sample
GROUP BY Sales_ID
HAVING MIN(CASE WHEN group_code IN ('V', 'Z') THEN 1 ELSE 0 END) = 1

The Query is not giving correct output

Jai Krishna

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-22 : 05:44:02

Why do you think it is wrong?
I get 128706 and 128709 with your new sample data.

OP requested (for question 2) that sales_id to be returned for group only having v OR z values.

quote:
Originally posted by gorrie

How do I:
1. select all the complete sales_id's that have sales only with NULL group_codes (RESULT: only bring up sales_id's 128703, 128705)
2. select all the complete sales_id's that have sales only with V or Z group_codes (RESULT: only bring up sales_id's 128706)

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources