0

Every table has a playerNumber. I want to get all columns from all tables in a single result. I.e. for example for a daily row all columns from the other tables are 0. Like this:

enter image description here

Tables:

CREATE TABLE `daily` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `playerNumber` varchar(255) DEFAULT '',
 `benchpress` varchar(255) DEFAULT '');

CREATE TABLE `weekly` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `playerNumber` varchar(255) DEFAULT '',
 `sprint40y` varchar(255) DEFAULT '',
 `sprint20y` varchar(255) DEFAULT '');

CREATE TABLE `monthly` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `playerNumber` varchar(255) DEFAULT '',
 `verticalJump` varchar(255) DEFAULT '',
 `sprint300y` varchar(255) DEFAULT '',
 `ldrill` varchar(255) DEFAULT '');

SQL Query: 

SELECT *
FROM
  daily d,weekly w,monthly m
LEFT JOIN
  daily d.playerNumber ON (d.playerNumber = w.playerNumber) AND  daily d.playerNumber ON (d.playerNumber = m.playerNumber)
UNION
  SELECT *
FROM
  daily d,weekly w,monthly m
RIGHT JOIN
  daily d.playerNumber ON (d.playerNumber = w.playerNumber) AND  daily d.playerNumber ON (d.playerNumber = m.playerNumber)
Kewitschka
  • 1,445
  • 1
  • 21
  • 35
  • It's generally best to handle issues of data display in the presentation layer/application-level code (e.g. PHP), if that's available. Either way, this question has been answered a thousand times already. – Strawberry Nov 25 '15 at 09:41
  • ...although, I can't imagine why you would want 3 separate rows in this instance ?!?!? – Strawberry Nov 25 '15 at 09:51
  • Because in the daily table are a lot of entries for each player and only a few in the monthly table. – Kewitschka Nov 25 '15 at 09:56
  • It seems like a daft schema design to me - but hey, whatever floats your boat! – Strawberry Nov 25 '15 at 09:57

1 Answers1

0
SELECT `playerNumber`,
        `benchpress`,
        IFNULL(`sprint40y`,0) AS benchpress,
        IFNULL(`sprint20y`,0) AS sprint20y,
        IFNULL(`verticalJump`,0) AS verticalJump,
        IFNULL(`sprint300y`,0) AS sprint300y,
        IFNULL(`ldrill`,0) AS ldrill

FROM daily 
LEFT JOIN weekly ON daily.`playerNumber`=weekly.`playerNumber`
LEFT JOIN monthly ON daily.`playerNumber`=monthly.`playerNumber`

Hope this helps.

Subin Chalil
  • 3,531
  • 2
  • 24
  • 38