Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Result that matches all the values from a list Expand / Collapse
Author
Message
Posted Thursday, August 22, 2013 12:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 2, 2014 10:23 PM
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
Post #1487075
Posted Thursday, August 22, 2013 1:23 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, December 14, 2014 11:09 PM
Points: 1,962, Visits: 2,406
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/
Post #1487086
Posted Thursday, August 22, 2013 1:27 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:56 AM
Points: 5,487, Visits: 10,335
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
Post #1487087
Posted Thursday, August 22, 2013 1:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 2, 2014 10:23 PM
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);

Post #1487088
Posted Thursday, August 22, 2013 1:39 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:56 AM
Points: 5,487, Visits: 10,335
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)

Post #1487089
Posted Thursday, August 22, 2013 2:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 2, 2014 10:23 PM
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)

Post #1487102
Posted Thursday, August 22, 2013 2:09 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:56 AM
Points: 5,487, Visits: 10,335
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
Post #1487104
Posted Thursday, August 22, 2013 2:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 2, 2014 10:23 PM
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
Post #1487111
Posted Thursday, August 22, 2013 3:26 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, December 14, 2014 11:09 PM
Points: 1,962, Visits: 2,406
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/
Post #1487131
Posted Thursday, August 22, 2013 3:36 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:56 AM
Points: 5,487, Visits: 10,335
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
Post #1487139
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse