# Join with more rows

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® is a registered trademark of EXPERTS EXCHANGE®
Database 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
``````

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'
``````

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)
``````

OR

SOLUTION 2

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

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)
``````
Designer

Commented:

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
Database Expert
Awarded 2016
Top Expert 2016

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

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'
``````
Designer

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

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

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