April 4, 2005 at 8:21 am
Hi,
I need to extract rows from a table in such a way that those rows fulfilling all the search conditions are reported.
Eg:
Table X
Student Subject
A English
A French
A German
B English
B Spanish
B German
C English
C Italian
C Spanish
C German
D French
D German
If I search for those students who are enrolled for German & Spanish then results are : B & C.
I've tried for long but I think I'm missing somthing very basic. Please help.
Regards
Deepa
Deepa
April 4, 2005 at 8:39 am
It's not the neatest thing, however it will give you the results you are looking for.
create table #x (student char(1))
insert into #x select student from
where subject = 'german'
insert into #x select student from
where subject = 'spanish'
select student from #x group by student having count(student) >1
drop table #x
April 4, 2005 at 8:57 am
You can also try this one:
SELECT *
FROM StudentSubject A
WHERE EXISTS (SELECT 'X' FROM StudentSubject B WHERE A.Student = B.Student AND B.Subject = 'German') AND
EXISTS (SELECT 'X' FROM StudentSubject C WHERE A.Student = C.Student AND C.Subject = 'Spanish') ... and so on
or this one:
SELECT A.* FROM
(SELECT * FROM StudentSubject
WHERE Subject = 'Spanish') A INNER JOIN
(SELECT * FROM StudentSubject
WHERE Subject = 'German') B
ON A.Student = B.Student
April 4, 2005 at 9:32 am
Select Student from dbo.TableX where Subject IN ('German','Spanish')
GROUP BY Student
HAVING COUNT(*) = 2
I have never tested all the proposed solution for best performances. But the neat thing about this one is that you can pass the list of languages in a variable and use a Split Function for the in clause like this :
create proc A @Languages as varchar(7998)
as
Select Student from dbo.TableX where Subject IN (Select Element from dbo.Split(@Languages))
GROUP BY Student
HAVING COUNT(*) = (Select MAX(ElementID) from dbo.Split(@Languages)) --might run faster if you set a variable and put it here.. don't know if the function is called on every line in this case.
Note that there are also set based solution for the split function that can run faster than this one.
CREATE FUNCTION [dbo].[Split] (@vcDelimitedString nVarChar(4000),
@vcDelimiternVarChar(100) )
/**************************************************************************
DESCRIPTION: Accepts a delimited string and splits it at the specified
delimiter points. Returns the individual items as a table data
type with the ElementID field as the array index and the Element
field as the data
PARAMETERS:
@vcDelimitedString- The string to be split
@vcDelimiter- String containing the delimiter where
delimited string should be split
RETURNS:
Table data type containing array of strings that were split with
the delimiters removed from the source string
USAGE:
SELECT ElementID, Element FROM Split('11111,22222,3333', ',') ORDER BY ElementID
AUTHOR:Karen Gayda
DATE: 05/31/2001
MODIFICATION HISTORY:
WHODATEDESCRIPTION
----------------------------------------------------------------
***************************************************************************/
RETURNS @tblArray TABLE
(
ElementIDsmallintIDENTITY(1,1) not null primary key, --Array index
ElementnVarChar(1200)null--Array element contents
)
AS
BEGIN
DECLARE
@siIndexsmallint,
@siStartsmallint,
@siDelSizesmallint
SET @siDelSize= LEN(@vcDelimiter)
--loop through source string and add elements to destination table array
WHILE LEN(@vcDelimitedString) > 0
BEGIN
SET @siIndex = CHARINDEX(@vcDelimiter, @vcDelimitedString)
IF @siIndex = 0
BEGIN
INSERT INTO @tblArray (Element) VALUES(@vcDelimitedString)
BREAK
END
ELSE
BEGIN
INSERT INTO @tblArray (Element) VALUES(SUBSTRING(@vcDelimitedString, 1,@siIndex - 1))
SET @siStart = @siIndex + @siDelSize
SET @vcDelimitedString = SUBSTRING(@vcDelimitedString, @siStart , LEN(@vcDelimitedString) - @siStart + 1)
END
END
RETURN
END
April 4, 2005 at 10:25 am
Hi,
Unfortunately it does not help a lot.
In my case, the number of students, number of courses they are subscribed to and the number of subjects in the search string are variants.
I need something whereby I can search for any combination of subjects.
Help!!!!!
Deepa
Deepa
April 4, 2005 at 11:15 am
Have you actually tried my solution???????
You can search for students that are suscribed in any number of course without modifying the proc. What else do you need?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply