SQL brauche Hilfe beim Query zusammenbasteln

[Kantholy]

Hallo Jungs (und Mädels ),

ich hab heute mal wieder ein gewaltiges Problem, und zwar gehts diesmal um meine Inventardatenbank die ich vor einiger Zeit geschrieben habe...

kurze Info: ich führe in diese Datenbank alle unsere Rechner und Laptops in der Firma, also Gerätename, Seriennummer, Hersteller, Typ, etc. - in einer "objects" Tabelle...

in einer anderen Tabelle führe ich eine Standordshistory (tabelle "location", properties: `id`, `object`, `location`, `office`, `user`, `department`, `timestamp)


so, object ist jetzt quasi der Fremdschlüssel, was immer einer "object".ID entspricht...

der aktuellste "location" - datensatz (order by timestamp desc) ist quasi der aktuelle Standpunkt, der Rest existiert aber weiterhin in meiner Tabelle - wegen meiner History die ich brauch...


so - was ich jetzt haben will, ist ein query der mir die objekte - inklusive aktueller "location" properties als EINE zeile ausgibt - wie stell ich das an?!

ich kann ja einfach machen

SELECT * FROM objects LEFT JOIN location ON location.object = objects.id

allerdings spuckt der mir dann eben die Datensätze DOPPELT aus - ich brauch die ja aber eindeutig - weils eben den Rechner nur EINMAL in der Firma gibt...

wenn ich jetzt "GROUP BY objects.name" noch anfüg hab ichs zwar EINDEUTIG - allerdings benutzt es jetzt von der "location" tabelle, den 1. Datensatz den er findet - was natürlich blöd ist, weil ich jetzt immer noch nicht den aktuellen Stand hab...


hat hierzu irgendjemand eine Idee wie ich genau das erreichen kann? Wäre echt wichtig

 

[volcem]

Weiß nicht ob ich dich richtig verstehe aber versuche es doch mal mit:

SELECT DISTINCT * FROM objects LEFT JOIN location ON location.object = objects.id ORDER BY objects.id DESC LIMIT 1

Damit hättest du den letzten aktuellen Datensatz.

 

[CHaos.Gentle]

[...]SELECT DISTINCT * FROM objects LEFT JOIN location ON location.object = objects.id ORDER BY objects.id DESC LIMIT 1
[...]

Ersetze ORDER BY objects.id durch order by timestamp dann passts. Die Lösung hast du ja eigentlich schon selbst genannt...

[EDIT]
Ach, irgendwie falsch verstanden und dann noch Mist geschrieben -.-

[EDIT II]
Also eine Variante wäre, die Objekte einzelnd mit einer Schleife zu holen und dann Sortieren nach Timestamp. Über die andere Variante muss ich länger nachdenken...

 

[Kantholy]

sorry, muss euch leider enttäuschen :-( das liefert mir wieder nicht den letzten aus der "location" tabelle.

Beweis:

die ersten beiden Bilder sind die einzelnen Tabellen anhand eines Rechner bei dem ich mir Sicher bin dass er schon den Standord gewechselt hat...

die letzten beiden sind eure Vorschläge mit den Queries...

das Limit 1 kann ich auch nicht brauchen, da ich mir ja eine gesamtliste ausspucken will von ALLEN Rechnern und nicht nur einem -.-

 

[volcem]

//Query
$test = mysql_query("SELECT * FROM `objects` LEFT JOIN `location` ON `location`.`object` = `objects`.`id`ORDER BY  `location`.`timestamp`") or (mysql_error());
/*
mit WHERE einfach das dran hängen:
WHERE `objects`.`id`= '193'
*/
//Schleife:
while($row = mysql_fetch_array($test)) 
    {

//holt nun alle Datensätze

}

??

 

[CHaos.Gentle]

@volcem: joar, dass ist meine zweite lösung gewesen

ich bastel grad an einer einzelnen abfrage aber ohne sql ist das nich so leicht

[EDIT] ich steh heute irgendwie neben mir...nein das ist nicht meine zweite lösung gewesen.

 

[volcem]

:-)

Kann es auch nicht testen^^ :-P

*edit*

Den richtigen Query kannst du doch eigentlich mit phpMyAdmin super zusammen klicken:

Klick auf deine Tabelle XX -> Abfrageeditor -> Daten die du brauchst anklicken und Query raus kopieren.

 

[Kantholy]

[EDIT II]
Also eine Variante wäre, die Objekte einzelnd mit einer Schleife zu holen und dann Sortieren nach Timestamp. Über die andere Variante muss ich länger nachdenken...

das fällt flach - ich brauch das in EINEM query, weil ich danach nämlich noch weiter filtern muss - genau deswegen will ichs ja in einem query weil das mit mehreren SQL Queries hab ich jetzt auch schon, aber das ist eben kein sauberes arbeiten - und dafür bin ich eben ein viel zu großer perfektionist - das MUSS einfach funktionieren... die frage ist halt wie ;-)

 

[volcem]

Hab ich doch gerade gepostet, machst du doch mit dem LEFT JOIN.
Es ist dann alles in einem Query, verstehe dein Problem nicht!

 

[darefilz]

Hallo.
Ich kann leider auch nicht testen gerade, aber wenn ich dein Problem richtig verstanden habe, könnte dir vielleicht die Idee von folgendem Befehl helfen:

SELECT * FROM objects
JOIN
(SELECT * FROM location ORDER BY timestamp ASC LIMIT 1) [AS] loc
ON loc.object = objects.id;

df

 

[Kantholy]

mein problem ist - dass es nicht das macht was ich will das joint mir immer den ERSTEN datensatz den es als treffer findet - aber genau das ist der fehler...

so, jetzt gibts mal was feines zum testen

CREATE TABLE IF NOT EXISTS `location` ( 
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT, 
  `object` int(10) unsigned NOT NULL, 
  `location` varchar(10) NOT NULL, 
  `office` int(5) unsigned NOT NULL, 
  `user` varchar(60) NOT NULL, 
  `department` int(5) unsigned NOT NULL, 
  `timestamp` int(10) unsigned NOT NULL, 
  `sav` tinyint(1) unsigned DEFAULT NULL, 
  PRIMARY KEY (`id`) 
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `objects` ( 
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT, 
  `name` varchar(30) NOT NULL, 
  `serial` varchar(30) DEFAULT NULL, 
  `type` tinyint(1) unsigned NOT NULL, 
  `manufacturer` int(5) unsigned NOT NULL, 
  `model` int(5) unsigned NOT NULL, 
  PRIMARY KEY (`id`), 
  UNIQUE KEY `serial` (`serial`) 
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;


INSERT INTO `location` (`id`, `object`, `location`, `office`, `user`, `department`, `timestamp`, `sav`) VALUES 
(279, 195, '1', 30, 'Praktikant', 23, 1249284194, NULL), 
(280, 195, '8', 39, 'CNC-PC', 28, 1249286832, NULL),
(282, 196, '8', 39, 'CNC-PC', 28, 1249286955, NULL);


INSERT INTO `objects` (`id`, `name`, `serial`, `type`, `manufacturer`, `model`) VALUES 
(195, 'GX520-004', '1XXP72J', 1, 1, 1), 
(196, 'GX520-011', '5RXP72J', 1, 1, 1);

so - wenn ich jetzt hergehe und den von euch vorgeschlagenen query laufen lasse - dann kommt genau folgendes bei raus: (siehe Anhang)

die ID von der "location" tabelle passt nicht - es muss nämlich die ID 280 statt 279 (also den LETZTEN statt den ERSTEN matching satz) benutzen...

 

[CHaos.Gentle]

Nein Volcem, dabei hast du das Problem, dass per Order By immer der oberste Tabelleneintrag genutzt wird. Ist das nicht der aktuelle, haste nen Fehler in der Auswertung...

Ich stelle mir sowas hier vor:
Select * FROM (Select * From 'location' ORDER BY timestamp) as 'l' LEFT JOIN 'objects' as 'o' ON o.ID = a.ID group by Id

 

[Kantholy]

Hallo.
Ich kann leider auch nicht testen gerade, aber wenn ich dein Problem richtig verstanden habe, könnte dir vielleicht die Idee von folgendem Befehl helfen:

SELECT * FROM objects
JOIN
(SELECT * FROM location ORDER BY timestamp ASC LIMIT 1) [AS] loc
ON loc.object = objects.id;

df

das limit darf nicht im subquery stehen - weil ich sonst statt in 230 datensätzen in genau EINEM suche... ich muss das irgendwie hinbekommen dass der mir die datensätze irgendwie "überschreibt" wenn er schon einen mit dieser ID gefunden hat... die frage ist "how to?!"

 

[volcem]

SELECT * FROM `objects` LEFT JOIN `location` ON `location`.`object` = `objects`.`id` GROUP BY `location`.`timestamp` DESC


oder bin ich gerade so durchen Wind ?

 

[Kantholy]

dingdingding - taugt net! liefert 3 statt 2 datensätze (den einen wie vermutet doppelt), man darf nicht aus der location tabelle groupen -.-

sorry Leute, aber ich seh schon, das ist echt hardcore... nichtsdestotrotz brauch ich ne Lösung für das Problem oder mein Chef hackt mir den Kopf ab... weil damit müssen Kostenstellenauswertungen gefahren werden und das ergebnis MUSS einfach passen....

 

[volcem]

Ja dann halt

SELECT * FROM `objects` LEFT JOIN `location` ON `location`.`object` = `objects`.`id` GROUP BY `objects`.`id` DESC

Liefert nur 2 Datensätze!

*edit*
hmm .. ^^

 

[darefilz]

das limit darf nicht im subquery stehen - weil ich sonst statt in 230 datensätzen in genau EINEM suche... ich muss das irgendwie hinbekommen dass der mir die datensätze irgendwie "überschreibt" wenn er schon einen mit dieser ID gefunden hat... die frage ist "how to?!"

Äh stimmt. Dann wäre vielleicht sowas angebracht?:

SELECT * FROM objects
JOIN
(SELECT * FROM location GROUP BY object HAVING timestamp = MIN(timestamp)) [AS] loc
ON loc.object = objects.id;

Ist alles ungetestet, muss also bestimmt noch nach korrigiert werden.

df

 

[Kantholy]

dingdingding - taugt immernoch net! beim 2. Datensatz wird nämlich schon wieder mal der erste "location" Datensatz gejoint - und nicht wie ich das will der letzte!

Zur Kontrolle: da muss die location id 280 und nicht 279 gejoint werden :S (oder: es ist erst richtig, wenn da 2x CNC-PC bei user steht)

 

[CHaos.Gentle]

@kant-holz: meines ausprobiert?

theorie is: query wie gewohnt, left join zwischen objects-tabelle und nach timestamp sortierter location-tabelle gruppiert nach id. müsste eigentlich klappen.

 

[Kantholy]

@kant-holz: meines ausprobiert?

jo hab ich nachdem ich deinen query gefixt hab - ergebnis: 3 datensätze.... ==> passt nicht

SELECT *
FROM (

SELECT *
FROM location
ORDER BY `timestamp`
) AS l
LEFT JOIN objects AS o ON o.id = l.object
GROUP BY l.id