SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Result that matches all the values from a list


Result that matches all the values from a list

Author
Message
prasanta.paul 88490
prasanta.paul 88490
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 27
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
kapil_kk
kapil_kk
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5264 Visits: 2767
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
John Mitchell-245523
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34139 Visits: 16649
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
prasanta.paul 88490
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 27
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
John Mitchell-245523
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34139 Visits: 16649
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
prasanta.paul 88490
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 27
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
John Mitchell-245523
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34139 Visits: 16649
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
prasanta.paul 88490
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 27
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
kapil_kk
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5264 Visits: 2767
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
John Mitchell-245523
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34139 Visits: 16649
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search