Re: Sortierung von inhalten in Left Joins
Vielleicht ist es einfacher zu verstehen wenn ich SQLs mit dazu packe (zum selbst ausprobieren):
CREATE TABLE IF NOT EXISTS `user` (
`id` int(11) NOT NULL,
`name` varchar(20) CHARACTER SET utf8 NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
INSERT INTO `user` (`id`, `name`) VALUES
(1, 'Max'),
(2, 'Paul');
---
CREATE TABLE IF NOT EXISTS `optionen_eins` (
`id` int(11) NOT NULL,
`name` varchar(20) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
INSERT INTO `optionen_eins` (`id`, `name`) VALUES
(1, 'optionX'),
(2, 'optionY');
---
CREATE TABLE IF NOT EXISTS `optionen_zwei` (
`id` int(11) NOT NULL,
`name` varchar(20) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
INSERT INTO `optionen_zwei` (`id`, `name`) VALUES
(1, 'zweiA'),
(2, 'zweiB');
---
CREATE TABLE IF NOT EXISTS `user_optionen` (
`id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`datum` date NOT NULL,
`option_id` int(11) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
INSERT INTO `user_optionen` (`id`, `user_id`, `datum`, `option_id`) VALUES
(1, 1, '2015-01-01', 1),
(2, 1, '2015-01-10', 2),
(3, 2, '2015-01-01', 1),
(4, 2, '2015-01-10', 2);
---
CREATE TABLE IF NOT EXISTS `user_options` (
`id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`datum` date NOT NULL,
`option_id` int(11) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
INSERT INTO `user_options` (`id`, `user_id`, `datum`, `option_id`) VALUES
(1, 1, '2015-01-01', 1),
(2, 1, '2015-01-10', 2),
(3, 2, '2015-01-01', 1),
(4, 2, '2015-01-10', 2);
---
Such-SQL:
SELECT u.*, o1.*, o2.*
FROM user AS u
LEFT JOIN (
SELECT o2.*, opt.name AS name
FROM user_optionen AS o2
LEFT JOIN optionen_zwei AS opt ON ( opt.id = o2.option_id )
WHERE o2.datum <= '2015-02-01'
ORDER BY datum DESC
) AS o2 ON ( u.id = o2.user_id )
LEFT JOIN (
SELECT o1.*, opt.name AS name
FROM user_options AS o1
LEFT JOIN optionen_eins AS opt ON ( opt.id = o1.option_id )
WHERE o1.datum <= '2015-02-01'
ORDER BY datum DESC
) AS o1 ON ( u.id = o1.user_id )
WHERE 1
GROUP BY u.id
Ergebnis:
id name id user_id datum Absteigend 1 option_id name id user_id datum Absteigend 1 option_id name
1 Max 2 1 2015-01-10 2 optionY 1 1 2015-01-01 1 zweiA
2 Paul 4 2 2015-01-10 2 optionY 3 2 2015-01-01 1 zweiA
Zielergebnis:
optionY und zweiB bei beiden