MySQL inner join only if record exists

Hi,

I have a query which is selecting tasks and inner joining to a customer table. I have now amended the application so that you can create a task without it being allocated to a customer. At the moment the join query will only return the record if the tasks.cid field is populated.
Can anyone suggest the best way to allow it to retrieve all tasks even if the cid field is null and if it is populated retrieve the customer details?

Regards
Leigh

SELECT 
        Tasks.TaskId, 
        Tasks.cId, 
        Tasks.TaskDet, 
        Tasks.startDate,
        Tasks.UsrId, 
        WEEK(startDate) AS thisWeek,
        Customer.Company 
        FROM tasks 
        INNER JOIN Customer 
        ON tasks.cid = Customer.cid

Open in new window

DatabasesMySQL ServerSQL

Last Comment

Lmillard

8/22/2022 - Mon

ASKER CERTIFIED SOLUTION

enachemc

5/18/2011

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

Pratima

5/18/2011

use Left join

SELECT
Tasks.TaskId,
Tasks.cId,
Tasks.TaskDet,
Tasks.startDate,
Tasks.UsrId,
WEEK(startDate) AS thisWeek,
Customer.Company
FROM tasks
Left JOIN Customer
ON tasks.cid = Customer.cid

if cid is null it will retun numm for customer details

Pratima

5/18/2011

if cid is null it will return null for customer details

Lmillard

5/18/2011

ASKER

Thanks, left join was working and then i added some additional joins and it stopped retrieving the nulls again

SELECT 
        Tasks.TaskId, 
        Tasks.cId, 
        Tasks.TaskDet, 
        Tasks.startDate,
        Tasks.endDate, 
        Tasks.DateCreated, 
        Tasks.DateCompleted, 
        Tasks.TaskStatus, 
        Tasks.TaskType, 
        Tasks.UsrId, 
        WEEK(startDate) AS thisWeek,
        Customer.Company,
        Customer.Deleted, 
        TaskTypes.TypeDesc, 
        TaskStatus.StatusDesc,
        usr.usrName,
        usr.usrColour 
        FROM tasks 
        LEFT JOIN Customer 
        ON tasks.cid = Customer.cid 
        inner JOIN TaskTypes 
        ON Tasks.TaskType = TaskTypes.TypeId 
        inner JOIN TaskStatus 
	ON Tasks.TaskStatus = TaskStatus.StatusId 
        inner JOIN usr 
        ON tasks.usrId = usr.usrId