I have the following query:
SELECT
dashboard_data.headline,
dashboard_data.message,
dashboard_messages.image_id
FROM dashboard_data
INNER JOIN dashboard_messages
ON dashboard_message_id = dashboard_messages.id
So I am using an INNER JOIN and grabbing the image_id. So now, I want to take that image_id and turn it into images.filename from the images table.
How can I add that in to my query?
You can simply add another join like this:
SELECT dashboard_data.headline, dashboard_data.message, dashboard_messages.image_id, images.filename
FROM dashboard_data
INNER JOIN dashboard_messages
ON dashboard_message_id = dashboard_messages.id
INNER JOIN images
ON dashboard_messages.image_id = images.image_id
However be aware that, because it is an INNER JOIN, if you have a message without an image, the entire row will be skipped. If this is a possibility, you may want to do a LEFT OUTER JOIN which will return all your dashboard messages and an image_filename only if one exists (otherwise you'll get a null)
SELECT dashboard_data.headline, dashboard_data.message, dashboard_messages.image_id, images.filename
FROM dashboard_data
INNER JOIN dashboard_messages
ON dashboard_message_id = dashboard_messages.id
LEFT OUTER JOIN images
ON dashboard_messages.image_id = images.image_id
join operations always commute: A~B~C is the same as (A~B)~C, which is the same as A~(B~C). This is because any join A~B is a Cartesian product of rows filtered by an appropriate condition. A~B~C is a Cartesian product of three relations, filtered by the conjunction of the two condition. The last statement is intuitively clear, but must be proved by showing that filtering for A~B can be postponed until after the second Cartesian product.Just add another join:
SELECT dashboard_data.headline,
dashboard_data.message,
dashboard_messages.image_id,
images.filename
FROM dashboard_data
INNER JOIN dashboard_messages
ON dashboard_message_id = dashboard_messages.id
INNER JOIN images
ON dashboard_messages.image_id = images.image_id
I shared my experience of using two LEFT JOINS in a single SQL query.
I have 3 tables:
Table 1) Patient consists columns PatientID, PatientName
Table 2) Appointment consists columns AppointmentID, AppointmentDateTime, PatientID, DoctorID
Table 3) Doctor consists columns DoctorID, DoctorName
Query:
SELECT Patient.patientname, AppointmentDateTime, Doctor.doctorname
FROM Appointment
LEFT JOIN Doctor ON Appointment.doctorid = Doctor.doctorId //have doctorId column common
LEFT JOIN Patient ON Appointment.PatientId = Patient.PatientId //have patientid column common
WHERE Doctor.Doctorname LIKE 'varun%' // setting doctor name by using LIKE
AND Appointment.AppointmentDateTime BETWEEN '1/16/2001' AND '9/9/2014' //comparison b/w dates
ORDER BY AppointmentDateTime ASC; // getting data as ascending order
I wrote the solution to get date format like "mm/dd/yy" (under my name "VARUN TEJ REDDY")
Multi joins in SQL work by progressively creating derived tables one after the other. See this link explaining the process:
