php mysql joining 4 tables with possible null values

Question

I have the following sql which queries a database and pulls out the info from 4 different tables.

 SELECT d.item AS day, m.item as month, y.item as yr, l.item as local
 FROM date_day d
 JOIN date_month m ON d.recid=m.recid
 JOIN date_year y ON d.recid=y.recid
 JOIN location l ON d.recid=l.recid
 WHERE d.recid='cneta0ld00s6'

Its possible that one or more of those tables might empty and not contain a value. Particularly on the date fields. Will any of the above being empty or not existing, cause the whole thing to fail? Im particularly worried about date_day as I know mysql doesnt have a FUll JOIN.

Any thoughts?

is location the one table that will always have a record and then the date tables might be empty? — Taryn
– Taryn, Commented Sep 12, 2012 at 11:52
Unfortunately each one could be empty or missing. Its data on historic photos, so the day,month, year or location might not have been known by the person filling in the data. Im unfortunately stuck with a very poor database structure. Im wondering if its easier to do 4 seperate selects and load the results into an array! — Paul M
– Paul M, Commented Sep 12, 2012 at 12:06

James C · Accepted Answer · 2012-09-12 11:31:34Z

1

I think that:

SELECT d.item AS day, m.item as month, y.item as yr, l.item as local
 FROM date_day d
 LEFT JOIN date_month m ON d.recid=m.recid
 LEFT JOIN date_year y ON d.recid=y.recid
 LEFT JOIN location l ON d.recid=l.recid
 WHERE d.recid='cneta0ld00s6'

will do what you want it to

answered Sep 12, 2012 at 11:31

James C

14.2k1 gold badge37 silver badges45 bronze badges

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

1 Comment

Paul M Over a year ago

Im pretty sure though, that that expects the date_day table to include a column with the recid, like my single join does.

Paul M · Accepted Answer · 2012-09-12 14:38:37Z

1

To make sure I always got the data from the table even if its null, I added in a different table as the main table, one which will always include the recid, the table is photo. That way I ensure that the query will always return something even if its lots of nulls. THis meant I could use a left join too.

 SELECT p.photo_file, d.item AS day, m.item as month, y.item as yr, l.item as local
  FROM photo p
 LEFT JOIN date_day d ON p.recid=d.recid
 LEFT JOIN date_month m ON p.recid=m.recid
 LEFT JOIN date_year y ON p.recid=y.recid
 LEFT JOIN location l ON p.recid=l.recid
 WHERE p.recid='paul'

answered Sep 12, 2012 at 14:38

Paul M

4,19510 gold badges48 silver badges54 bronze badges

Collectives™ on Stack Overflow

php mysql joining 4 tables with possible null values

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related