query - 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

query

Author

Topic

WoodHouse
Posting Yak Master

211 Posts

Posted - 2010-01-12 : 05:45:14

hi

CREATE TABLE #TEMP(ID INT , COURSE VARCHAR(50))

INSERT INTO #TEMP
SELECT 10, 'SCI' UNION ALL
SELECT 10, 'SOCIAL' UNION ALL
SELECT 10, 'maths' UNION ALL
SELECT 20, 'SCI' UNION ALL
SELECT 30, 'SOCIAL'

I need to write the query to find who will take the both the course

for course name input param...

for example i will pass SCI & SOCIAL

I need output ID 10

help on this..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-12 : 05:45:54

how will you passing them? as comma separated values?

WoodHouse
Posting Yak Master

211 Posts

Posted - 2010-01-12 : 05:48:56

DECLARE @STRING VARCHAR(50)

SET @STRING = 'SCI,SOCAIL'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-12 : 05:50:59

here's a way (passing csv)


CREATE PROC getdetails
@CourseList varchar(8000)
AS
DECLARE @Cnt int
SELECT @Cnt = LEN(@CourseList)-LEN(REPLACE(@CourseList,',','')) + 1

SELECT ID
FROM Table
WHERE @CourseList LIKE '%,' + COURSE + ',%'
GROUP BY ID 
HAVING COUNT(DISTINCT COURSE)=@Cnt
GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-12 : 05:51:36

execute like EXEC getdetails @STRING

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2010-01-12 : 05:53:50

try patindex also



CREATE TABLE #TEMP(ID INT , COURSE VARCHAR(50))

INSERT INTO #TEMP
SELECT 10, 'SCI' UNION ALL
SELECT 10, 'SOCIAL' UNION ALL
SELECT 10, 'maths' UNION ALL
SELECT 20, 'SCI' UNION ALL
SELECT 30, 'SOCIAL'

DECLARE @STRING VARCHAR(50)

SET @STRING = 'SCI,SOCAIL'

SELECT * FROM #TEMP WHERE PATINDEX('%,' + CAST( COURSE AS VARCHAR(255)) +',%',','+@STRING+',' )>0

WoodHouse
Posting Yak Master

211 Posts

Posted - 2010-01-12 : 05:56:53

quote:

Originally posted by bklr

try patindex also



CREATE TABLE #TEMP(ID INT , COURSE VARCHAR(50))

INSERT INTO #TEMP
SELECT 10, 'SCI' UNION ALL
SELECT 10, 'SOCIAL' UNION ALL
SELECT 10, 'maths' UNION ALL
SELECT 20, 'SCI' UNION ALL
SELECT 30, 'SOCIAL'

DECLARE @STRING VARCHAR(50)

SET @STRING = 'SCI,SOCAIL'

SELECT * FROM #TEMP WHERE PATINDEX('%,' + CAST( COURSE AS VARCHAR(255)) +',%',','+@STRING+',' )>0

your query output below like this..
but i need who took both the course SCI & SOCIAL


ID	COURSE
10	SCI
20	SCI

WoodHouse
Posting Yak Master

211 Posts

Posted - 2010-01-12 : 05:57:42

quote:
Originally posted by visakh16

execute like EXEC getdetails @STRING

Your solution will get blank..result set

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2010-01-12 : 06:01:57

quote:
Originally posted by WoodHouse

quote:
Originally posted by bklr

try patindex also

CREATE TABLE #TEMP(ID INT , COURSE VARCHAR(50))

INSERT INTO #TEMP
SELECT 10, 'SCI' UNION ALL
SELECT 10, 'SOCIAL' UNION ALL
SELECT 10, 'maths' UNION ALL
SELECT 20, 'SCI' UNION ALL
SELECT 30, 'SOCIAL'

DECLARE @STRING VARCHAR(50)

SET @STRING = 'SCI,SOCAIL'

SELECT * FROM #TEMP WHERE PATINDEX('%,' + CAST( COURSE AS VARCHAR(255)) +',%',','+@STRING+',' )>0

your query output below like this..
but i need who took both the course SCI & SOCIAL

ID COURSE
10 SCI
20 SCI

just check out the spelling for the input social u have given as SOCAIL

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2010-01-12 : 06:04:28

quote:
Originally posted by WoodHouse

quote:
Originally posted by visakh16

execute like EXEC getdetails @STRING

Your solution will get blank..result set

use like as
small modification to visakh query
SELECT * FROM #TEMP
WHERE @STRING LIKE '%'+ COURSE +'%'

SELECT * FROM #TEMP
WHERE ','+ @STRING+',' LIKE '%,'+ COURSE +',%'

WoodHouse
Posting Yak Master

211 Posts

Posted - 2010-01-12 : 06:05:33

Am sure your query will return two columns like below ...

ID COURSE
10 SCI
20 SCI

But i need who took two course...

WoodHouse
Posting Yak Master

211 Posts

Posted - 2010-01-12 : 06:09:20

DECLARE @CourseList varchar(8000)

SET @CourseList = 'SCI,SOCAIL'

DECLARE @Cnt int
SELECT @Cnt = LEN(@CourseList)-LEN(REPLACE(@CourseList,',','')) + 1

SELECT ID
FROM #TEMP
WHERE ','+ @CourseList+',' LIKE '%,'+ COURSE +',%'
GROUP BY ID
HAVING COUNT(DISTINCT COURSE)=@Cnt

above one also i get blank

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-12 : 06:42:43

quote:
Originally posted by WoodHouse

DECLARE @CourseList varchar(8000)

SET @CourseList = 'SCI,SOCAIL'

DECLARE @Cnt int
SELECT @Cnt = LEN(@CourseList)-LEN(REPLACE(@CourseList,',','')) + 1

SELECT ID
FROM #TEMP
WHERE ','+ @CourseList+',' LIKE '%,'+ COURSE +',%'
GROUP BY ID
HAVING COUNT(DISTINCT COURSE)=@Cnt

above one also i get blank

make sure you pass same value as in your data SOCIAL not SOCAIL

WoodHouse
Posting Yak Master

211 Posts

Posted - 2010-01-12 : 06:50:07

CREATE TABLE #TEMP(ID INT , COURSE VARCHAR(50))

INSERT INTO #TEMP
SELECT 10, 'SCI' UNION ALL
SELECT 10, 'SOCIAL' UNION ALL
SELECT 10, 'maths' UNION ALL
SELECT 20, 'SCI' UNION ALL
SELECT 30, 'SOCIAL'

DECLARE @CourseList varchar(8000)

SET @CourseList = 'SCI,SOCIAL'

If you want you can change the input param format so that i can change in front end...

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2010-01-12 : 07:02:24

[code]
is this u want
DROP TABLE #TEMP
CREATE TABLE #TEMP(ID INT , COURSE VARCHAR(50))

INSERT INTO #TEMP
SELECT 10, 'SCI' UNION ALL
SELECT 10, 'SOCIAL' UNION ALL
SELECT 10, 'maths' UNION ALL
SELECT 20, 'SCI' UNION ALL
SELECT 30, 'SOCIAL'

DECLARE @STRING VARCHAR(50)
SET @STRING = 'SCI,SOCIAL'

SELECT ID FROM #TEMP WHERE PATINDEX('%,'+ COURSE +',%','%,'+@STRING+',%' )> 0
GROUP BY ID HAVING COUNT(DISTINCT Course) >1
[/code]

brian147
Starting Member

10 Posts

Posted - 2010-01-12 : 07:02:25

you could try a user-defined table function to parse the input parameter into rows (there are plenty of "split" type functions out there), then do:

select ID
from #TEMP
where COURSE in (select rowname from dbo.udfSplitFunction(@inputParameter)
group by ID
having count(*) > 1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-12 : 07:11:50

quote:
Originally posted by WoodHouse

CREATE TABLE #TEMP(ID INT , COURSE VARCHAR(50))

INSERT INTO #TEMP
SELECT 10, 'SCI' UNION ALL
SELECT 10, 'SOCIAL' UNION ALL
SELECT 10, 'maths' UNION ALL
SELECT 20, 'SCI' UNION ALL
SELECT 30, 'SOCIAL'

DECLARE @CourseList varchar(8000)

SET @CourseList = 'SCI,SOCIAL'

If you want you can change the input param format so that i can change in front end...

Is it still showing blank?

WoodHouse
Posting Yak Master

211 Posts

Posted - 2010-01-12 : 07:18:45

Hi

Can you check below your code...

DROP TABLE #TEMP
CREATE TABLE #TEMP(ID INT , COURSE VARCHAR(50))

INSERT INTO #TEMP
SELECT 10, 'SCI' UNION ALL
SELECT 10, 'SOCIAL' UNION ALL
SELECT 10, 'MATHS' UNION ALL
SELECT 20, 'SCI' UNION ALL
SELECT 30, 'SOCIAL' UNION ALL
SELECT 30, 'MATHS'

DECLARE @CourseList varchar(8000)
SET @CourseList = 'SOCIAL,MATHS'

DECLARE @Cnt int
SELECT @Cnt = LEN(@CourseList)-LEN(REPLACE(@CourseList,',','')) + 1

SELECT ID
FROM #TEMP
WHERE @CourseList LIKE '%,' + COURSE + ',%'
GROUP BY ID
HAVING COUNT(DISTINCT COURSE)=@Cnt
GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-12 : 07:20:49

quote:
Originally posted by WoodHouse

Hi

Can you check below your code...

DROP TABLE #TEMP
CREATE TABLE #TEMP(ID INT , COURSE VARCHAR(50))

INSERT INTO #TEMP
SELECT 10, 'SCI' UNION ALL
SELECT 10, 'SOCIAL' UNION ALL
SELECT 10, 'MATHS' UNION ALL
SELECT 20, 'SCI' UNION ALL
SELECT 30, 'SOCIAL' UNION ALL
SELECT 30, 'MATHS'

DECLARE @CourseList varchar(8000)
SET @CourseList = 'SOCIAL,MATHS'

DECLARE @Cnt int
SELECT @Cnt = LEN(@CourseList)-LEN(REPLACE(@CourseList,',','')) + 1

SELECT ID
FROM #TEMP
WHERE ',' + @CourseList + ',' LIKE '%,' + COURSE + ',%'
GROUP BY ID
HAVING COUNT(DISTINCT COURSE)=@Cnt
GO

modify like above

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2010-01-12 : 07:20:53

DROP TABLE #TEMP
CREATE TABLE #TEMP(ID INT , COURSE VARCHAR(50))

INSERT INTO #TEMP
SELECT 10, 'SCI' UNION ALL
SELECT 10, 'SOCIAL' UNION ALL
SELECT 10, 'MATHS' UNION ALL
SELECT 20, 'SCI' UNION ALL
SELECT 30, 'SOCIAL' UNION ALL
SELECT 30, 'MATHS'

DECLARE @CourseList varchar(8000)
SET @CourseList = 'SOCIAL,MATHS'

DECLARE @Cnt int
SELECT @Cnt = LEN(@CourseList)-LEN(REPLACE(@CourseList,',','')) + 1

SELECT ID
FROM #TEMP
WHERE @CourseList LIKE '%' + COURSE + '%'
GROUP BY ID
HAVING COUNT(DISTINCT COURSE)=@Cnt
GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-12 : 07:23:13

quote:
Originally posted by bklr

DROP TABLE #TEMP
CREATE TABLE #TEMP(ID INT , COURSE VARCHAR(50))

INSERT INTO #TEMP
SELECT 10, 'SCI' UNION ALL
SELECT 10, 'SOCIAL' UNION ALL
SELECT 10, 'MATHS' UNION ALL
SELECT 20, 'SCI' UNION ALL
SELECT 30, 'SOCIAL' UNION ALL
SELECT 30, 'MATHS'

DECLARE @CourseList varchar(8000)
SET @CourseList = 'SOCIAL,MATHS'

DECLARE @Cnt int
SELECT @Cnt = LEN(@CourseList)-LEN(REPLACE(@CourseList,',','')) + 1

SELECT ID
FROM #TEMP
WHERE @CourseList LIKE '%' + COURSE + '%'
GROUP BY ID
HAVING COUNT(DISTINCT COURSE)=@Cnt
GO

wont always return correct results

consider case where you've identical course names

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources