Kriege bestimmte Suchabfrage nicht hin

[Beinschiene]

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.

 

[castorp]

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:

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").

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

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]

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!

 

[castorp]

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:

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]

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

 

[castorp]

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.

 

[akretschmer]

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

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 ;-)

 

[Beinschiene]

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?

 

[castorp]

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.

 

[Beinschiene]

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?

 

[castorp]

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

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

 

[Beinschiene]

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?

 

[Beinschiene]

(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