I need some help with this query. I have 2 tables of data

Booking table

Student table |studentid|firstname|lastname| |--|--|--| |12345678|john|doe| |12345679|mary|doe| |12345670|vincent|doe|

What table im looking for

Using this query,

Select Booking_date, 
       Booking_start, 
       case WHEN booking.StudentID is NULL THEN NULL ELSE student.First_name end as First_name, 
       case WHEN booking.StudentID is NULL THEN NULL ELSE student.Last_name end as Last_name, 
       BookingID 
from booking, student 
where (booking.staffid = '$userid') ORDER BY booking_start ASC)

This is the table i am getting

There should be 1 more 2021-10-10|12.00pm|| im unable to show it due to formatting issues. It shows duplicated listing with the wrong student name. What can I do to fix this query?

CodePudding user response：

You need to join tables like this :

booking inner join student on booking.StudentID = student.StudentID

    Select Booking_date,
       Booking_start,
       student.First_name,      
       student.Last_name,
       BookingID
  from booking inner join student on booking.StudentID = student.StudentID
 where (booking.staffid = '$userid')
 ORDER BY booking_start ASC

if you dont join tables, they act like cartesian joins, all rows are matched with all others, so it generates duplicate . for example , if you have 2 table with 10 rows each, in result you get 10*10 result ...

Inner join = you get only if records are matched.

If you have records on booking table and it has no studing_id matched with students table , and still want to display it as a result, you need to use LEFT JOIN instead of INNER JOIN

Also its the best to use join syntax instead of old joins like :

old syntax :

student,booking 
where 
student.id = booking.studentid 

new syntax:

student inner join booking on student.id = booking.studentid