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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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

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
Pawan KumarDatabase ExpertCommented:
How do you check whether the registration has been confirmed for student ?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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

Thanks
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

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.
Pawan KumarDatabase ExpertCommented:
Can you tell me what you need as output from the input you have given?
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 (+)
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.