CODEWITHSUNDEEP
mysqljoinsubquery
Neokoenig
Neokoenig

Reputation: 1102

MySQL 3 table join with null values

I'm trying to work out: If I have three joined tables, i.e tableA<-->tableB<-->tableC

How to always have tableA's records and NULL values in B&C if they don't exist? See http://www.sqlfiddle.com/#!9/65dd5/6

The second example will naturally return nothing, and there's no matching IDs in either the second or third tables, but I still need to return the contents of TableA

==== EDIT =====

I don't think I've been very helpful in providing info. My bad!.

Here's the actual query:

SELECT
    cf_definitions.id,
    cf_definitions.`name`,
    cf_definitions.parentmodel,
    cf_definitions.type,
    cf_definitions.`options`,
    cf_definitions.class,
    cf_definitions.description,
    cf_joins.cf_definitionid,
    cf_joins.cf_childid,
    cf_joins.cf_valueid,
    cf_values.id,
    cf_values.`value`
FROM
    cf_definitions
LEFT JOIN cf_joins ON cf_joins.cf_definitionid = cf_definitions.id
LEFT JOIN cf_values ON cf_joins.cf_valueid = cf_values.id
WHERE
    cf_definitions.parentmodel = 'location' AND cf_joins.cf_childid = 1

So if I've got records with a join entry, then it's fine.

However, what I really need is (in pseudo code) IF cf_joins.cf_childid doesn't exist, still return the records from cf_definitions.

(to put this in context, this is for custom form fields, where I'm essentially definining the form schema in definitions, then 'if' the page has values (which is the childid), then return the rows complete with values, otherwise return null values).

I appreciate I might be going against what a JOIN is actually meant to do?

Upvotes: 0

Views: 79

Answers (4)

Michael Y.
Michael Y.

Reputation: 661

Put the condition on the JOIN, not the WHERE:

SELECT
    cf_definitions.id,
    cf_definitions.`name`,
    cf_definitions.parentmodel,
    cf_definitions.type,
    cf_definitions.`options`,
    cf_definitions.class,
    cf_definitions.description,
    cf_joins.cf_definitionid,
    cf_joins.cf_childid,
    cf_joins.cf_valueid,
    cf_values.id,
    cf_values.`value`
FROM
    cf_definitions
LEFT JOIN cf_joins ON cf_joins.cf_definitionid = cf_definitions.id AND cf_joins.cf_childid = 1 
LEFT JOIN cf_values ON cf_joins.cf_valueid = cf_values.id
WHERE
    cf_definitions.parentmodel = 'location'

Upvotes: 1

Raging Bull
Raging Bull

Reputation: 18737

Try this:

SELECT T1.*,
T2.*
FROM Table1 T1
LEFT JOIN
(SELECT table1.id AS FormID,
 table1.`name` AS FormName,
 table1.model AS FormModel,
 table2.anotherid,
 table2.table1id,
 table2.table3id,
 table3.id AS FormValueID, 
 table3.`name` AS FormValue
 FROM table1
 INNER JOIN table2 ON table2.table1id = table1.id
 INNER JOIN table3 ON table2.table3id = table3.id
 WHERE table1.model = 'example1' AND anotherid = 1) T2 ON T2.FormID=T1.id

Result:

ID  NAME        MODEL       FORMID  FORMNAME    FORMMODEL   ANOTHERID   TABLE1ID    TABLE3ID    FORMVALUEID FORMVALUE
1   Thing One   example1    1       Thing One   example1    1           1           1           1           Value One
2   Thing Two   example1    2       Thing Two   example1    1           2           2           2           Value Two
3   Thing Three example1    3       Thing Three example1    1           3           3           3           Value Three
4   Thing Four  example1    4       Thing Four  example1    1           4           4           4           Value Four
5   Thing Five  example2    (null)  (null)      (null)      (null)      (null)      (null)      (null)      (null)
6   Thing Six   example2    (null)  (null)      (null)      (null)      (null)      (null)      (null)      (null)
7   Thing Seven example3    (null)  (null)      (null)      (null)      (null)      (null)      (null)      (null)
8   Thing Eight example3    (null)  (null)      (null)      (null)      (null)      (null)      (null)      (null)

See result in Sql Fiddle

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269633

You want a left join:

select . . .
FROM table1 t1 LEFT JOIn
     table2 t2
     ON t2.table1id = t1.id AND t2.anotherid = 2 LEFT JOIN
     table3 t3
     ON t2.table3id = t3.id
WHERE t1.model = 'example1'

One subtlety is splitting the WHERE condition. The condition on table1 (the first table) stays in the WHERE clause. The condition on table2 needs to go into the on clause. Otherwise, it will turn the outer joins into inner joins.

Upvotes: 0

h7r
h7r

Reputation: 5074

You need a left join from the values of tableA. From the query you linked to, you would have: 

SELECT
    table1.id AS FormID,
    table1.`name` AS FormName,
    table1.model AS FormModel,
    table2.anotherid,
    table2.table1id,
    table2.table3id,
    table3.id AS FormValueID, 
    table3.`name` AS FormValue
FROM table1
    LEFT JOIN table2 ON table2.table1id = table1.id
    LEFT JOIN table3 ON table2.table3id = table3.id
WHERE table1.model = 'example1' AND anotherid = 2;

Note that if table2 has null values in relation to table3, they will be shown as well.

Upvotes: 0

Related Questions