Join with more rows

Coco Beans
Coco Beans used Ask the Experts™
on
Students                              Registrations                  
                                          
Name      Student ID                  Name      Student ID      Registration ID      Registered  Subject
Jane      1324                              Jane      1324              3213215                      Y                   Math
Joe              1234                              Jane      1324              3213215               N                  Math
Clare      6784                              Joe      1234              54654646             Y                    English
James      8973                               Joe      1234              54654646             N                   English
                                                   


Hi Everyone,

Any idea how'd I'd return subject from registration.  There are more students in the student table but more entries on the registration table.  I need all the students on the student table and only subject from registration where confirmed = 'Y' .

Thank you so much!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Question is not very clear so I have give 2 solutions below. Please inform if you need any variation.

DATA GENERATION AND TABLE CREATION
CREATE TABLE Students                             
(                                  
	 Name VARCHAR(10)    
	,StudentID INT                 
)
GO

INSERT INTO Students VALUES
('Jane'       ,1324),                       
('Joe'        ,1234),                
('Clare'      ,6784),                      
('James'      ,8973)   
GO                
          
CREATE TABLE Registrations                  
(
	 Name VARCHAR(10)    
	,StudentID INT      
	,RegistrationID BIGINT     
	,Registered VARCHAR(1) 
	,[Subject] VARCHAR(10)
)
GO
   
INSERT INTO Registrations VALUES   
('Jane',1324,3213215    , 'Y','Math'),
('Jane',1324,3213215    , 'N','Math'),
('Joe ',1234,54654646   , 'Y','English'),
('Joe ',1234,54654646   , 'N','English')
GO

Open in new window



QUESTION
I need all the students on the student table and only subject from registration where confirmed = 'Y' .

SOLUTION 1

SELECT * FROM Students s 
LEFT JOIN Registrations r ON r.StudentID = s.StudentID
AND r.Registered = 'Y'

Open in new window


OUTPUT
/*------------------------
OUTPUT
------------------------*/
Name       StudentID   Name       StudentID   RegistrationID       Registered Subject
---------- ----------- ---------- ----------- -------------------- ---------- ----------
Jane       1324        Jane       1324        3213215              Y          Math
Joe        1234        Joe        1234        54654646             Y          English
Clare      6784        NULL       NULL        NULL                 NULL       NULL
James      8973        NULL       NULL        NULL                 NULL       NULL

(4 row(s) affected)

Open in new window



OR

SOLUTION 2

SELECT * FROM Students s 
INNER JOIN Registrations r ON r.StudentID = s.StudentID
AND r.Registered = 'Y'

Open in new window


OUTPUT
/*------------------------
OUTPUT
------------------------*/
Name       StudentID   Name       StudentID   RegistrationID       Registered Subject
---------- ----------- ---------- ----------- -------------------- ---------- ----------
Jane       1324        Jane       1324        3213215              Y          Math
Joe        1234        Joe        1234        54654646             Y          English

(2 row(s) affected)

Open in new window

Coco BeansDesigner

Author

Commented:
Hi,  thanks for replying.

The Issue I have is that ALL the students are on the Student table and I only want to return the registration where is it confirmed.  Not All students have registrations and some have multiple registrations.

thanks
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
How do you check whether the registration has been confirmed for student ?
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

Coco BeansDesigner

Author

Commented:
and r.Registered = 'Y'

Thanks
Database Expert
Awarded 2016
Top Expert 2016
Commented:
Did u try this solution then ?

SELECT * FROM Students s 
INNER JOIN Registrations r ON r.StudentID = s.StudentID
AND r.Registered = 'Y'

Open in new window

Coco BeansDesigner

Author

Commented:
This only brings back students that have registrations.  Not all students have registrations.
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Can you tell me what you need as output from the input you have given?
Coco BeansDesigner

Author

Commented:
Got it - thanks

select ST.Student_ID,   FROM (
Select Student_ID
FROM REGISTRATIONS
Where REGISTERED  = 'Y'
) REG,
Students ST
WHERE ST= REG.Student_ID (+)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial