Select according to specifics - 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 2000 Forums

Transact-SQL (2000)

Select according to specifics

Author

Topic

rintus
Starting Member

26 Posts

Posted - 2008-10-03 : 19:21:06

Hi all,

I want to select certain roll nos, names of students who take specific subjects.

For ex - N - Name, R - Roll, S - Subject and primary key - row_id

N R S
A 1 Sc, Co, Ar
B 5 Sc, Co
C 3 Sc, Ar
D 9 Sc, Co
E 7 Sc, Co, Ar, Ma

So I need the output as :-

N R S
B 5 Sc, Co
D 9 Sc, Co

And all the tables are 3 three different tables and I have no idea about all the subjects. I just know that I have to find those students who has Sc, Co

Please suggest.

Thanks

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-10-03 : 19:50:29

You need to explain clearly with table structure and what you expect?

rintus
Starting Member

26 Posts

Posted - 2008-10-03 : 21:09:17

I expect to get all the roll nos who take subjects only (Sc and Co) or only Sc or only Co and Nothing else. Hope this helps.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-04 : 03:19:41

quote:
Originally posted by rintus

I expect to get all the roll nos who take subjects only (Sc and Co) or only Sc or only Co and Nothing else. Hope this helps.

SELECT N,R,S
FROM YourTable
WHERE  ',Sc,Co,' LIKE '%,'+ S + ',%'
AND LEN(S)-LEN(REPLACE(S,',',''))<=1

rintus
Starting Member

26 Posts

Posted - 2008-10-06 : 14:00:14

This does not work

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-06 : 14:48:48

SELECT N, R, S
FROM YourTable
WHERE REPLACE(REPLACE(REPLACE(S, 'Co', ''), 'Sc', ''), ',', '') = ''

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

rintus
Starting Member

26 Posts

Posted - 2008-10-06 : 15:04:31

Doesn't work again

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-06 : 15:11:49

You must be kidding!

DECLARE	@Sample TABLE
	(
		N CHAR(1),
		R TINYINT,
		S VARCHAR(200)
	)

INSERT	@Sample
SELECT	'A', 1, 'Sc, Co, Ar' UNION ALL
SELECT	'B', 5, 'Sc, Co' UNION ALL
SELECT	'C', 3, 'Sc, Ar' UNION ALL
SELECT	'D', 9, 'Sc, Co' UNION ALL
SELECT	'E', 7, 'Sc, Co, Ar, Ma'

SELECT	*
FROM	@Sample

SELECT	N,
	R,
	S
FROM	@Sample
WHERE	REPLACE(REPLACE(REPLACE(S, 'Co', ''), 'Sc', ''), ',', '') = ''

N	R	S
------	------	------
B	5	Sc, Co
D	9	Sc, Co

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

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-06 : 15:16:59

This gotta work


select N,R,S
from YourTable
where S in ('Sc','Co','Sc, Co','Co, Sc')

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-06 : 15:17:06

SELECT N, R, S
FROM YourTable
WHERE ltrim(REPLACE(REPLACE(REPLACE(S, 'Co', ''), 'Sc', ''), ',', '')) = ''

Planning replaces chance by mistake

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-06 : 15:22:00

quote:
Originally posted by hanbingl

This gotta work

Oh, We haven't even got into COLLATION yet!

Ot the possibility that commas are not always followed by a space.

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

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-06 : 15:29:34

Peso, you are correct. The query you gave does not work if S is blank.

SELECT N, R, S
FROM @Sample
WHERE ltrim(REPLACE(REPLACE(REPLACE(S, 'Co', ''), 'Sc', ''), ',', '')) = ''
and ltrim(S) <> ''

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-06 : 15:30:54

Good catch!

and ltrim(S) > ''

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-06 : 15:41:31

and what is about NULL?

Planning replaces chance by mistake

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-06 : 15:46:55

Yes, what about NULLs?
NULLs are automatically discarded when check for values.

DECLARE	@Sample TABLE
	(
		N CHAR(1),
		R TINYINT,
		S VARCHAR(200)
	)

INSERT	@Sample
SELECT	'A', 1, 'Sc, Co, Ar' UNION ALL
SELECT	'B', 5, 'Sc, Co' UNION ALL
SELECT	'C', 3, 'Sc, Ar' UNION ALL
SELECT	'D', 9, 'Sc, Co' UNION ALL
SELECT	'G', 2, '' UNION ALL
SELECT	'I', 2, NULL UNION ALL
SELECT	'E', 7, 'Sc, Co, Ar, Ma'

SELECT	*
FROM	@Sample

SELECT	N,
	R,
	S
FROM	@Sample
WHERE	REPLACE(REPLACE(REPLACE(S, 'Co', ''), 'Sc', ''), ',', '') = ''
	AND LTRIM(S) > ''

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-06 : 15:53:12

Where is rintus now to confirm?

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-06 : 16:06:12

Peso, i have one more question:
The replace(s) don't replace any blank spaces between Co and , and Sc.
So why can we test with = ''?

Planning replaces chance by mistake

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-06 : 16:20:15

Trailing spaces are omitted by the query engine.

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-06 : 16:27:22

ok - thank you.

Planning replaces chance by mistake

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-06 : 16:55:39

BTW, "," only will also fail...

SELECT	N,
	R,
	S
FROM	@Sample
WHERE	REPLACE(REPLACE(REPLACE(S, 'Co', ''), 'Sc', ''), ',', '') = ''
	AND LTRIM(S) not in ('',',')

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-06 : 16:58:42

AND ltrim(replace(S,',','')) > ''

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources