CODEWITHSUNDEEP
mysqlsqljoinleft-joinouter-join
RajveerParikh
RajveerParikh

Reputation: 111

Whats wrong with this MySQL query - Left Outer Join

I have three tables in my data base, lets call them: 1. value_type 2. values 3. orgs

Each value has a foreign key reference to a value_type and an org. Further, each value type also has a default value.

I am trying to do a left outer join for all orgs such that if a record exists in the value table, it will return the values from that record and if the record does not exist, it will take the default value from the value_type table. However, when I run the following query, it returns nulls for all orgs where the records don't already exist in the value table.

SELECT 
    o.id as org_id,
    o.name as org_name,
    case when vals.choice is null then vals.default_value else vals.choice END as select_choice,
    vals.choices as choices
FROM orgs o
LEFT OUTER JOIN
    (SELECT 
        v.id as id,
        v.choice as choice,
        v.org_id as org_id,
        v.name as val_name,
        vt.default_value as default_value,
        vt.choices as choices
    FROM value v
    INNER JOIN value_type vt ON v.value_type = vt.id
    WHERE vt.name = 'xyz') vals
ON vals.org_id = o.id

Upvotes: 1

Views: 69

Answers (2)

xQbert
xQbert

Reputation: 35323

Seems odd but perhaps you need to cross join orgs to value_Type then left join to value?

This way ever record from org will list all the value types for a given name; along with the default and then IF a record exists in value, we join specifically to it as well. coalesce will then pick the first non-null between vals.choice if it exists and the cross join vt.default_value which we know will exist.

SELECT 
    o.id as org_id,
    o.name as org_name,
    coalesce(vals.choice, VT.Default_Value)
    VT.choices as choices
FROM orgs o
CROSS JOIN (SELECT id, choices, default_value FROM Value_Type where Name = 'xyz') VT
LEFT OUTER JOIN
    (SELECT 
        v.id as id,
        v.choice as choice,
        v.org_id as org_id,
        v.name as val_name,
        v.value_Type
    FROM value v) vals
  ON vals.org_id = o.id
 AND vals.value_type = vt.id

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269753

I would expect something like this:

SELECT o.id as org_id,o.name as org_name,
       COALESCE(v.choice, vt.default_value) as select_choice,
       vt.choices as choices
FROM orgs o LEFT JOIN
     value v
     ON v.org_id = o.id LEFT JOIN
     value_types vt
     ON vt.id = v.value_type AND vt.name = 'xyz' ;

However, this will still return NULL when there is no matching record between orgs and value. You don't have a connection between value_types and orgs. It has to go through value, so a match between orgs and value is needed.

Upvotes: 0

Related Questions