CODEWITHSUNDEEP
mysqljoinleft-join
Lorenzo Gasperoni
Lorenzo Gasperoni

Reputation: 57

Single query with multiple independent left joins

I have two tables. One for elements (leads) and one for that elements properties (leads_properties).

Leads table columns, useful to solve this issue, are: id, token. Properties table is something like this:

id      label      value
1       phone      555333666
3       phone      111222555
3       city       Milan

I am trying to extract with a single query a lead and all the requested properties. If i ask for city, i will retrieve all leads info joined wit city property only, if i ask for city and phone i will retrieve even phone.

The query i am trying is:

SELECT leads.* ,
p1.value as 'phone' ,
p2.value as 'city'
FROM leads
LEFT JOIN leads_properties as p1 ON leads.id = p1.lead_id 
LEFT JOIN leads_properties as p2 ON leads.id = p2.lead_id 
WHERE leads.id = 1 
AND p1.label = 'phone' 
AND p2.label = 'city' 
AND leads.token = 'xxxxx'
LIMIT 1

This is working great if all the requested proerties are present in DB (in this example for the lead id=3) but fails and return 0 lines for lead id=1 that didn't had "city" property.

I understand why (the AND clause in WHERE statement probably is restrictive) but i don't understand how to solve the issue.

I would like to retrieve a single line like this (if all the requested properties are in DB)

id   token        city      phone
3    xxxxx        Milan     111222555

Or like this (with empty or NULL value) if some of the requested properties are not in DB:

id   token        city      phone
1    xxxxx                  111222555

Thanks so much.

Upvotes: 0

Views: 52

Answers (2)

Ale
Ale

Reputation: 997

It is not AND being restrictive, but rather asking p2.label = 'city'. If you want any property you probably can just ask to avoid duplication. For example:

    SELECT leads.* ,
    p1.value as 'phone' ,
    p2.value as 'city'
    FROM leads
    LEFT JOIN leads_properties as p1 ON leads.id = p1.lead_id 
    LEFT JOIN leads_properties as p2 ON leads.id = p2.lead_id 
    WHERE leads.id = 1 
    AND p1.label > p2.label
    AND leads.token = 'xxxxx'
    LIMIT 1

Upvotes: 0

Fahmi
Fahmi

Reputation: 37473

You can use conditional aggregation using case when expression

SELECT leads.id,max(case when p1.label = 'city' then p1.value end) as city
max(case when p1.label = 'phone' then p1.value end) as phone
FROM leads
LEFT JOIN leads_properties as p1 ON leads.id = p1.lead_id
WHERE leads.id = 1 
group by leads.id

Upvotes: 1

Related Questions