Join with more rows

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!
Coco BeansDesignerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
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

0
Coco BeansDesignerAuthor 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
0
Pawan KumarDatabase ExpertCommented:
How do you check whether the registration has been confirmed for student ?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Coco BeansDesignerAuthor Commented:
and r.Registered = 'Y'

Thanks
0
Pawan KumarDatabase ExpertCommented:
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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Coco BeansDesignerAuthor Commented:
This only brings back students that have registrations.  Not all students have registrations.
0
Pawan KumarDatabase ExpertCommented:
Can you tell me what you need as output from the input you have given?
0
Coco BeansDesignerAuthor 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 (+)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.