MySQL: Ein paar Query Probleme...

[Kuma]

Hey, wir machen in einer LV gerade sowas wie ein Social Network und wir müssen eben ein paar Basis Querys anwenden (können).
Habe da leider einige Probleme vielleiche kann mir ja jemand weiterhelfen.

Schema:
person(email,......)
freundlink(email,emailfreund,bis...)
hasstlink(email,emailhasst, von) //die Person mit email hasst die Person mit emailhasst

Ich muss jetzt mal auf Fehler überprüfen unter anderem wenn eine person zur gleichen zeit mit einer anderen befreundet/verhasst ist. (Subquerys sind nicht zulässig)

Mein query, welcher nix zurückliefert:

create view as fail(email1,email2) as select p1.email.p2.email
from person p1,person p2, freundlink f ,hasstlink h
where p1.email=f.email and p2.email=h.emailfreund and   //selektiere alle die miteinander befreundet sind
p1.email=h.email and p2.email=h.emailhasst    //selektiere davon alle wo p1, p2 hasst
and h.von < f.bis;

erstellen....personen die sich zur selben zeit nicht mögen:

from person p1,person p2, hasstlink h
where p1.email=h.email and p2.email=h.emailhasst and
p2.email=h.email and p1.email=h.emailhasst and

//keine Ahnung wie ich die Zeitabfrage machen soll


gib alle Personen aus, welche eine Person nicht mögen diese Person sie aber schon mag.

from person p1, person p2. hasstlink h, freundlink f 
where p1.email=f.email and p2.email=f.emailfreund  //selektiere alle paare die befreundet sind
and p2.email=h.email and p1.email = h.emailhasst  //selektiere die, die den der sie mag, hasst

Also, ich habe mir die drei Sachen mal als Mengen Diagramm aufgezeichnet, jedoch finde ich meinen Fehler nicht.
Vom Prinzip her sind sie für mich korrekt.


Und dann soll ich noch überprüfen ob es drei Personen gibt, die sich als gegenseitig als Freunde angegeben haben, bzw sich hassen.
Jedoch habe ich da keinen Ansatz.

 

[Hancock]

where p1.email=f.email and p2.email=h.emailfreund and

h sollte doch f sein, oder?

Drei Personen Abfrage: du musst 6 Bedingungen über drei Personen prüfen.
Kurz:

select sum(1) as Count from f1,f2,f3
where f1 likes f2 and f2 likes f3
Group by f1.mail having Count=5

 

[Kuma]

h sollte doch f sein, oder?

Ja, ist es in meiner Abfrage auch.

Kurz:........

Die Frage ist genau: die sich paarweise und gegenseitig als Freunde angegeben haben.
Dh einfach, dass alle miteinander befreundet sind müssen bzw sich hassen?
Also:
where p1.email=f.email and p2.email=f.emailfreund and p2.email=f.email and p1.email=f.email and ...........
So richtig?

 

[Hancock]

Ja, sollte so gehen.

Der Query, der nix zurückgibt: Bedingungen streichen, bis was kommt und gucken ob das passt, sonst andere streichen. So merkt man, wo's nicht stimmig ist und kann da nachgucken.

Zeit:
start1<ende2 &&ende1>start2

 

[Kuma]

Also die create view passt glaub ich (vielleicht liegt es auch daran, dass in hasst nur 17 Einträge sind), mich würde nur interessieren wie ich das datum abfragen muss, doch so dass die hasst von kleiner ist als freund bis?

Bei der Dreiecksabfrage habe ich jetzt schon sechs Überprüfungen ob sie befreundet sind, was auch nix zurückgibt....
Kann ich es irgendwie einfach machen, dass es mir auch zurückgibt welche sich hassen, ohne wieder sechs Vergleiche zu machen?

 

[Hancock]

Wenn sich hassen und befreundet sein genau gleich aufgebaut ist, musst du nur Codeersetzung vornehmen.

Was heißt bei dir "nix zurückgeben", wenn du die View erstellst? (0 Rows, Fehler, kein Fehler...)
Ich bastele mir die selects immer erst z.B. in phpMyAdmin zusammen und generiere erst dann die View, da sehe ich Fehler viel schneller.

 

[omaliesschen]

edit: sorry. Ich trink noch nen Kaffee und schaus mir dann nochmal an...

Ergänzung (28. April 2013)

Schema:

Tabelle 1 
Name: Person

Column1 : Email


Tabelle 2
Name: Friends

Column1 : Email
Column2 : Email_from_friend
Column3 : Friend_until


Tabelle 3
Name: Enemy

Column1 : Email
Column2 : Email_from_enemy
Column3 : Enemy_since

Aufgabe:
Gemäß der Vorgabe der Umgang mit Impliziten Joins zum feststellen möglicher Fehler. Sicher?

Fehlermöglichkeiten:

1. Person X ist Freund von Y, Y allerdings ist kein Freund von X
2. Datum: Enemy_since < Friend_until
3. X und Y sind befreundet, wobei Friend_until (friend with Y) von X identisch mit Friend_until (friend with X) von seinem Freund Y ist
4. Wie 3 nur im Bezug auf die Tabelle Enemy
5. 3 Personen Korrelation
6. was war noch möglich??

 

[Kuma]

@Hancock:
Ja, ich teste zuvor auch immer mit normalen Select.
Mit nix zurückgegeben meine ich, dass ich die leere Menge zurück bekomme und dass bei allen vier Querys...
Fehler kommt nicht.
Aber wie ich oben schon geschrieben habe kann es wohl auch daran liegen, dass nur ~20 Einträge in hasst ist und so einfach nix matcht.

@liesschen
In Person sind noch ein paar Felder drin, aber die werde ich für diese Joins mM nicht benötigen, sind nur Geburtstag, Wohnort usw.

Gemäß der Vorgabe der Umgang mit Impliziten Joins zum feststellen möglicher Fehler. Sicher?

Es darf wohl doch "alles" verwendet werden, also auch Subquerys.

Naja "Fehlermöglichkeiten" Es sind halt alles einzelne Querys zu schreiben.

1.Aufgabe:
Alle Personenpaare ausgeben, wenn freund und hasst sich zeitlich "überlappen".
Jedoch darf es so sein, dass x y mag jedoch y x nicht.

3. X und Y sind befreundet, wobei Friend_until (friend with Y) von X identisch mit Friend_until (friend with X) von seinem Freund Y ist

Verstehe nicht was du meinst.

4.) Personenpaare, die sich zur selben Zeit hassen.

6.)Personenpaare, wo A B mag, zur gleichen Zeit B aber A hasst.

5.)Personentriple überprüft ob 3 Personen gibt, die sich paarweise und
gegenseitig als Freunde angegeben haben oder hassen.

 

[Hancock]

Also mit meiner Testdb:
Test, ob jemand mit einer Person befreundet ist, die ihn hasst

SELECT * FROM friend f,enemy e WHERE f.from=e.to and f.to=e.from
[CODE]
Beide mögen sich grade nicht
[CODE]SELECT * FROM enemy e1,enemy e2 where e1.from=e2.to and e2.from=e1.to

Personen, die gemocht werden, es aber nicht zurückgeben:

SELECT f1.from as likes,f1.to `likes not back` FROM friend f1 left join friend f2 on f1.to=f2.from where f2.from is null

Tripplets:

SELECT *,sum(1) as c FROM friend f1,friend f2,friend f3 WHERE f1.to=f2.from and f2.to=f3.from Group by f1.from having c=8

CREATE TABLE IF NOT EXISTS `enemy` (
`from` int(11) NOT NULL,
`to` int(11) NOT NULL,
`since` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `enemy` (`from`, `to`, `since`) VALUES
(2, 1, 4),
(1, 5, 3),
(5, 1, 4);

CREATE TABLE IF NOT EXISTS `friend` (
`from` int(11) NOT NULL,
`to` int(11) NOT NULL,
`until` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `friend` (`from`, `to`, `until`) VALUES
(1, 2, 3),
(1, 3, 4),
(2, 1, 2),
(4, 1, 2),
(5, 3, 1),
(3, 1, 0),
(3, 2, 0),
(2, 3, 0);

 

[Kuma]

Hmm okay, danke.
Die ersten habe ich auch so gemacht, nur das dritte verstehe ich nicht so ganz.

SELECT *,sum(1) as c FROM friend f1,friend f2,friend f3 WHERE f1.to=f2.from and f2.to=f3.from Group by f1.from having c=8

sum(), wozu?
having c=8, was soll das sein?

Ahso, zur Angabe: Heißt das so viel, wie das ich transitivität überprüfen soll? a mit b , b mit c , c mit a? Irgendwie werde ich daraus nicht so ganz schlau.

 

[Hancock]

OK, der Query ist auch echt nicht ganz einfach^^, wollt es halt ohne subquery lösen ... und muss feststellen, dass er totaler Murks ist, vergess ihn besser schnell...

OK: Bessere Variante:

SELECT * FROM friend f1,friend f2,friend f3,friend f4,friend f5,friend f6 WHERE 
-- Ring vorwärts
f1.to=f2.from and 
f2.to=f3.from and 
f3.to=f1.from and
--Ring rückwärts 
f4.to=f5.from and
f5.to=f6.from and
f6.to=f4.from and
--Gleiche Freunde
f4.from=f1.from and
f5.from=f3.from and
f6.from=f2.from
-- Bedingung zur Unterscheidung zwischen "vorwärts" und "rückwärts"
and f2.to<f6.to

Idee: Ich kann ein Dreieck in der Form 1,2,3 und in der Form 1,3,2 durchlaufen, wenn ich ein Ringschluss erreichen will. Kann ich ihn mit den gleichen Leuten in beide Richtungen durchlaufen, hab ich einen, will ich nur eine Ausgabe, muss ich nur eine Bedingung einfügen, was vorwärts ist.

 

[yxcv]

Hallo Kuma,

wie schon oft von mir erwähnt, sollten Tabellen immer per "JOIN" verbunden werden.
Unverbesserliche meine "Quatsch, im WHERE sieht's auch toll aus!"

Das du keine Daten bekommst wundert mich nicht.
Denn Tabellenverknüpfungen per "WHERE" werden als "INNER JOIN" berücksichtigt.
Es müssten sich also Leute gleichzeitig hassen und nett finden...

Um die Aufgabe zu lösen, kurz in die Funktionsweise von "LEFT JOIN" und "INNER JOIN" einlesen.
Außerdem verbessert dies zusätzlich die Übersicht-, und Verständlichkeit.

Falls du dann nicht weiterkommen solltest, gib Mal bitte konkrete Datensätze.

 

[Kuma]

@Hancock
Okay, hab ihn eh nicht verstanden ^^
Also ich hab das jetzt mal einfach so gelöst wie ich es oben schon gesagt habe. Sechs Vergleiche auf Freund und dann ein union auf 6x hasst.

@yxcv
Ja, das es mehrere Möglichkeiten für Joins gibt ist klar.
Nur verstehe ich nicht was ich in dem Fall mit left Join machen soll, so wie ich das verstehe bekomme ich da halt alles von der linken Tabelle/Query, dass passt doch für meine Aufgabe gar nicht.

Emm, ich hatte noch ein paar Fragen, die mir gerade entfallen sind...frage nachher nochmal.

 

[Kuma]

AW: MySQL:Gruppierungen / Abzählungen

Okay, die union hat so gepasst.
Der Prof hat nur gemeint, dass man das mit zwei Views machen könne.

Jedenfalls ist mir meine Frage wieder eingefallen.

Wann genau verwendet man Group by?
So wie ich das verstehe, nimmt man es wenn man z.B eine Table Abteilung und eine Mitarbeiter hat.
Und dann will ich wissen wie viele Leute in einer Abteilung arbeiten, dann würde ich mall abfragen wer in der Abteilung arbeitet und da ich es nach der group by gruppiere erhalte ich alle Abteilungen aufgelistet mit der MA Anzahl.
Passt so ungefähr?

Was genau ist der Unterschied von Foreign Key References und nur References? Bei Google finde ich dazu nix.
Ich habe mir vorgestellt, dass FK Ref bedeuted, dass auf den Wert, welchem referenziert wird dort ein PK ist und bei References nur ein normaler Wert?

Wenn ich, alle Mitarbeiter ausgeben möchte und zusätzlich wenn dieser MA eine Abteilung leitet, die Abteilung.
Wäre doch ein left Join geeignet?
Immerhin brauche ich zuerst nurmal die Gruppe ausgeben und wenn der Typ kein Leiter ist wird NULL ausgegeben.

Also: select * from MA m left Join Abt a where m.email=a.leitermail; // IRgendwas passt da aber nicht

Okay, ich möchte nun vom obigem Schema ausgeben lassen, wer die meisten Freunde hat sowie wer genauso viele Freunde hat wie Johann Bach.

1.)

select from p.email from person p, freundlink f where p.email=f.email and count(*) limit 1

//geht nicht

2.)

select from p.email from person p, freundlink f where p.vn="j" and p.nn="B" count(*)

Nein, okay keine Ahnung. Wie kann ich das zählen mit wie vielen der befreundet ist?
Wie kann ich die anzahl, die ich da rausbekomme weiterverwenden, wenn ich überhaupt eine Anzahl rausbekomme?
Eigentlich könnte ich ja dann die 2. Abfrage mit einem Subquery machen, nur wie komme ich auf das count von johann?

 

[Hancock]

Group by: Mehrere Zeilen "zusammenschieben", wobei sinnvollerweise irgendein Wert aufaddiert/... wird.

Foreign-Key Reference müsste das sein, wo gleich auch noch ein constraint dabei ist, also ungültige Einträge verboten sind.
Reference könnte alles sein, z.B. ein Datum (auch wenns imperformant ist), und sie muss nicht unbedingt auflösbar sein.

left join: ersetzte "where" mit "on"

1.)

select *,p.email from Person p,freundlink f where p.email=f.email Group by p.email order by Count(*) desc Limit

2.)
Mit subquery

select *,Count(*) as c from Person p,freundlink f where p.email=f.email and c=(select Count(*) from freundlink f2 where p2.email=f2.email and f2.email='Johann.sebastian.bach') Group by p.email

Ohne subquery: Wieder mehrere Joins