CODEWITHSUNDEEP
sqlleft-join
rfpdl
rfpdl

Reputation: 964

sql left join possible no relationship

I have to generate a report from 4 tables in the database

leads
    id
    name
    country
    create_by       #different from lead_call_logs create_by

lead_call_logs
    id
    lead_id
    create_by
    create_datetime

users
    id
    employee_id
    username
    password

employees
    id
    name

I am using this query

SELECT
    leads.id AS id,
    CONCAT(leads.first_name,' ',leads.surname) AS lead_name,
    leads.country AS country,
    CONCAT(employees.first_name,' ',employees.surname) AS booked_by,
    lead_call_logs.create_datetime
FROM
    leads
        LEFT JOIN lead_call_logs ON leads.id = lead_call_logs.lead_id,
    users,
    employees
WHERE
    lead_call_logs.create_by = users.id AND
    users.employee_id = employees.id

The problem comes when the leads still does not have any lead_call_logs. How can I get all the leads with or without any lead_call_logs and get the employee name who booked it?

Thanks in advance.

UPDATE Question

What if there is a create_by in leads as well where the create_by in leads <> create_by in lead_call_logs (different people is doing it)

Upvotes: 0

Views: 96

Answers (1)

Amit
Amit

Reputation: 46341

Why did you "stop" after the first LEFT JOIN? Do the same with the other tables:

SELECT
    leads.id AS id,
    CONCAT(leads.first_name,' ',leads.surname) AS lead_name,
    leads.country AS country,
    CONCAT(employees.first_name,' ',employees.surname) AS booked_by,
    lead_call_logs.create_datetime
FROM
    leads
        LEFT JOIN lead_call_logs ON leads.id = lead_call_logs.lead_id
        LEFT JOIN users ON lead_call_logs.create_by = users.id
        LEFT JOIN employees ON users.employee_id = employees.id

Upvotes: 1

Related Questions