February 23, 2016 at 8:16 am
My requirement is
list of Students registered to both music(TSM) and dance(PADA) school along with their parents details.
select distinct
r.ID, r.FULL_NAME, r.FULL_ADDRESS,r.MEMBER_TYPE, r.BIRTH_DATE,
CONVERT (int,DATEDIFF(hour,r.BIRTH_DATE,GETDATE())/8766)as Age,r.MEETING_TYPE,r.MEETING,r.TITLE,
r.Parent1_FullName,r.Parent1_Cell,r.Parent1_Email,
r.Parent2_FullName,r.Parent2_Cell,r.Parent2_Email
from IMIS.dbo.vw_csys_registrations r
WHERE r.MEETING_TYPE in ('PADA', 'TSM') and r.MEMBER_TYPE LIKE '%CH'
order by r.ID, r.MEETING_TYPE, Age
I am getting results with this query, but I am getting more dance students than music students.....My assumption was, I should see 2 records for each student....1 row with music and another row with dance.
can someone suggest, what exactly am I doing wrong with this query.
I have attached the create script for the view.
Regards,
SQLisAwe5oMe.
February 23, 2016 at 8:31 am
Your problem is you are getting students in either not both
Here is my try (this incorrect but might give you an idea):
WITH cte1 AS (
select distinct
r.ID, r.FULL_NAME, r.FULL_ADDRESS,r.MEMBER_TYPE, r.BIRTH_DATE,
CONVERT (int,DATEDIFF(hour,r.BIRTH_DATE,GETDATE())/8766)as Age,r.MEETING_TYPE,r.MEETING,r.TITLE,
r.Parent1_FullName,r.Parent1_Cell,r.Parent1_Email,
r.Parent2_FullName,r.Parent2_Cell,r.Parent2_Email
from IMIS.dbo.vw_csys_registrations r
WHERE r.MEETING_TYPE in ('PADA') and r.MEMBER_TYPE LIKE '%CH'
)
, cte2 AS (
select distinct
r.ID, r.FULL_NAME, r.FULL_ADDRESS,r.MEMBER_TYPE, r.BIRTH_DATE,
CONVERT (int,DATEDIFF(hour,r.BIRTH_DATE,GETDATE())/8766)as Age,r.MEETING_TYPE,r.MEETING,r.TITLE,
r.Parent1_FullName,r.Parent1_Cell,r.Parent1_Email,
r.Parent2_FullName,r.Parent2_Cell,r.Parent2_Email
from IMIS.dbo.vw_csys_registrations r
WHERE r.MEETING_TYPE in ('TSM') and r.MEMBER_TYPE LIKE '%CH'
)
SELECT r.ID, r.FULL_NAME, r.FULL_ADDRESS,r.MEMBER_TYPE, r.BIRTH_DATE, AGE,
r.MEETING_TYPE,r.MEETING,r.TITLE,
r.Parent1_FullName,r.Parent1_Cell,r.Parent1_Email,
r.Parent2_FullName,r.Parent2_Cell,r.Parent2_Email
FROM cte1
JOIN cte2 ON cte1.id = cte2.id;
February 23, 2016 at 9:36 am
Thank you djj. I modified the query and got the results. I have about 49 unique students registered to both music and dance. Appreciate your help.
WITH cte1 AS (
select distinct
r.ID, r.FULL_NAME, r.FULL_ADDRESS,r.MEMBER_TYPE, r.BIRTH_DATE,
CONVERT (int,DATEDIFF(hour,r.BIRTH_DATE,GETDATE())/8766)as Age,r.MEETING_TYPE,r.MEETING,r.TITLE,
r.Parent1_FullName,r.Parent1_Cell,r.Parent1_Email,
r.Parent2_FullName,r.Parent2_Cell,r.Parent2_Email
from IMIS.dbo.vw_csys_registrations r
WHERE r.MEETING_TYPE in ('PADA') and r.MEMBER_TYPE LIKE '%CH'
)
, cte2 AS (
select distinct
r.ID, r.FULL_NAME, r.FULL_ADDRESS,r.MEMBER_TYPE, r.BIRTH_DATE,
CONVERT (int,DATEDIFF(hour,r.BIRTH_DATE,GETDATE())/8766)as Age,r.MEETING_TYPE,r.MEETING,r.TITLE,
r.Parent1_FullName,r.Parent1_Cell,r.Parent1_Email,
r.Parent2_FullName,r.Parent2_Cell,r.Parent2_Email
from IMIS.dbo.vw_csys_registrations r
WHERE r.MEETING_TYPE in ('TSM') and r.MEMBER_TYPE LIKE '%CH'
)
SELECT cte1.ID, cte1.FULL_NAME, cte1.FULL_ADDRESS,cte1.MEMBER_TYPE, cte1.BIRTH_DATE, cte1.Age,
cte1.MEETING_TYPE,cte1.MEETING,cte1.TITLE,
cte1.Parent1_FullName,cte1.Parent1_Cell,cte1.Parent1_Email,
cte1.Parent2_FullName,cte1.Parent2_Cell,cte1.Parent2_Email
FROM cte1
JOIN cte2 ON cte1.id = cte2.id;
Regards,
SQLisAwe5oMe.
February 23, 2016 at 3:40 pm
SELECT
r.ID, r.FULL_NAME, r.FULL_ADDRESS,r.MEMBER_TYPE, r.BIRTH_DATE,
CONVERT (int,DATEDIFF(hour,r.BIRTH_DATE,GETDATE())/8766)as Age,r.MEETING_TYPE,r.MEETING,r.TITLE,
r.Parent1_FullName,r.Parent1_Cell,r.Parent1_Email,
r.Parent2_FullName,r.Parent2_Cell,r.Parent2_Email
FROM (
SELECT r2.ID
FROM IMIS.dbo.vw_csys_registrations r2
WHERE r2.MEETING_TYPE in ('PADA', 'TSM') AND r2.MEMBER_TYPE LIKE '%CH'
GROUP BY r2.ID
HAVING COUNT(*) = 2
) AS r2
INNER JOIN IMIS.dbo.vw_csys_registrations r ON r.ID = r2.ID AND
r.MEETING_TYPE in ('PADA', 'TSM') AND r.MEMBER_TYPE LIKE '%CH'
ORDER BY r.ID, MEETING_TYPE
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 24, 2016 at 7:59 am
ScottPletcher (2/23/2016)
SELECT
r.ID, r.FULL_NAME, r.FULL_ADDRESS,r.MEMBER_TYPE, r.BIRTH_DATE,
CONVERT (int,DATEDIFF(hour,r.BIRTH_DATE,GETDATE())/8766)as Age,r.MEETING_TYPE,r.MEETING,r.TITLE,
r.Parent1_FullName,r.Parent1_Cell,r.Parent1_Email,
r.Parent2_FullName,r.Parent2_Cell,r.Parent2_Email
FROM (
SELECT r2.ID
FROM IMIS.dbo.vw_csys_registrations r2
WHERE r2.MEETING_TYPE in ('PADA', 'TSM') AND r2.MEMBER_TYPE LIKE '%CH'
GROUP BY r2.ID
HAVING COUNT(*) = 2
) AS r2
INNER JOIN IMIS.dbo.vw_csys_registrations r ON r.ID = r2.ID AND
r.MEETING_TYPE in ('PADA', 'TSM') AND r.MEMBER_TYPE LIKE '%CH'
ORDER BY r.ID, MEETING_TYPE
Thanks Scott,
This is also working. Appreciate your help as always.
Regards,
SQLisAwe5oMe.
February 24, 2016 at 8:22 am
select
r.ID, r.FULL_NAME, r.FULL_ADDRESS,r.MEMBER_TYPE, r.BIRTH_DATE,
CONVERT (int,DATEDIFF(hour,r.BIRTH_DATE,GETDATE())/8766)as Age,
r.Parent1_FullName,r.Parent1_Cell,r.Parent1_Email,
r.Parent2_FullName,r.Parent2_Cell,r.Parent2_Email
from IMIS.dbo.vw_csys_registrations r
WHERE r.MEETING_TYPE='PADA' and r.MEMBER_TYPE LIKE '%CH'
intersect
select
r.ID, r.FULL_NAME, r.FULL_ADDRESS,r.MEMBER_TYPE, r.BIRTH_DATE,
CONVERT (int,DATEDIFF(hour,r.BIRTH_DATE,GETDATE())/8766)as Age,
r.Parent1_FullName,r.Parent1_Cell,r.Parent1_Email,
r.Parent2_FullName,r.Parent2_Cell,r.Parent2_Email
from IMIS.dbo.vw_csys_registrations r
WHERE r.MEETING_TYPE='TSM' and r.MEMBER_TYPE LIKE '%CH'
order by r.ID, Age
February 24, 2016 at 8:43 am
palotaiarpad (2/24/2016)
selectr.ID, r.FULL_NAME, r.FULL_ADDRESS,r.MEMBER_TYPE, r.BIRTH_DATE,
CONVERT (int,DATEDIFF(hour,r.BIRTH_DATE,GETDATE())/8766)as Age,
r.Parent1_FullName,r.Parent1_Cell,r.Parent1_Email,
r.Parent2_FullName,r.Parent2_Cell,r.Parent2_Email
from IMIS.dbo.vw_csys_registrations r
WHERE r.MEETING_TYPE='PADA' and r.MEMBER_TYPE LIKE '%CH'
intersect
select
r.ID, r.FULL_NAME, r.FULL_ADDRESS,r.MEMBER_TYPE, r.BIRTH_DATE,
CONVERT (int,DATEDIFF(hour,r.BIRTH_DATE,GETDATE())/8766)as Age,
r.Parent1_FullName,r.Parent1_Cell,r.Parent1_Email,
r.Parent2_FullName,r.Parent2_Cell,r.Parent2_Email
from IMIS.dbo.vw_csys_registrations r
WHERE r.MEETING_TYPE='TSM' and r.MEMBER_TYPE LIKE '%CH'
order by r.ID, Age
Wow palotaiarpad.....interesting using intersect.
Even though the other queries were working, I had to export to excel and remove duplicates, etc to get the unique 49 results.
This query gives me the exact 49 results. Thanks for your support.
Regards,
SQLisAwe5oMe.
February 24, 2016 at 10:03 am
SQLisAwE5OmE (2/24/2016)
Even though the other queries were working, I had to export to excel and remove duplicates, etc to get the unique 49 results.This query gives me the exact 49 results. Thanks for your support.
Your initial post said that you expected TWO records per student and several of the responses went to great lengths to ensure that you got both records. If you had only wanted ONE record per student, you should have said so from the start. It would have greatly simplified many of the previous responses and you wouldn't have had to dedupe.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 24, 2016 at 10:35 am
drew.allen (2/24/2016)
SQLisAwE5OmE (2/24/2016)
Even though the other queries were working, I had to export to excel and remove duplicates, etc to get the unique 49 results.This query gives me the exact 49 results. Thanks for your support.
Your initial post said that you expected TWO records per student and several of the responses went to great lengths to ensure that you got both records. If you had only wanted ONE record per student, you should have said so from the start. It would have greatly simplified many of the previous responses and you wouldn't have had to dedupe.
Drew
Hi Drew, Thanks for pointing it out....but other previous queries were also correct. Previous queries were listing students activities for all music/dance course.
For example, if the student were registered to more than 1 music course, it was listing.....it was expected.
but I wasn't sure the query could eliminate the duplicate and the get the unique ones.
Anyway, thanks for all the assistance from everyone.....I have a better idea now....having seen different way to get the results.
Regards,
SQLisAwe5oMe.
February 24, 2016 at 4:34 pm
I did mine differently. I checked for existence of both class types in the "attends" table...
CREATE TABLE Student(
StudentID INT IDENTITY PRIMARY KEY,
FirstName VARCHAR(10) NOT NULL,
LastName VARCHAR(15) NOT NULL
);
GO
INSERT INTO Student(FirstName,LastName) VALUES ('Bart','Simpson'),('Lisa','Simpson'),('Nelson','Munz');
CREATE TABLE Meeting(
MeetingID INT IDENTITY(1000,1) PRIMARY KEY,
MeetingType VARCHAR(4) NOT NULL
);
GO
INSERT INTO Meeting(MeetingType) VALUES ('PADA'),('TSM');
CREATE TABLE Attends(MeetingID INT NOT NULL,StudentID INT NOT NULL);
INSERT INTO Attends(MeetingID, StudentID) VALUES (1000,1),(1001,1),(1001,1),(1002,2);
SELECT S.FirstName, S.LastName
FROM Student S
WHERE EXISTS (SELECT 1 FROM Attends a
INNER JOIN Meeting m ON a.MeetingID=m.MeetingID
WHERE m.MeetingType='PADA' AND a.StudentID = S.StudentID)
AND EXISTS (SELECT 1 FROM Attends a
INNER JOIN Meeting m ON a.MeetingID=m.MeetingID
WHERE m.MeetingType='TSM' AND a.StudentID=S.StudentID);
Looks to me like the COUNT stuff is a red herring. You don't need to count at all, you just need to check for existence with two different "course" criteria. (Sorry, this looks a lot like your standard "Students and Classes" database where you're asking "Show me students that took class A and class B.) If you do the filtering with EXISTS, you don't get duplicate values returned.
February 24, 2016 at 5:57 pm
pietlinden (2/24/2016)
I did mine differently. I checked for existence of both class types in the "attends" table...
CREATE TABLE Student(StudentID INT IDENTITY PRIMARY KEY,
FirstName VARCHAR(10) NOT NULL,
LastName VARCHAR(15) NOT NULL
);
GO
INSERT INTO Student(FirstName,LastName) VALUES ('Bart','Simpson'),('Lisa','Simpson'),('Nelson','Munz');
CREATE TABLE Meeting(
MeetingID INT IDENTITY(1000,1) PRIMARY KEY,
MeetingType VARCHAR(4) NOT NULL
);
GO
INSERT INTO Meeting(MeetingType) VALUES ('PADA'),('TSM');
CREATE TABLE Attends(MeetingID INT NOT NULL,StudentID INT NOT NULL);
INSERT INTO Attends(MeetingID, StudentID) VALUES (1000,1),(1001,1),(1001,1),(1002,2);
SELECT S.FirstName, S.LastName
FROM Student S
WHERE EXISTS (SELECT 1 FROM Attends a
INNER JOIN Meeting m ON a.MeetingID=m.MeetingID
WHERE m.MeetingType='PADA' AND a.StudentID = S.StudentID)
AND EXISTS (SELECT 1 FROM Attends a
INNER JOIN Meeting m ON a.MeetingID=m.MeetingID
WHERE m.MeetingType='TSM' AND a.StudentID=S.StudentID);
Looks to me like the COUNT stuff is a red herring. You don't need to count at all, you just need to check for existence with two different "course" criteria. (Sorry, this looks a lot like your standard "Students and Classes" database where you're asking "Show me students that took class A and class B.) If you do the filtering with EXISTS, you don't get duplicate values returned.
The COUNT is for efficiency. With two values to compare, not a big difference in multiple checks of the table. But what about 10 values, or 20 or 50? A GROUPing only requires one scan of the table to determine any number of existence checks.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply