MySQL Forums
Forum List  »  German

Re: Sortierung von inhalten in Left Joins
Posted by: Vorname Zuname
Date: February 18, 2015 10:47AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
1081
February 18, 2015 08:05AM
Re: Sortierung von inhalten in Left Joins
651
February 18, 2015 10:47AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.