CODEWITHSUNDEEP
sqloracle-database
Uhujkill
Uhujkill

Reputation: 89

SQL: Join Statements

I am trying to produce an output where only the person with Surname = "Berry" lessons are shown, with Date in Ascending order. 

SELECT c.booking_number AS "Reference",
       c.ldate          AS "Lesson Date",
       s.first_name     AS "Instructor First Name",
       s.surname        AS "Instructor Last Name"
FROM   am_lessons c
       join am_staff s
         ON ( c.staff_id = c.staff_id )
       join am_bookings m
         ON ( c.booking_number = m.booking_number )
       join am_customers f
         ON ( f.customer_id = c.customer_id )
WHERE  f.surname = 'Berry'
ORDER  BY c.ldate ASC

The Statement runs but produces repetitions of "Booking Numbers" and "First Name" and "Second Name".

Upvotes: 0

Views: 47

Answers (1)

PinnyM
PinnyM

Reputation: 35533

Firstly, you have some extra/incorrect joins:

SELECT c.booking_number AS "Reference",
       c.ldate          AS "Lesson Date",
       s.first_name     AS "Instructor First Name",
       s.surname        AS "Instructor Last Name"
FROM   am_lessons c
       join am_staff s
         ON ( c.staff_id = s.staff_id ) -- should be s.Staff_Id
       -- JOIN AM_Bookings m           -- this join is not needed
       -- ON (c.Booking_Number = m.Booking_Number)
       join am_customers f
         ON ( f.customer_id = c.customer_id )
WHERE  f.surname = 'Berry'
ORDER  BY c.ldate ASC

Secondly, you may have duplicate rows in your tables. If this is the case, either fix the data, or add a DISTINCT modifier.

Note: It is possible that the join on AM_Bookings was being used to filter out records that have no associated Booking_Number. If this is the case, you can simply filter on c.Booking_Number IS NOT NULL.

Upvotes: 1

Related Questions