1. Willkommen im Forum für alle Datenbanken! Registriere Dich kostenlos und diskutiere über DBs wie Mysql, MariaDB, Oracle, Sql-Server, Postgres, Access uvm
    Information ausblenden

Kriege bestimmte Suchabfrage nicht hin

Dieses Thema im Forum "PostgreSQL" wurde erstellt von Beinschiene, 25 Oktober 2018.

  1. Beinschiene

    Beinschiene Benutzer

    Hallo, Leute!

    Ich habe folgendes Problem. Arbeite mit PSQL über die Shell und kriege Punkt 4 einfach nicht hin.

    select distinct suppliers.sid from suppliers, parts, catalog where suppliers.sid = catalog.sid and ((catalog.pid = 4 or catalog.pid =5) and (catalog.pid = 3 or catalog.pid=2));

    Die ersten 3 Punkte konnte ich so ähnlich lösen, nur bei Punkt 4 komme ich einfach nicht weiter.
    Sehr wsl denke ich wieder nur zu kompliziert.
    Vielen Dank im Voraus für die Hilfe!




    Excercise 1. Consider the following database schema:

    create table if not exists Suppliers (sid integer primary key, sname varchar, address varchar);
    create table if not exists Parts(pid integer primary key, pname varchar, color varchar);
    create table if not exists Catalog(sid integer, pid integer, cost real, primary key(sid, pid));

    insert into Suppliers values(1, 'East Co.', 'Brigittenau'), (2, 'West GmbH', 'Hietzing'), (3, 'Nord Inc.', 'Doebling');
    insert into Parts values(1, 'Skypart', 'blue'), (2, 'Woodpart', 'green'), (3, 'Grasspart', 'green'), (4, 'Sunpart', 'red'), (5, 'Firepart', 'red');
    insert into Catalog values (1, 4, 120), (1, 5, 223), (1, 3, 523), (2, 2, 499), (2, 3, 320), (3, 1, 356), (3, 2, 650), (3, 3, 586), (3, 4, 184), (3, 5, 302);


    1. Find the names of suppliers who supply some red part.
    2. Find the ids of suppliers (column sid) who supply some red or green part.
    3. Find the ids of suppliers who supply some red part or are located in Hietzing
    4. Find the ids of suppliers who supply some red part and some green part.
    5. Find pairs of ids such that the supplier with the first id charges more for some part than the supplier with the second id.
    6. Find the ids of parts (pids) supplied by at least two different suppliers.
     
  2. castorp

    castorp Fleissiger Benutzer

    Das Problem nennt sich "Relational Division" und wir üblicherweise über ein GROUP BY gelöst.

    Schritt 1: Finde all Supplier die rote oder grüne Produkte verkaufen:
    Code:
    select distinct c.sid
    from parts p
      join catalog c on c.pid = p.pid
    where p.color in ('red','green');
    Da sind aber auch welche dabei, welche nur grüne produkte verkaufen. Diese müssen wir rausfiltern. Das machen wir indem wir zählen wieviele verschiedene Farben in den Produkten auftauchen - das sollte genau zwei sein ("rot" und "grün").

    Code:
    select c.sid
    from parts p
      join catalog c on c.pid = p.pid
    where p.color in ('red','green')
    group by c.sid
    having count(distinct p.color) = 2
    Voila - nur die Supplier IDs welche rote und grüne Produkte verkaufen. Das DISTINCT brauchen wir nicht mehr weil wir ein GROUP BY verwenden.

    Wenn man dazu die Information aus der Supplier Tabelle haben will, kann man das obige Ergebnis in einem Sub-Select verwenden

    Code:
    select *
    from suppliers s
    where s.sid in (select c.sid
                    from parts p
                      join catalog c on c.pid = p.pid
                    where p.color in ('red','green')
                    group by c.sid
                    having count(distinct p.color) = 2);
     
    Beinschiene gefällt das.
  3. Beinschiene

    Beinschiene Benutzer

    Wow, vielen Dank für die Supererklärung! Werde es mir nochmal durch den Kopf gehen lassen, da ich mit dem Befehl join noch nicht so vertraut bin.
    Ich versuche mal selbstständig auf eine Lösung ohne Group by zu kommen, da der Befehl nicht in meine Unterlagen vorkommt.
    Nochmals vielen Dank , zerbreche mir schon ewig den Kopf darüber! :)
     
  4. castorp

    castorp Fleissiger Benutzer

    Ohne GROUP BY geht das auch, ist aber nicht so effizient. Letztendlich muss man die Bedingung "verkauft rote UND verkauft grüne Produkte" mit zwei EXISTS Bedingungen formulieren die mit AND verknüpft werden:

    Code:
    select*
    from suppliers s
    where exists (select *
                    from parts p
                      join catalog c on c.pid = p.pid
                    where c.sid = s.sid
                      and p.color = 'red')
    and exists (select *
                    from parts p
                      join catalog c on c.pid = p.pid
                    where c.sid = s.sid
                      and p.color = 'green');
    Für das EXISTS wird ein sog. co-related sub-query verwendet. Für jede Zeile des äußeren SELECTs (select ... from suppliers) wird geprüft ob es mindestest einen Datensatz gibt der der Bedingung im sub-select genügt. Dadurch, dass beide EXISTS mit AND verknüpft werden, werden nur die Supplier geliefert auf die beide Bedingungen zutreffen.
     
    Beinschiene gefällt das.
  5. Beinschiene

    Beinschiene Benutzer

    select distinct c.sid
    from parts p
    join catalog c on c.pid = p.pid
    where p.color = 'red' and p.color= 'green';

    Wenn ich das so eingebe gibt es mir 0 rows aus. Offensichtlich falsch, aber warum?
    Verstehe ich die Suchabfrage falsch? Glaubt SQL das etwas rot und grün gleichzeitig sein muss?
    Dachte zuerst das Sql beispielsweise zuerst Rot absucht und sagt quasi check und merken, und auch merkt grün kommt auch vor und dass alles unter der selben ID nummer-->Ausgabe
     
  6. castorp

    castorp Fleissiger Benutzer

    Die Bedingung p.color='red' and p.color='green' sagt, dass die Spalte color sowohl den Wert 'red' als auch (gleichzeitig) den Wert 'green' haben soll. Nachdem aber in einer Spalte nur ein Wert drinstehen kann, kann die Bedingung niemals wahr sein.
     
    Beinschiene gefällt das.
  7. akretschmer

    akretschmer Datenbank-Guru

    andere Lösung, die auf den Array-Features von PG basiert, wäre:

    Code:
    select * from (select c.sid, array_agg(p.color) from parts p join catalog c on c.pid=p.pid where p.color in ('red','green') group by c.sid) tmp where array_agg @> array['red','green']::varchar[];
     sid |       array_agg       
    -----+-----------------------
       1 | {red,red,green}
       3 | {green,green,red,red}
    (2 rows)
    
    Grüße aus Lissabon von der pgconf.eu ;-)
     
  8. Beinschiene

    Beinschiene Benutzer

    Vielen Dank für die bisherigen Antworten! Ich merke gerade wie unvollständig die Unterlagen der Uni sind.
    Nach längeren Recherechen im Internet verstehe ich endlich die group by und having Kombination!

    Kann mir wer bei Punkt 5 weiterhelfen? Es muss nicht unbedingt gleich die Lösung sein. Aber ich verstehe nicht einmal wie die Ausgabe aussehen könnte.
    Könnte ich ein paar Tipps bekommen? Bzw. komme ich mit den jetzigen erwähnten Befehlen aus?
     
  9. castorp

    castorp Fleissiger Benutzer

    Dafür brauchst Du einen sog. "Self Join" - Du musst die "catalog" Tabelle mit sich selber joinen. Das geht genauso wie mit zwei verschiedenen Tabellen nur dass Du gezwungen bist in beiden Fällen eine Alias zu verwenden. Die join Bedingung muss dann über die Part ID gehen und wird auch eine Bedingung enthalten mit der sicher gestellt ist, dass es zwei verschiedene Supplier sind (Tip: eine Join Bedingung muss nicht notwendigerweise ein = sein!)

    Das sollte im Prinzip mit dem machbar sein, was Du bis jetzt kennst.
     
  10. Beinschiene

    Beinschiene Benutzer

    Try to solve the queries above only with the basic operators (projection, selection, cartesian product / join & set operators), and not with aggregation / subqueries

    Habe ich gerade erst entdeckt als Anforderung. :/
    Punkt 5 und 6 konnte ich wirklich mit Self.Join lösen. Punkt 4 sehe ich auch keine Möglichkeit es ohne subqueries zu lösen. Oder wie seht ihr das?
     
  11. castorp

    castorp Fleissiger Benutzer

    Das "some" in 4) macht die Sache einfacher:

    Code:
    select distinct c.sid
    from catalog c
      join parts p on p.pid = c.pid
    where p.color in ('red', 'blue');
     
    Beinschiene gefällt das.
  12. Beinschiene

    Beinschiene Benutzer

    Code:
    select distinct c.sid
    from catalog c
    join parts p on p.pid = c.pid
    where p.color in ('red', 'blue');

    Zeig mir ohne Duplizierung die sid von catalog an von der tabelle die über pid von part und pid von catalog verknüpft wird wo aber die color von parts rot oder blau ist? So lese ich gerade den code. Das Ergebnis ist richtig aber ich versteh nicht warum das so funktioniert?
     
  13. Beinschiene

    Beinschiene Benutzer

    (select sid from catalog where pid = 4 or pid = 5) intersect (select sid from catalog where pid =3 or pid =2);
    Ich denke ich habs. Hab das in meinen Unterlagen gefunden
     
Die Seite wird geladen...

Diese Seite empfehlen

  1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden