Result that matches all the values from a list

Question

Post reply

Result that matches all the values from a list

prasanta.paul 88490

SSC Rookie

Points: 31
More actions
August 22, 2013 at 12:45 am

#281552

I have two tables which are listed below.
TableA
StudentID StudentName
1 A
2 B
3 C
4 D
5 E
TableB
StudentID ClassID SectionID
1 2 5
3 2 7
Now I am trying to retrive those students for which all items in a input list matches.
For example, If I pass the input list(ClassID & SectionID) as (2, 5), it should return StudentID : 1
If I pass the input list as (2, 5 | 1, 1) it should not return the StudentID : 1
DECLARE @tblData AS TABLE
(
[ClassID] INT
,[SectionID] INT
)
INSERT INTO @tblData VALUES (2, 5)
INSERT INTO @tblData VALUES (2, 1)
SELECT
A.[StudentID]
,A.[StudentName]
,B.[ClassID]
,B.[SectionID]
FROM
[AAAAAA] AS A
INNER JOIN [BBBBBB] AS B
ON A.[StudentID] = B.[StudentID]
INNER JOIN @tblData AS C
On B.[ClassID] = C.[ClassID] AND B.[SectionID] = C.[SectionID]
But above mentioned query does not returning the expected value.
Can you please help me?
Regards,
P. Paul

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

kapil_kk SSC-Insane Points: 21316 More actions · Answer 1

how are you passing value to the list...

if you pass values to list in this manner list(2,5) then you can create a procedure for this...

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

Join @tblData to TableB, group by StudentID, and return those rows having count(*) equal to the number of rows in @tblData. Without full DDL and sample data, I can't be of any more help than that.

John

prasanta.paul 88490 SSC Rookie Points: 31 More actions · Answer 3

John Mitchell-245523 (8/22/2013)
Join @tblData to TableB, group by StudentID, and return those rows having count(*) equal to the number of rows in @tblData. Without full DDL and sample data, I can't be of any more help than that.
John

Hi John,

Please find the DDL and sample data below or you can check this link.

CREATE TABLE A

([StudentID] int, [StudentName] varchar(1))

;

INSERT INTO A

([StudentID], [StudentName])

VALUES

(1, 'A'),

(2, 'B'),

(3, 'C'),

(4, 'D'),

(5, 'E')

;

CREATE TABLE B

([StudentID] int, [ClassID] int, [SectionID] int)

;

INSERT INTO B

([StudentID], [ClassID], [SectionID])

VALUES

(1, 2, 5),

(3, 2, 7),

(1, 2, 1)

;

CREATE TABLE TblData ([ClassID] int, [SectionID] int)

INSERT INTO TblData values (2,5);

INSERT INTO TblData values (2,7);

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

Here you go. Obviously it doesn't return any rows for your sample data, but if it did, you could join back to A if you need the student name.

John

SELECT

B.StudentID

FROM

B

JOIN

TblData d ON B.ClassID = d.ClassID AND B.SectionID = d.SectionID

GROUP BY

b.StudentID

HAVING

COUNT(*) = (SELECT COUNT(*) FROM TblData)

prasanta.paul 88490 SSC Rookie Points: 31 More actions · Answer 5

Hi John,

I have changed the code as below. It is returning data.

FROM

HAVING

COUNT(*) = (SELECT COUNT(*) FROM TblData)

TO

HAVING

COUNT(*) > 0

Am I doing correct?

Regards,

P.Paul

John Mitchell-245523 (8/22/2013)
Here you go. Obviously it doesn't return any rows for your sample data, but if it did, you could join back to A if you need the student name.
John
SELECT
B.StudentID
FROM
B
JOIN
TblData d ON B.ClassID = d.ClassID AND B.SectionID = d.SectionID
GROUP BY
b.StudentID
HAVING
COUNT(*) = (SELECT COUNT(*) FROM TblData)

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 6

prasanta.paul 88490 (8/22/2013)
Am I doing correct?

You tell me. Is it returning the results you expected? If it is, then your requirement has changed, because you said you only wanted students that matched each row in tblData. The way you've done it, you'll get students that match one or more rows.

John

prasanta.paul 88490 SSC Rookie Points: 31 More actions · Answer 7

Hi John,

I got your point. Yes your solution is correct for my question.

Thanks for your great help!

Regards,

P.Paul

John Mitchell-245523 (8/22/2013)
prasanta.paul 88490 (8/22/2013)
Am I doing correct?
You tell me. Is it returning the results you expected? If it is, then your requirement has changed, because you said you only wanted students that matched each row in tblData. The way you've done it, you'll get students that match one or more rows.
John

kapil_kk SSC-Insane Points: 21316 More actions · Answer 8

John Mitchell-245523 (8/22/2013)
Here you go. Obviously it doesn't return any rows for your sample data, but if it did, you could join back to A if you need the student name.
John
SELECT
B.StudentID
FROM
B
JOIN
TblData d ON B.ClassID = d.ClassID AND B.SectionID = d.SectionID
GROUP BY
b.StudentID
HAVING
COUNT(*) = (SELECT COUNT(*) FROM TblData)

Can you please tell me why we need this -

HAVING

COUNT(*) = (SELECT COUNT(*) FROM TblData)

its not clear to me..

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 9

Because you're looking for those students who have a match with every row in tblData. Therefore the row count for each student needs to be the same as the rowcount for tblData.

John