I am using mysql database. I have two tables bugs and profiles. Bugs table has two columns (assigned_to, qa_contact) pointing to profiles by a many to one relationship. These are the simplified version of my queries.
Firstly, I was trying to do this but it returns duplicate rows where qa_contact is null in the bugs table
select
bug_id,
desc,
dev.assigned_to,
qa.qa_contact
from
bugs,
profiles as dev,
profiles as qa
where
assigned_to = dev.userid
and (qa_contact = qa.userid or qa_contact is null)
Secondly, my new approach is:
select bug_id, desc, dev.assigned_to, qa.qa_contact
from
bugs,
profiles as dev,
profiles as qa
where
assigned_to = dev.userid
and qa_contact = qa.userid
UNION
select bug_id, desc, dev.assigned_to, null
from
bugs,
profiles as dev,
profiles as qa
where
assigned_to = dev.userid
and qa_contact is null
But in the second approach it excludes the result where qa_contact is null. Can anyone suggest a efficient way of doing this because I am dealing with records in order of millions and would like to add more filters on the resultset.
This is what LEFT JOINs are for:
SELECT bug_id, `desc`, dev.assigned_to, qa.qa_contact
FROM bugs
INNER JOIN profiles as dev ON bugs.assigned_to = dev.userid
LEFT OUTER JOIN profiles as qa ON bugs.qa_contact = qa.userid
I think you want to use a LEFT JOIN
:
select bug_id, desc, dev.assigned_to, qa.qa_contact
from bugs b
left join profiles dev
b.assigned_to dev.userid
left join profiles qa
on b.qa_contact = qa.userid
If you need help learning JOIN
syntax, then here is a great visual explanation of joins
A LEFT JOIN
will return data from the bugs
table even if the id does not exist in the profiles
table.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With