mehrere Unterabfragen in einem Query

[raven3k]

Hoffe es kann mir einer einen kleinen Tip geben.
Ich stehe leider gerade etwas auf dem Schlauch.

Habe bisher in meinem foreach weitere SQL Abfragen abgeschickt, dies wollte ich nun eleganter lösen.

Lasse mir eine Liste von System ausgeben und muss zu jedem System dann weitere Informationen hinzupacken wie z.B. Anzahl der freigeschalteten Nutzer.

Um es mal verkürzt darzustellen.


User

pkUser
username
confirmed​

UserSystem

fkUser
pkSystem​

System

pkSystem
shortname​

shortname | anzahl_user_mit_confirm = 1 | anzahl_user_mit_confirm = 0


Mein bisheriger versuch, klappt leider nicht so ganz. Es fehlt der Bezug zu UserSystem
SELECT shortname,(SELECT count(pkUser) FROM user WHERE confirmed=1) AS mit_confirm FROM system

 

[Dukel]

Hast du es auch mit Joins versucht?

 

[akretschmer]

test=# \d t_user
  Table "public.t_user"
  Column  |  Type  | Modifiers
-----------+---------+-----------
 pkuser  | integer | not null
 username  | text  |
 confirmed | boolean |
Indexes:
  "t_user_pkey" PRIMARY KEY, btree (pkuser)
Referenced by:
  TABLE "user_system" CONSTRAINT "user_system_fkuser_fkey" FOREIGN KEY (fkuser) REFERENCES t_user(pkuser)

test=# \d t_system
  Table "public.t_system"
  Column  |  Type  | Modifiers
-----------+---------+-----------
 pksystem  | integer | not null
 shortname | text  |
Indexes:
  "t_system_pkey" PRIMARY KEY, btree (pksystem)
Referenced by:
  TABLE "user_system" CONSTRAINT "user_system_fk_system_fkey" FOREIGN KEY (fk_system) REFERENCES t_system(pksystem)

test=# \d user_system
  Table "public.user_system"
  Column  |  Type  | Modifiers
-----------+---------+-----------
 fkuser  | integer |
 fk_system | integer |
Foreign-key constraints:
  "user_system_fk_system_fkey" FOREIGN KEY (fk_system) REFERENCES t_system(pksystem)
  "user_system_fkuser_fkey" FOREIGN KEY (fkuser) REFERENCES t_user(pkuser)

test=# select * from t_user
test-# ;
 pkuser | username | confirmed
--------+----------+-----------
  1 | max  | t
  2 | peter  | f
  3 | lisa  | t
(3 rows)

test=# select * from t_system ;
 pksystem | shortname
----------+-----------
  1 | system1
  2 | system2
(2 rows)

test=# select * from user_system ;
 fkuser | fk_system
--------+-----------
  1 |  1
  2 |  1
  3 |  2
(3 rows)

test=# select s.shortname, sum(u.confirmed::int) filter (where u.confirmed) as anzahl_confirmed, sum(case when not u.confirmed then 1 else 0 end) as user_unconfirmed from user_system us left join t_system s on s.pksystem=us.fk_system left join t_user u on us.fkuser=u.pkuser group by s.shortname;
 shortname | anzahl_confirmed | user_unconfirmed
-----------+------------------+------------------
 system2  |  1 |  0
 system1  |  1 |  1
(2 rows)

test=#

 

[raven3k]

Der Befehl "filter" war mir bis dato unbekannt... Danke!
Und an das Case hatte ich auch nicht mehr so dran gedacht.

Da arbeitet seit fast 10 Jahren mit Datenbanken und dann so etwas....

Aber ist dies wirklich noch MySQL ?

Habe es mal ein wenig angepasst.

select s.shortname, sum(case when u.confirmed = 1 then 1 else 0 end) as user_unconfirmed from user_system us left join system s on s.pksystem=us.fksystem left join user u on us.fkuser=u.pkuser group by s.shortname

 

[akretschmer]

Nein, ich verwende keine Datenbanken, die 14,5 Jahre brauchen, um einen Bug zu fixen. Ich verwende PostgreSQL. FILTER wird MySQL nicht kennen, daher habe ich das bei der zweiten Spalte mit normalem Case gemacht.

Why PostgreSQL is better than MySQL |

 

[raven3k]

Ja glaube ich dir sofort das PostgreSQL besser ist...