✔ mySQL - Abfrage über 2 Tabellen

[tombe]

Hallo,

ich habe 2 Tabellen in denen Maschinendaten bzw. -texte dazu gespeichert sind.

Tabelle "maschine":

nummer | seriennummer | bezeichnung | baujahr
100    | 12345        | Kran 100    | 1995
200    | 09876        | Kran 200    | 2002
300    | 11111        | Kran 300    | 2010

Tabelle "textbaustein":

nummer | zeile | text
100    | 1     | Ausstattung 1
100    | 2     | Ausstattung 2
100    | 3     | Ausstattung 3
200    | 1     | Ausstattung 1
300    | 1     | Ausstattung 1
300    | 2     | Ausstattung 1

Jetzt versuche ich eine Abfrage zu gestallten mit der es mir möglich ist die 2 Tabellen gleichzeitig nach verschiedenen Suchbegriffen zu durchsuchen (Feld "bezeichnung" und "text").

Im Moment habe ich folgende Abfrage:

SELECT m.nummer, m.bezeichnung, m.baujahr, m.seriennummer, t.text
FROM maschine AS m
LEFT JOIN textzeile AS t ON m.nummer = t.nummer
WHERE m.nummer IN
(
SELECT nummer
FROM maschine AS m
WHERE m.bezeichnung LIKE '%anhänger%' OR m.bezeichnung LIKE 'funk%') UNION
(
SELECT nummer
FROM textzeile AS t
WHERE t.text LIKE '%anhänger%' OR t.text LIKE 'funk%')

Doch leider liefert sie mir keine Treffer.

Wenn ich im WHERE Abschnitt direkt etwas eingebe (und den Rest der Abfrage weglasse) werden mir die Treffer angezeigt.
Nehme ich nur den zweiten Teil der Abfrage (nach WHERE) und führe sie aus, erhalte ich auch das richtige Ergebnis.

Nur in der obigen Kombination klappt es nicht. Es kommt der Fehler "The used SELECT statements have a different number of columns ".

Was mache ich falsch ? ? ?

 

[tombe]

Ich habe den Fehler scheinbar doch selber gefunden.

Man darf die Abfragen die als WHERE-Bedingung angegeben sind nicht mit UNION verknüpfen, sondern man muss beide Abfragen als separate Bedingung angeben.

Sieht dann so aus:

SELECT m.nummer, m.bezeichnung, m.baujahr, m.seriennummer, t.text
FROM maschine AS m
LEFT JOIN textzeile AS t ON m.nummer = t.nummer
WHERE m.nummer IN
(
SELECT nummer
FROM maschine AS m
WHERE m.bezeichnung LIKE '%anhänger%' OR m.bezeichnung LIKE 'funk%') OR m.nummer IN
(
SELECT nummer
FROM textzeile AS t
WHERE t.text LIKE '%anhänger%' OR t.text LIKE 'funk%')

Dann aber gleich die nächste Frage. Macht die Abfrage so Sinn bzw. was kann besser gemacht werden?

 

[Yaslaw]

Wenn du das ganze mal sauber formatierst, erkenst du, dass der UNION und die Klammern da ein kleines Durcheinander machen

SELECT 
	m.nummer, 
	m.bezeichnung, 
	m.baujahr, 
	m.seriennummer, 
	t.text
FROM 
	maschine AS m
	LEFT JOIN textzeile AS t 
		ON m.nummer = t.nummer
WHERE 
	m.nummer IN (
				SELECT nummer
				FROM maschine AS m
				WHERE m.bezeichnung LIKE '%anhänger%' OR m.bezeichnung LIKE 'funk%')
UNION
(
SELECT nummer
FROM textzeile AS t
WHERE t.text LIKE '%anhänger%' OR t.text LIKE 'funk%')

Wenn du die schliessende Klammer vor und die öffnende Klammer nach dem UNION entfernst, dann hast du das was du haben willst

SELECT 
	m.nummer, 
	m.bezeichnung, 
	m.baujahr, 
	m.seriennummer, 
	t.text
FROM 
	maschine AS m
	LEFT JOIN textzeile AS t 
		ON m.nummer = t.nummer
WHERE 
	m.nummer IN (
				SELECT nummer
				FROM maschine AS m
				WHERE m.bezeichnung LIKE '%anhänger%' OR m.bezeichnung LIKE 'funk%'
				UNION
				SELECT nummer
				FROM textzeile AS t
				WHERE t.text LIKE '%anhänger%' OR t.text LIKE 'funk%'
	)

 

[tombe]

Hi yaslaw,

danke das du mal wieder geholfen hast.

Die Klammern stammten noch aus der "Entstehungszeit" der UNION-Abfrage. Da kam mal ein Fehler als ich der Abfrage einen Alias geben wollte und da es dann mit den Klammern ging, habe ich sie so gelassen.

 

[Yaslaw]

Ansonsten kannst du auch ein search-Feld zusammensetzen das du dann durchsuchen kannst

SELECT 
	m.nummer, 
	m.bezeichnung, 
	m.baujahr, 
	m.seriennummer, 
	t.text
FROM 
	maschine AS m
	LEFT JOIN textzeile AS t 
		ON m.nummer = t.nummer
	-- Das Suchfeld anhängen
	INNER JOIN (
			SELECT 
				m1.nummer,
				-- Das Suchfeld zusammensetzen
				CONCAT(m1.bezeichnung, ' ', GROUP_CONCAT(t1.text SEPARATOR ' ')) AS search
			FROM
				maschine AS m1
				LEFT JOIN textzeile AS t1
					ON m1.nummer = t1.nummer
			GROUP BY
				m1.nummer,
				m1.bezeichnung
		) AS searchitems
		ON m.nummer = searchitems.nummer
WHERE
	searchitems.search LIKE '%anhänger%'
	OR searchitems.search LIKE 'funk%'

 

[tombe]

Auch nicht schlecht oder im Grunde soger noch viel besser.

So wie es aussieht ist aber die Länge von GROUP_CONCAT auf 1024 Zeichen beschränkt und ich muss jetzt mal sehen ob ich damit ein Problem kriegen kann.

Wenn nicht reicht sonst diese "einfache" völlig aus:

SELECT
	m.nummer,
	m.bezeichnung,
	GROUP_CONCAT(t.text ORDER BY zeile SEPARATOR "<br>") AS zusatz
FROM
	maschine AS m
LEFT JOIN textzeile AS t
	ON m.nummer = t.nummer
	GROUP BY m.nummer
	HAVING m.bezeichnung LIKE '%kran%' OR m.bezeichnung LIKE '%funk%'
	OR zusatz LIKE '%kran%' or zusatz LIKE '%funk%'

NACHTRAG:

Für alle für die der hier gezeigte Weg nützlich ist und die (so wie ich) ein Problem mit der maximalen Länge der GROUP_CONCAT-Funktion haben.

Man kann die Länge mit folgender Anweisung vorübergehend zum Beispiel auf 2000 Zeichen erhöhen:

SET @@session.group_concat_max_len= 2000;

Näheres dazu gibt es auch hier.