SQL Verknüpfung von Datensätzen

[TobiaZ]

EDIT:

---

Direkt zum aktuellen Problem: http://www.php-resource.de/forum/sho...306#post329306

---

Also ich habe jede menge Datensätze in Tabelle "friends" und muss diese miteinander verknüpfen. Und zwar gegenseitug. also nehme ich mir noch ne tabelle "friend_friend". In dieser Tab wird jetzt also die Beziehung zwischen den freunden hergestellt.

friends: id, name
1 Tom
2 Frank
3 Sabine
4 Jan
5 Sandra
6 Anne

Wie baue ich die friend_friend auf?

Sehe im Moment nur zwei möglichkeiten
1) friend_friend: me_id, other_id
2) friend_friend: friend1_id, friend2_id

Bei Variante 1 wären die Abfragen etc. alle relativ einfach und schnell. Allerdings hätte ich für jede Verbindung zwei Einträge: Tom und Sabine bekommen (1,3 und 3,1).

Bei Variante 2 werden die Abfragen vermutlich etwas aufwändiger, dafür hab ich nur jeweils einen Eintrag pro Verbindung. (Ob Tom jetzt an erster oder zweiter Stelle steht, ist ja egal.)

Welche Variante ist in euren Augen besser? Oder gibts die perfekte lösung, die ich übersehen habe?

[mrhappiness]

nimm variante 2, schnell genug wird's trotzdem noch sein, du musst bei deinem INNER JOIN ja nur eine bedingung mehr einbauen

[Heini81]

Aber die erste Variante wäre vielleicht besser, wenn jeder Benutzer
seine eigenen Freunde aussuchen will.
Beispiel: Hans will Max auf seiner Freundesliste haben, Max aber
nicht Hans....
Kommt drauf an, ob sowas möglich sein soll, ansonsten ist Variante 2 besser.

[TobiaZ]

Ich überlege bei der struktur allerdings noch, wie ich mir alle personen in friends anzeigen lassen kann, die nicht meine friends sind.

Tabelle friends s.o.

CREATE TABLE `friend_friend` (
`friend1_id` int(10) unsigned NOT NULL default '0',
`friend2_id` int(10) unsigned NOT NULL default '0'
) TYPE=MyISAM;

INSERT INTO `friend_friend` VALUES (1, 3),(4, 3),(5, 2),(5, 3),(3, 2);

SELECT DISTINCT(f.friend_id), f.name
FROM friends f
LEFT JOIN friend_friend ff 
  ON f.friend_id = ff.friend1_id 
  OR f.friend_id = ff.friend2_id
WHERE ff.friend1_id IS NULL 

Liefert mir alle Personen, die GAR KEINE Freunde haben. In diesem Fall nur Anne(6). Allerdings finde ich keinen weg die Query so zu erweitern, dass ich alle Personen finde, die nicht mit Frank (2) befreundet sind. Das wären 1Tom, 4Jan, 6Anne

[TobiaZ]

Aber die erste Variante wäre vielleicht besser, wenn jeder Benutzer seine eigenen Freunde aussuchen will.

Kommt hierbei definitiv nicht vor. Entweder beide mögen sich, oder keine Freundschaft kommt zustande.

Weiterer Grund für die zweite Version ist, dass es möglich ist, dass einer die "Freundschaft" anfordert. Wenn der andere jetzt ebenfalls anfordert, wäre nicht so schön. Prüfung ist ja überflüssig, wenn man nur einen DS hat. Außerdem denke ich, dass "doppelte" Datensätze eigentlich nicht sein müssen.

[derHund]

dass ich alle Personen finde, die nicht mit Frank (2) befreundet sind. Das wären 1Tom, 4Jan, 6Anne

diese recht abenteuerliche query bringt zumindest für frank das gewünschte ergebnis ... keine ahnung, was ich da gebastelt habe ...

SELECT
  f.name yourname,
  f2.name yourfriendsname
FROM
  friends f
LEFT JOIN
  friend_friend ff1
ON
  f.id = ff1.friend1_id
OR
  f.id = ff1.friend2_id
RIGHT JOIN
  friends f2
ON
  f2.id = IF(ff1.friend1_id = 2, ff1.friend2_id, ff1.friend1_id)
WHERE
  f.id = 2
AND
  f2.id != 2
AND
  IF(ff1.friend1_id = 2, ff1.friend2_id, ff1.friend1_id) IS NULL

[TobiaZ]

Hmm,

also ich ziehe seit gestern nacht doch wieder beide versionen in betracht. Wobei mich Hunds Query schon Richtung Variante 2 leitet.

Allerdings vermute ich fast, dass ich mit Variante 1 performancemäßig besser liege...

[mrhappiness]

was spricht gegen

SELECT
	f.*,
	SUM(ff.friend1_id = 2 OR ff.friend2_id = 2) befreundet
FROM
	friend f
	LEFT JOIN friend_friend ff 
	ON f.friend_id IN (ff.friend1_id, ff.friend2_id)
GROUP BY
	f.friend_id
HAVING
	befreundet = 0

Ich finde, es sieht gut aus, kannst ja mal testen

[TobiaZ]

Sah in der Tat gut aus. >Habs aber sicherheitshalber mal getestet. bringt mir allerdings nur Tom und Jan, Anne muss draußen bleiben, weil Anne ja noch gar keine Verknüpfung in den System hat...

hab mir jetzt überlegt, dass ich wohl meine Prioritäten auf die Geschwindigkeit beim Auslesen der vorhandenen Freunde legen sollte. Die Funktion wird/muss wohl am häufigsten genutzt werden.

Das Auflisten der "noch nicht Freunde" ist dann erstmal sekundär. Wenn auch nicht ganz unwichtig!

In meinen Augen wäre unter diesem Gesichtspunkt wohl Variante 1 die bessere. Obgleich ich hier beim Hinzufügen, Löschen, ... ne zusätzliche jedoch einfache Prüfquery einsetzen müsste, was denke ich nicht ins Gewicht fällt.

Was denkt ihr?

[mrhappiness]

so, jetzt hab ich's auch mal getestet:

mysql> SELECT * FROM friend;
+-----------+-------------+
| friend_id | friend_name |
+-----------+-------------+
|         1 | Tom         |
|         2 | Frank       |
|         3 | Sabine      |
|         4 | Jan         |
|         5 | Sandra      |
|         6 | Anne        |
+-----------+-------------+
6 rows in set (0.00 sec)

mysql> SELECT * FROM friend_friend;
+------------+------------+
| friend1_id | friend2_id |
+------------+------------+
|          1 |          3 |
|          4 |          3 |
|          5 |          2 |
|          5 |          3 |
|          3 |          2 |
+------------+------------+
5 rows in set (0.00 sec)

mysql> SELECT
    ->  f.*,
    ->  SUM(ff.friend1_id = 2 OR ff.friend2_id = 2) befreundet
    -> FROM
    ->  friend f
    ->  LEFT JOIN friend_friend ff
    ->  ON f.friend_id IN (ff.friend1_id, ff.friend2_id)
    -> GROUP BY
    ->  f.friend_id
    -> HAVING
    ->  befreundet = 0;
+-----------+-------------+------------+
| friend_id | friend_name | befreundet |
+-----------+-------------+------------+
|         1 | Tom         |          0 |
|         4 | Jan         |          0 |
|         6 | Anne        |          0 |
+-----------+-------------+------------+
3 rows in set (0.00 sec)

Ich seh Anne
Du solltest sie auch sehen, schließlich verwendest du ja den gleichen LEFT JOIN wie ich oder?!

[TobiaZ]

Ganz ehrlich, Anna kommt bei mir nicht. Unsere Queries unterscheiden sich lediglich in der tabelle friend, die heißt bei mir friends. Alles andere is Copy & Paste.

Wenn ich Anne z.b. mit Sandra verknüpfe, dann wird sie mir angezeigt. (MySQL 4.0.15)

Wenn ich Having erweitere (nach dem alten muster), dann kommt Anna mit:

SELECT f.*,
  SUM( ff.friend1_id =2 OR ff.friend2_id =2 ) befreundet, 
  ff.friend1_id
FROM friends f
LEFT JOIN friend_friend ff 
  ON f.friend_id IN (ff.friend1_id, ff.friend2_id)
GROUP BY f.friend_id
HAVING befreundet =0 OR friend1_id IS NULL
LIMIT 0 , 30

Aber das kanns nicht wirklich sein.

[mrhappiness]

ich weiß ja nicht was du machst, aber bei mir gehts

mysql> CREATE TABLE friends AS SELECT * FROM friend;
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT f.*,
    ->   SUM( ff.friend1_id =2 OR ff.friend2_id =2 ) befreundet,
    ->   ff.friend1_id
    -> FROM friends f
    -> LEFT JOIN friend_friend ff
    ->   ON f.friend_id IN (ff.friend1_id, ff.friend2_id)
    -> GROUP BY f.friend_id
    -> HAVING befreundet =0;
+-----------+-------------+------------+------------+
| friend_id | friend_name | befreundet | friend1_id |
+-----------+-------------+------------+------------+
|         1 | Tom         |          0 |          1 |
|         4 | Jan         |          0 |          4 |
|         6 | Anne        |          0 |       NULL |
+-----------+-------------+------------+------------+
3 rows in set (0.00 sec)

irgendwas machst du falsch *g*

mysql> desc friends;
+-------------+----------+------+-----+---------+-------+
| Field       | Type     | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| friend_id   | int(11)  |      |     | 0       |       |
| friend_name | char(50) | YES  |     | NULL    |       |
+-------------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> desc friend_friend;
+------------+------------------+------+-----+---------+-------+
| Field      | Type             | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+-------+
| friend1_id | int(10) unsigned |      |     | 0       |       |
| friend2_id | int(10) unsigned |      |     | 0       |       |
+------------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

[TobiaZ]

hab leider keine konsole hier, aber es ist definitiv alles gleich.

friend_id         name        befreundet      friend1_id
1                 Tom         0               1
4                 Jan         0               4

irgendwie kann ich mein ergebnis auch gut nachvollziehen...

EDIT:

---

Hab grad geguckt. Also befreundet würde bei Anna NULL sein, was irgendwo logisch erscheint.

---

[mrhappiness]

Original geschrieben von TobiaZ
hab leider keine konsole hier, aber es ist definitiv alles gleich.

irgendwie kann ich mein ergebnis auch gut nachvollziehen...

ich nicht, häng doch mal nen dump an und verrat mir mal deine mysql-version

[TobiaZ]

Version steht 3 Posts drüber... MySQL 4.0.15

SELECT f.*,
  IF(SUM(ff.friend1_id =2 OR ff.friend2_id =2) > 0,1,0) befreundet,
  ff.friend1_id
FROM friends f
LEFT  JOIN friend_friend ff 
  ON f.friend_id IN ( ff.friend1_id, ff.friend2_id )
GROUP  BY f.friend_id
HAVING befreundet = 0

So würde gehen.

Dump:

CREATE TABLE `friends` (
  `friend_id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(15) NOT NULL default '',
  PRIMARY KEY  (`friend_id`)
) TYPE=MyISAM AUTO_INCREMENT=7 ;

INSERT INTO `friends` 
VALUES (1, 'Tom'),(2, 'Frank'),(3, 'Sabine'),
  (4, 'Jan'),(5, 'Sandra'),(6, 'Anne');

CREATE TABLE `friend_friend` (
  `friend1_id` int(10) unsigned NOT NULL default '0',
  `friend2_id` int(10) unsigned NOT NULL default '0'
) TYPE=MyISAM;

INSERT INTO `friend_friend` 
VALUES (1, 3),(4, 3),(5, 2),(5, 3),(3, 2);