MySQL 3 table join with null values

Question

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?

You should ideally provide all the details needed for an answer in the question itself, avoiding offsite references. — h7r
– h7r, Commented Jan 25, 2015 at 17:11

Michael Y. · Accepted Answer · 2015-01-25 18:20:59Z

1

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'

answered Jan 25, 2015 at 18:20

Michael Y.

6617 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

h7r · Accepted Answer · 2015-01-25 17:14:28Z

0

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.

answered Jan 25, 2015 at 17:14

h7r

5,0732 gold badges30 silver badges31 bronze badges

1 Comment

Neokoenig Over a year ago

I revised my question, so this wasn't quite what I was after - but thank you for taking the time to comment :)

Raging Bull · Accepted Answer · 2015-01-25 17:19:11Z

0

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.

edited Jan 25, 2015 at 17:19

Raging Bull

18.8k13 gold badges52 silver badges56 bronze badges

answered Jan 25, 2015 at 17:15

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

Raging Bull · Accepted Answer · 2015-01-25 17:55:58Z

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

I revised my question, so this was very close to what I was after - but thank you for taking the time to comment :)

Collectives™ on Stack Overflow

MySQL 3 table join with null values

4 Answers 4

Comments

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related