SQL Abfrage mit "verknüpften" Tabellen

[GRudiD]

Hallo,

ich schreibe momentan an einer Verwaltungssoftware für Audio-Dateien auf Datenbankbasis. An der Datenbank an sich kann ich nichts ändern.

Die Struktur sieht u.a. folgendermaßen aus:
eine Tabelle für die Audio-Dateien [audio_files]
eine Tabelle für die Interpreten [artists] und
eine Tabelle, die die Tabellen [audio_files] und [artists] per id verbindet [relation] (id, audioFileID, artistID).

So ist es möglich, dass einer Audio-Datei mehrere Interpreten zugewiesen werden können und andersherum ein Interpret mehreren Audio-Dateien angehören kann.

Ich versuche nun allerdings schon seit geraumer Zeit eine SQL-Abfrage zu erstellen, ähnlich dieser: "Gebe mir alle!!! Einträge aus [audio_files] und verknüpfe sie anhand der Tabelle [relations] mit den entsprechenden Interpreten aus [artists]"

SELECT audio_files.title, artists.name FROM audio_files INNER JOIN relation, artists WHERE relation.audioFileID = audio_files.id AND relation.artistID = artists.id;

Probleme dabei sind:
1. Die Einträge sind so oft vorhanden, wie Interpreten zugeordnet sind.
Gibt es eine Möglichkeit die Interpreten z.B. Trennzeichen getrennt in eine einzelne Zelle zu schreiben?
title = "blabla" | artist = "artist1,artist2"

2. Wenn einem Eintrag keine Interpreten zugeordnet sind (kein Eintrag in [relation]), wird dieser auch nicht zurückgegeben.

Also Lösung ist mir der Gedanke gekommen, eine verschachtelte Abfrage zu nehmen "Gebe mir alle Einträge aus [audio_files] zurück" und für jeden Eintrag dann "Gebe mir alle Interpreten mit der relation.audioFileID = ... zurück". Die Lösung ist aber so zeitintensiv, dass ich bei 10000 Dummydaten ruhig mal eine Tasse Kaffee trinken kann.

Hat jemand einen Lösungsvorschlag?

 

[Jockel]

Hast du dir schonmal den 'distinct'-Befehl angeschaut?

 

[Ivanhoe]

1. kann hier nur den Count anbieten:

2. versuch doch folgendes:
SELECT af.title, count(r.id)
FROM audio_files af
LEFT OUTER JOIN relation r ON
r.audioFileID = af.id
GROUP BY af.title;

 

[bronks]

... Hat jemand einen Lösungsvorschlag?

Je nach Datenbankmaschine gibt es ganz spezielle Möglichkeiten, sowas einfacher oder komplizierter umzusetzen. Ich will Dich aber nicht dazu drängen uns Deine Geheimnisse auszuplaudern. :wink:

 

[GRudiD]

Vielen Dank für die schnellen Antworten.

@Jockel: Den DISTINCT Befehl habe ich mir schon angesehen. Problem dabei ist nur, dass dann nur der erste Interpret angezeigt wird und alle weiteren ignoriert werden.

@Ivanhoe: Dieser Befehl funktioniert zwar ganz gut, hat nur den Nachteil, dass er relativ langsam läuft. Ich habe das mal mit nur 10!!! Einträgen (LIMIT 10) versucht. Dafür hat er schon gut 2 Sekunden gebraucht. auf gute 10000 Einträge hochgerechnet, ich will gar nicht dran denken ...

@bronks: Ich nutze momentan die MySQL Datenbank 5.0. Wenn du das meinst. Wenn du mehr wissen willst, meld dich einfach 🙂

Ich habe nun einmal versucht, nur die audio_files DB auszulesen (ohne die artists, 1.7 Sekunden), diese in einen Vector für eine JTable gespeichert und nur die Interpreten zu laden, wenn der Eintrag angezeigt wird. So richtig fix läuft das allerdings auch nicht. Zumal ich für jeden Track die Daten einzeln abrufn muss (Connection, Statement, ResultSet erstellen, Abfrage tätigen, Ergebnisse eintragen und alles wieder schließen).

Hier mal der Quelltext für die TrackItems, die im JTable angezeigt werden. Momentan wichtig ist dabei die Methode loadArtists().

public class TrackItem {

	private int id;
	private String name;
	private List<String> artists;

	public TrackItem(int id, String name) {
		this.id = id;
		this.name = name;
	}

	public int getId() {
		return this.id;
	}

	public String getName() {
		return this.name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public void loadArtists(){
		if (this.artists == null) {
			this.artists = new ArrayList<String>();
			
			try {
				Statement statement = Command.getConnection().createStatement();
				ResultSet resultSet = statement
						.executeQuery("SELECT artists.name FROM artists INNER JOIN artist_audio_file_relation, audio_files WHERE artist_audio_file_relation.artistID = artists.id AND artist_audio_file_relation.audioFileID = audio_files.id AND artist_audio_file_relation.audioFileID = "
								+ this.id);
				
				while (resultSet.next()) {
					this.artists.add(resultSet.getString("name"));
				}
				
				resultSet.close();
				statement.close();

				resultSet = null;
				statement = null;
			} catch (SQLException exception) {
				exception.printStackTrace();
			}
		}
	}
	
	public List<String> getArtists() {
		this.loadArtists();
		return this.artists;
	}

	public String toString() {
		return "<html>" + this.getName() + "
<span style=\"color:gray\">" + this.getArtists().toString() + "</span></html>";
	}
}

 

[Ivanhoe]

@Ivanhoe: Dieser Befehl funktioniert zwar ganz gut, hat nur den Nachteil, dass er relativ langsam läuft. Ich habe das mal mit nur 10!!! Einträgen (LIMIT 10) versucht. Dafür hat er schon gut 2 Sekunden gebraucht. auf gute 10000 Einträge hochgerechnet, ich will gar nicht dran denken ...

Was für eine DB verwendest du? Und auf welcher Maschine läuft das Ganze? Z.B. für Oracle sind die 10000 auch nicht viel.

 

[GRudiD]

Wie gesagt, das Ganze läuft auf MySQL 5.0. Nutzen tue ich für die Entwicklung einen 3GHz PC mit 512MB Ram.

10000 Einträge sind dabei auch nicht das Problem, solange ich nur INNER JOIN nutze. Bei LEFT JOIN etc. braucht er allerdings brachial mehr Zeit.

Ich habe leider nicht die Möglichkeit das Datenbanksystem zu wechseln, da es bereits seit geraumer Zeit fertig auf einem Server leigt und der nun mal MySQL nutzt.

 

[GRudiD]

Status: gelöst!

Also, mit LEFT JOIN braucht mein Rechner rund 28 Minuten für 10000 Einträge. Ich habe das nun so gelöst, in dem ich alle Einträge aus der Tabelle audio_files in eine Hashtable geladen habe (key = id). In einer zweiten Abfrage habe ich dann mittels INNER JOIN die verknüpften Elemente "drüberkopiert".
Macht dann nur noch rund 4 Sekunden für 10000 Einträge.