Link to home
Create AccountLog in
Avatar of Lmillard
Lmillard

asked on

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

ASKER CERTIFIED SOLUTION
Avatar of enachemc
enachemc
Flag of Afghanistan image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Pratima
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
if cid is null it will return null for customer details
Avatar of Lmillard
Lmillard

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

Open in new window

SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
perfect, thanks very much