MySQL JOIN und ORDER über mehrere Tabellen

TDS · 13 Mai 2011

Hallo,

Ich habe ein Problem

Ich hab vor 3 Tabellen mittels JOIN zu verbinden und die Daten nach einem Datumsfeld zu sortieren.
Sub-Selects, Unions und Joins hab ich probiert - ohne Erfolg.

portal_gm_provide ist die Tabelle wo die globale wms_id hinterlegt ist.
portal_gm_log ist die Tabelle wo alle Logs (info, warning, error) reingeschrieben werden.
portal_gm_cron ist die Tabelle wo alle Cronjob Informationen hinterlegt sind (success or error).

Das Problem ist, das diese 3 Tabellen nicht die selbe Struktur geschweige denn das selbe Datumsfeld haben.
Kann ich denn überhaupt die Tabellen verbinden und sortieren so das jede Zeile die unnötigen Felder mit NULL wie bei einem LEFT/RIGHT Join füllt?

Bitte um Hilfe, Danke.

==================================================

PHP:

 SELECT 
db_main.wms_id AS id_wms,
db_log.message, db_log.message_real, db_log.id_layer,
db_log.code, db_log.subcode, db_log.counter, db_log.time_first, db_log.time_last,
db_cron.error, db_cron.errorno,
CASE
    WHEN db_log.time_first IS NULL THEN db_cron.time 
    ELSE NULL # db_log.time_first
END AS time
FROM portal_gm_provide AS db_main
RIGHT JOIN portal_gm_log AS db_log ON db_log.id_wms = db_main.wms_id
RIGHT JOIN portal_gm_cron AS db_cron ON db_cron.id_wms = db_main.wms_id
WHERE db_main.user_id = 1
ORDER BY time DESC
LIMIT 50

==================================================

PHP:

CREATE TABLE IF NOT EXISTS `portal_gm_provide` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL DEFAULT '0',
  `wms_id` int(10) unsigned NOT NULL DEFAULT '0',
  `mail_notify` varchar(100) DEFAULT NULL,
  `mail_period` varchar(100) DEFAULT NULL,
  `time` datetime NOT NULL,
  `time_cron` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `portal_gm_log` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `code` int(10) unsigned NOT NULL DEFAULT '0',
  `subcode` int(10) unsigned DEFAULT '0',
  `id_wms` int(10) unsigned DEFAULT '0',
  `id_layer` int(10) unsigned DEFAULT '0',
  `message` mediumtext,
  `message_real` mediumtext,
  `counter` int(10) unsigned NOT NULL DEFAULT '1',
  `time_first` datetime NOT NULL,
  `time_last` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id_wms` (`id_wms`),
  KEY `id_layer` (`id_layer`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `portal_gm_cron` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `id_wms` int(10) unsigned DEFAULT '0',
  `error` varchar(250) DEFAULT NULL,
  `errorno` int(10) unsigned DEFAULT '0',
  `flag` int(10) unsigned DEFAULT '0',
  `time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `GM_ID` (`id_wms`),
  KEY `DATUM` (`time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `portal_gm_provide` (`id`, `user_id`, `wms_id`, `mail_notify`, `mail_period`, `time`, `time_cron`) VALUES
    (1, 1, 2, 'MOD_GEOMONITOR_PROVIDE_NOTIFY_STATE_WARNINGERROR', 'MOD_GEOMONITOR_PROVIDE_NOTIFY_PERIOD_3H', '2011-04-21 10:35:36', '2011-05-13 10:10:02'),
    (2, 1, 1389, 'MOD_GEOMONITOR_PROVIDE_NOTIFY_STATE_WARNINGERRORIN', 'MOD_GEOMONITOR_PROVIDE_NOTIFY_PERIOD_IMMEDIATELY', '2011-05-02 13:42:02', '2011-05-13 11:34:01'),
    (3, 1, 415, 'MOD_GEOMONITOR_PROVIDE_NOTIFY_STATE_WARNINGERRORIN', 'MOD_GEOMONITOR_PROVIDE_NOTIFY_PERIOD_IMMEDIATELY', '2011-05-02 13:42:02', '2011-05-13 11:53:01');

INSERT INTO `portal_gm_log` (`id`, `code`, `subcode`, `id_wms`, `id_layer`, `message`, `message_real`, `counter`, `time_first`, `time_last`) VALUES (578, 8192, 0, 415, 5975, 'Valid SRS not found. Maybe no matching BoundingBox. Defaulting to EPSG:4326.', NULL, 90, '2010-10-21 20:19:42', '2010-10-27 13:42:52');
INSERT INTO `portal_gm_log` (`id`, `code`, `subcode`, `id_wms`, `id_layer`, `message`, `message_real`, `counter`, `time_first`, `time_last`) VALUES (3984, 8192, 0, 415, 5975, 'No valid Box for given SRS not found. Maybe no matching BoundingBox. Defaulting to EPSG:4326.', NULL, 483, '2010-10-27 15:16:05', '2010-12-02 08:18:19');
INSERT INTO `portal_gm_log` (`id`, `code`, `subcode`, `id_wms`, `id_layer`, `message`, `message_real`, `counter`, `time_first`, `time_last`) VALUES (7800, 4352, 0, 415, 0, 'www.umweltkarten.mv-regierung.de', 'every name server provided has failed: Success', 3, '2010-11-26 00:37:05', '2010-11-29 21:34:27');
INSERT INTO `portal_gm_log` (`id`, `code`, `subcode`, `id_wms`, `id_layer`, `message`, `message_real`, `counter`, `time_first`, `time_last`) VALUES (14471, 12288, 0, 1389, 19219, '', NULL, 95, '2011-05-02 14:24:01', '2011-05-13 10:40:51');

INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2530349, 415, 'Error loading XML file. (Not Found, Code: 404)', 256, 5, '2011-05-06 00:00:27');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2531615, 415, 'Error loading XML file. (Not Found, Code: 404)', 256, 5, '2011-05-06 02:48:49');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2532882, 415, 'Error loading XML file. (Not Found, Code: 404)', 256, 5, '2011-05-06 05:36:05');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2534150, 415, 'Error loading XML file. (Not Found, Code: 404)', 256, 5, '2011-05-06 08:16:10');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2535418, 415, 'Error loading XML file. (Not Found, Code: 404)', 256, 5, '2011-05-06 11:04:05');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2536684, 415, 'Error loading XML file. (Not Found, Code: 404)', 256, 5, '2011-05-06 13:56:07');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2537951, 415, 'Error loading XML file. (Not Found, Code: 404)', 256, 5, '2011-05-06 16:44:20');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2539219, 415, 'Error loading XML file. (Not Found, Code: 404)', 256, 5, '2011-05-06 19:24:07');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2540489, 415, 'Error loading XML file. (Not Found, Code: 404)', 256, 5, '2011-05-06 21:56:04');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2541758, 415, 'Error loading XML file. (Not Found, Code: 404)', 256, 5, '2011-05-07 00:40:04');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2543027, 415, 'Error loading XML file. (Not Found, Code: 404)', 256, 5, '2011-05-07 03:24:01');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2544298, 415, 'Error loading XML file. (Not Found, Code: 404)', 256, 5, '2011-05-07 06:00:07');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2545566, 415, 'Error loading XML file. (Not Found, Code: 404)', 256, 5, '2011-05-07 08:48:05');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2546836, 415, 'Error loading XML file. (Not Found, Code: 404)', 256, 5, '2011-05-07 11:24:19');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2548106, 415, 'Error loading XML file. (Not Found, Code: 404)', 256, 5, '2011-05-07 14:00:09');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2549374, 415, 'Error loading XML file. (Not Found, Code: 404)', 256, 5, '2011-05-07 16:44:04');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2550643, 415, 'Error loading XML file. (Not Found, Code: 404)', 256, 5, '2011-05-07 19:24:01');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2551912, 415, 'Error loading XML file. (Not Found, Code: 404)', 256, 5, '2011-05-07 22:04:02');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2553181, 415, 'Error loading XML file. (Not Found, Code: 404)', 256, 5, '2011-05-08 00:44:04');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2554451, 415, 'Error loading XML file. (Not Found, Code: 404)', 256, 5, '2011-05-08 03:20:10');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2555719, 415, 'Error loading XML file. (Not Found, Code: 404)', 256, 5, '2011-05-08 06:04:05');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2556987, 415, 'Error loading XML file. (Not Found, Code: 404)', 256, 5, '2011-05-08 08:48:05');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2558256, 415, 'Error loading XML file. (Not Found, Code: 404)', 256, 5, '2011-05-08 11:28:06');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2559523, 415, 'Error loading XML file. (Not Found, Code: 404)', 256, 5, '2011-05-08 14:20:04');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2560791, 415, 'Error loading XML file. (Not Found, Code: 404)', 256, 5, '2011-05-08 17:08:04');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2562060, 415, 'Error loading XML file. (Not Found, Code: 404)', 256, 5, '2011-05-08 19:48:05');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2563328, 415, 'Error loading XML file. (Not Found, Code: 404)', 256, 5, '2011-05-08 22:28:02');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2564598, 415, 'Error loading XML file. (Not Found, Code: 404)', 256, 5, '2011-05-09 01:04:39');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2531247, 1389, NULL, 0, 5, '2011-05-06 01:36:55');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2532514, 1389, NULL, 0, 5, '2011-05-06 04:24:25');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2533782, 1389, NULL, 0, 5, '2011-05-06 07:04:47');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2535050, 1389, NULL, 0, 5, '2011-05-06 09:48:36');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2536316, 1389, NULL, 0, 5, '2011-05-06 12:44:14');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2537582, 1389, NULL, 0, 5, '2011-05-06 15:36:12');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2538850, 1389, NULL, 0, 5, '2011-05-06 18:16:10');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2541389, 1389, NULL, 0, 5, '2011-05-06 23:28:48');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2542660, 1389, NULL, 0, 5, '2011-05-07 02:04:43');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2543929, 1389, NULL, 0, 5, '2011-05-07 04:48:38');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2545198, 1389, NULL, 0, 5, '2011-05-07 07:32:29');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2546468, 1389, NULL, 0, 5, '2011-05-07 10:12:55');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2547737, 1389, NULL, 0, 5, '2011-05-07 12:52:45');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2549006, 1389, NULL, 0, 5, '2011-05-07 15:32:34');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2550275, 1389, NULL, 0, 5, '2011-05-07 18:08:02');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2551545, 1389, NULL, 0, 5, '2011-05-07 20:48:32');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2552813, 1389, NULL, 0, 5, '2011-05-07 23:32:46');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2554083, 1389, NULL, 0, 5, '2011-05-08 02:08:56');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2555351, 1389, NULL, 0, 5, '2011-05-08 04:52:26');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2556621, 1389, NULL, 0, 5, '2011-05-08 07:28:48');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2557888, 1389, NULL, 0, 5, '2011-05-08 10:16:33');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2559156, 1389, NULL, 0, 5, '2011-05-08 13:00:21');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2560423, 1389, NULL, 0, 5, '2011-05-08 15:52:43');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2561692, 1389, NULL, 0, 5, '2011-05-08 18:36:16');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2562960, 1389, NULL, 0, 5, '2011-05-08 21:16:01');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2564229, 1389, NULL, 0, 5, '2011-05-08 23:56:02');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2565498, 1389, NULL, 0, 5, '2011-05-09 02:32:19');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2566768, 1389, NULL, 0, 5, '2011-05-09 05:08:43');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2568037, 1389, NULL, 0, 5, '2011-05-09 07:48:32');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2569304, 1389, NULL, 0, 5, '2011-05-09 10:40:46');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2570571, 1389, NULL, 0, 5, '2011-05-09 13:28:45');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2571838, 1389, NULL, 0, 5, '2011-05-09 16:16:36');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2573106, 1389, NULL, 0, 5, '2011-05-09 18:56:10');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2574373, 1389, NULL, 0, 5, '2011-05-09 21:40:38');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2575640, 1389, NULL, 0, 5, '2011-05-10 00:24:09');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2576906, 1389, NULL, 0, 5, '2011-05-10 03:16:30');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2578175, 1389, NULL, 0, 5, '2011-05-10 05:52:55');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2579443, 1389, NULL, 0, 5, '2011-05-10 08:32:28');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2580711, 1389, NULL, 0, 5, '2011-05-10 11:20:34');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2581979, 1389, NULL, 0, 5, '2011-05-10 14:00:46');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2583239, 1389, NULL, 0, 5, '2011-05-10 16:32:32');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2584511, 1389, NULL, 0, 5, '2011-05-10 19:04:14');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2585784, 1389, NULL, 0, 5, '2011-05-10 21:29:00');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2587056, 1389, NULL, 0, 5, '2011-05-10 23:56:21');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2589600, 1389, NULL, 0, 5, '2011-05-11 04:56:32');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2590873, 1389, NULL, 0, 5, '2011-05-11 07:20:50');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2592143, 1389, NULL, 0, 5, '2011-05-11 10:00:47');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2593414, 1389, NULL, 0, 5, '2011-05-11 12:36:19');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2594682, 1389, NULL, 0, 5, '2011-05-11 15:28:39');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2595953, 1389, NULL, 0, 5, '2011-05-11 18:04:23');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2597226, 1389, NULL, 0, 5, '2011-05-11 20:28:09');
INSERT INTO `portal_gm_cron` (`id`, `id_wms`, `error`, `errorno`, `flag`, `time`) VALUES (2598498, 1389, NULL, 0, 5, '2011-05-11 22:56:59');

marac · 13 Mai 2011

Habe ich das richtig verstanden, dass in der "portal_gm_provide" alle wms_ids liegen, und es für jede wms_id entweder einen Detail-Eintrag in der "portal_gm_log" oder in der "portal_gm_cron" gibt?

Dann sollte das prinzipiell doch fast so funktionieren, wie du geschrieben hast, nur dass der JOIN natürlich kein RIGHT JOIN sondern ein LEFT JOIN sein muss, denn die linke Tabelle (also die "portal_gm_provide") ist doch die Tabelle, die für jede Ergebniszeile etwas liefert...

TDS · 13 Mai 2011

Ja, das habe ich ja alles probiert. Egal ob LEFT oder RIGHT. Es soll ja so funktionieren das auf der log- oder cron-Tabelle die Einträge so erkennbar sein sollen und woher sie kommen. Jedoch habe ich auch bei cron-Einträgen immer time_first und time_last welche ja nur in der log-Tabelle sind.

Bin ratlos

marac · 13 Mai 2011

Ah, ich habe gerade erst gesehen, dass du für die Datenbank ja auch Daten gepostet hattest und nicht nur die Tabellen-Strukturen.
Meine Grundannahme aus dem letzten Post war also falsch, es kann also für jeden Eintrag in der portal_gm_provide auch mehrere Einträge in der portal_gm_log und/oder portal_gm_cron geben. Dann sieht das natürlich ganz anders aus...
Das lässt sich dann natürlich nicht mit Join lösen, da du die Daten von cron und log ja gar nicht gemeinsam in einer Zeile haben willst, folglich darfst du auch keinen Join machen, der diese beiden Tabellen enthält.
Dann brauchst du wohl zwei Abfragen, in denen du einmal provide mit log und einmal provide mit cron verknüpfst. Drum herum dann noch ein Union, also etwa so:

PHP:

(select h.h1
, h.h2
, h.h3
, a.a1
, a.a2
, a.a3
, null
, null
, null
, a.a5 as time
from h
join a on a.a4 = h.h1)
union
(select h.h1
, h.h2
, h.h3
, null
, null
, null
, b.b1
, b.b2
, b.b3
, b.b5 as time
from h
join b on b.b4 = h.h1)
order by time desc
limit 50

TDS · 14 Mai 2011

Okay, ich werd das mal so probieren. Gebe dir dann Bescheid.

TDS · 14 Mai 2011

TDS schrieb:
Okay, ich werd das mal so probieren. Gebe dir dann Bescheid.

Ich glaub es funktioniert

PHP:

(SELECT 
		db_main.wms_id AS id_wms,
		db_log.message, db_log.message_real, db_log.id_layer,
		db_log.code, db_log.subcode, db_log.counter,
		db_log.time_first, db_log.time_last, NULL as error, NULL as errorno,
		CASE WHEN db_log.time_last IS NULL THEN db_log.time_first ELSE db_log.time_last END AS TIME
	FROM portal_gm_provide AS db_main
	JOIN portal_gm_log AS db_log ON db_log.id_wms = db_main.wms_id
	WHERE db_main.user_id = 1
)
UNION
(SELECT 
		db_main.wms_id AS id_wms,
		NULL, NULL, NULL,
		NULL, NULL, NULL,
		NULL, NULL,	db_cron.error, db_cron.errorno,
		db_cron.time AS TIME
	FROM portal_gm_provide AS db_main
	JOIN portal_gm_cron AS db_cron ON db_cron.id_wms = db_main.wms_id
	WHERE db_main.user_id = 1
)
ORDER BY time DESC
LIMIT 500

Werd das nochmal kurz richtig durchtesten. Danke schon mal dafür :renoyg:

marac · 14 Mai 2011

TDS schrieb:

Hallo,

also mit einigen Statements geht's, mit einigen nicht. Das betrifft z.B. das Zeitfeld:

PHP:

 (SELECT SQL_CALC_FOUND_ROWS db_main.wms_id AS id_wms,
         db_sub.message                              ,
         db_sub.message_real                         ,
         db_sub.id_layer                             ,
         db_sub.code as code                         ,
         db_sub.subcode                              ,
         db_sub.counter                              ,
         db_sub.time_first                           ,
         db_sub.time_last                            ,
         NULL as error                               ,
         NULL as errorno                             ,
         1    as type                                ,
         CASE
                 WHEN db_sub.time_last IS NULL
                 THEN db_sub.time_first
                 ELSE db_sub.time_last
         END                AS mytime
 FROM    portal_gm_provide  AS db_main
         JOIN portal_gm_log AS db_sub
         ON      db_sub.id_wms = db_main.wms_id
 WHERE   1
 AND     user_id = 1
 AND     id_wms  ="1389"
 AND
         (
                 UNIX_TIMESTAMP(mytime) > 1305367204
         )
 )

UNION ALL
          (SELECT db_main.wms_id AS id_wms,
                  NULL                    ,
                  NULL                    ,
                  NULL                    ,
                  NULL                    ,
                  NULL                    ,
                  NULL                    ,
                  NULL                    ,
                  NULL                    ,
                  db_sub.error            ,
                  db_sub.errorno      as code  ,
                  2                   as type  ,
                  db_sub.time         AS mytime
          FROM    portal_gm_provide   AS db_main
                  JOIN portal_gm_cron AS db_sub
                  ON      db_sub.id_wms = db_main.wms_id
          WHERE   1
          AND     user_id = 1
          AND     id_wms  ="1389"
          AND
                  (
                          UNIX_TIMESTAMP(mytime) > 1305367204
                  )
          )
ORDER BY  mytime DESC LIMIT 100

=> Unknown column 'mytime' in 'where clause'

Ich bin ratlos. Hab alles schon umbenannt, Tabellen in db_sub1 und db_sub2, HAVING probiert, ON clause probiert - alles kein Erfolg.

Hast du eine Lösung für so ein Problem?

Warum bleibst du mit der Frage denn nicht im Thread? Erstens könnte es ja sein, dass auch jemand anders darauf eine Antwort hat, zweitens könnte irgendwann auch mal jemand anders die selbe Frage haben, und wäre dann vielleicht froh, hier über die Suchfunktion etwas zu finden...

Aber zur Frage selbst:
Das Problem hat ja diesmal nichts mit dem Zusammenpappen der beiden Selects zu tun, sondern liegt darin, dass mysql offensichtlich dein definiertes Alias "mytime" in der where-Klausel noch nicht versteht. Ich nehme mal an, dass das Problem einfach die Verarbeitungsreihenfolge ist. mysql will wohl zunächst die Zeilen filtern, die verarbeitet werden sollen, anschließend wird der eigentliche select ausgeführt und danach das Ergebnis sortiert. Deshalb funktioniert die Verwendung der Aliase in der order-by-Klausel, aber nicht in der where-Klausel.
Du musst also versuchen, die Verwendung des Alias "mytime" in der where-Klausel zu entfernen. (Zum Beispiel indem du die case-when-Abfrage unten einfach wiederholst.)
Im Übrigen gibt es für "case a when null then b else a end" eine deutlich kürzere Schreibweise: "ifnull(a,b)"

MySQL JOIN und ORDER über mehrere Tabellen

TDS

Webbie u. Progger

marac

Well-known member

TDS

Webbie u. Progger

marac

Well-known member

TDS

Webbie u. Progger

TDS

Webbie u. Progger

marac

Well-known member

Ähnliche Themen