MySQL Not in (alias)

[Split1989]

hallöchen,

komme mal wieder nicht weiter (kann auch an der später stunde liegen) und brauche eure hilfe.

ich will folgendes machen

(select id,name,xyz from table) as t1
UNION
(select id,name,xyz,foo,bar from table2 WHERE id not in (t1)) as t2

geht so aber leider nicht oder? gibt es da eine schöne kurze lösung um mit dem alias zu arbeiten? oder muss ich den ganzen sql statement da wieder reinhauen.

(das ganze ist jetzt natürlich sehr vereinfacht dargestellt)

Schöne Grüße
Splitiii

 

[theHacker]

IN ist für eine Liste von Werten, nicht für Spalten/Tabellen.

Du musst einen entsprechenden JOIN machen, um die Einträge von t1 mit t2 zu knüpfen. Mach einen LEFT JOIN, damit auch Treffer kommen, wo es keine Verknüpfung gibt. Diese Einträge haben NULL-Werte in der anderen Tabelle; damit erkennst du, dass sie in t2, nicht aber in t1 (oder andersrum) vorkommen.

 

[Split1989]

hmmm das ist glaube ich keine lösung für mein problem .. ich glaube ich habe zu sehr zussammengefasst


ads

ID|STATUS|GROUP_ID
1|0|1
2|0|2
3|0|1
4|1|3
5|0|3

gruppe

GROUP_ID|NAME|USER_ID
1|test|2
2|abc|3
3|xyz|2
4|foo|1
5|bar|2

user

USER_ID|NAME
1|franz
2|henriette
3|jan

ich möchte als ergebniss folgendes haben

erg | WHERE USER_ID = 2 AND STATUS = 1

GROUP_ID|USER_ID|COUNT(STATUS)|NAME
1|2|0|test
3|2|1|xyz
5|2|0|bar

hoffe du kannst mir da behilflich sein.
hatte dass mal mit einem right join gelöst aber da die tabellen t1 und t2 mehrere 1000 zeilen lang geworden sind dauert eine abfrage mit unter mehrere sekunden

 

[theHacker]

Bevor ich versuch, zu rätseln, welche Tabelle nun welche is.... du kannst sie gleich ordentlich beschriften. Da blickt ja sonst keiner durch.

 

[apolle]

Von dem Mangel an Durchblick mal abgesehen: Im Zusammenhang mit dem UNION-Operator gibt es für die SELECT-Anweisungen ein paar Einschränkungen. Beispielsweise muß die Anzahl der von den einzelnen Anweisungen zurückgelieferten Spalten gleich sein, der oben angedeuteter Code kann so nicht funktionuckeln.

select id,name,xyz from table
UNION
select id,name,xyz,foo,bar from table2

Die erste Abfrage liefert drei Spalten zurück, die zweite fünf.

https://www.w3schools.com/sql/sql_union.asp
https://dev.mysql.com/doc/refman/5.1/de/union.html

 

[Split1989]

@apolle: war natürlich mein fehler in meiner sql querry sind es natürlich gleich viele.


zum allgemeinen verständniss:

User können "Ads" und "Gruppen" erstellen.

Sie können, müssen aber nicht, Ads Gruppen zuordnen.

mit der abfrage möchte ich eigentlich nur wissen welche gruppen hat ein user und dazu die anzahl der ads die den status 1 haben.

problem ist das eine Gruppe angelegt werden kann ohne das ihr ads zugewiesen werden, in diesem fall soll die gruppe aber trotzdem auftauchen mit der anzahl = 0

entschuldigt das ich es nicht von anfang an deutlich geschildert habe.

 

[Aradiv]

SELECT 
	gruppe.GROUP_ID, 
	user.USER_ID, 
	COUNT(ads.STATUS), 
	gruppe.NAME 
FROM user 
LEFT JOIN gruppe 
	ON user.id = gruppe.USER_ID 
FULL OUTER JOIN ads 
	ON ads.GROUP_ID=gruppe.GROUP_ID
WHERE 
	ads.STATUS=1 
AND 
	user.USER_ID = ?

so würde ich das machen

hab das ganze aber ungetestet hier direkt im Forum geschrieben daher könnte da durchaus der ein oder andere fehler bei seien.

 

[Split1989]

mysql hat leider kein full outer join und das nachzubilden würde meine abfrage wieder nur zu stark aufblähen

 

[theHacker]

mit der abfrage möchte ich eigentlich nur wissen welche gruppen hat ein user und dazu die anzahl der ads die den status 1 haben.

Also Basistabelle is die User<->Gruppe.

[FONT=Courier New][B][COLOR=#9932cc]SELECT[/COLOR][/B] group_id, user_id, name
[B][COLOR=#9932cc]FROM[/COLOR][/B] gruppe[/FONT]

mit der abfrage möchte ich eigentlich nur wissen welche gruppen hat ein user und dazu die anzahl der ads die den status 1 haben.

Ersteres hast du schon Zweiteres heißt, du musst nun die Ads anjoinen.

[FONT=Courier New][B][COLOR=#9932cc]SELECT[/COLOR][/B] gruppe.group_id, gruppe.user_id, gruppe.name
       ads.id, ads.status
[B][COLOR=#9932cc]FROM[/COLOR][/B] gruppe
[B][COLOR=#9932cc]JOIN[/COLOR][/B] ads [B][COLOR=#9932cc]ON[/COLOR][/B] ads.group_id = gruppe.group_id
[B][COLOR=#9932cc]WHERE[/COLOR][/B] ads.status = 1[/FONT]

problem ist das eine Gruppe angelegt werden kann ohne das ihr ads zugewiesen werden, in diesem fall soll die gruppe aber trotzdem auftauchen mit der anzahl = 0

Im Moment fallen diese Gruppen weg, richtig. Also machen wir LEFT JOIN, damit keine Gruppe verloren geht.

[FONT=Courier New][B][COLOR=#9932cc]SELECT[/COLOR][/B] gruppe.group_id, gruppe.user_id, gruppe.name
       ads.id, ads.status
[B][COLOR=#9932cc]FROM[/COLOR][/B] gruppe
[B][COLOR=#9932cc]LEFT[/COLOR][/B] [B][COLOR=#9932cc]JOIN[/COLOR][/B] ads [B][COLOR=#9932cc]ON[/COLOR][/B] ads.group_id = gruppe.group_id
[B][COLOR=#9932cc]WHERE[/COLOR][/B] ads.status = 1[/FONT]

Fast fertig. Jetzt willstde die Ads noch gezählt haben, also gruppieren wir:

[FONT=Courier New][B][COLOR=#9932cc]SELECT[/COLOR][/B] gruppe.group_id, gruppe.user_id, gruppe.name
       [B][COLOR=#9932cc]COUNT[/COLOR][/B][COLOR=#9932cc]([/COLOR]*[COLOR=#9932cc])[/COLOR]
[B][COLOR=#9932cc]FROM[/COLOR][/B] gruppe
[B][COLOR=#9932cc]LEFT[/COLOR][/B] [B][COLOR=#9932cc]JOIN[/COLOR][/B] ads [B][COLOR=#9932cc]ON[/COLOR][/B] ads.group_id = gruppe.group_id
[B][COLOR=#9932cc]WHERE[/COLOR][/B] ads.status = 1
[B][COLOR=#9932cc]GROUP[/COLOR][/B] [B][COLOR=#9932cc]BY[/COLOR][/B] gruppe.group_id, gruppe.user_id, gruppe.name[/FONT]

Alles nur so runtergeschrieben; das sollte der Weg sein.

edit:
P.S. Die Nomenklatur deiner Tabellen lässt sehr zu wünschen übrig. Einmal Singular, einmal Plural. Tabellennamen passen nicht zu den Spaltennamen... macht nicht viel Spaß, damit zu arbeiten.

edit2:
COUNT(*) dürfte schneller sein. Hab die Abfrage geändert.

 

[transversalis]

LEFT JOIN ads ON ads.group_id = gruppe.group_id
WHERE ads.status = 1

diese Kombination ist nicht plausibel.
Hat die linke Tabelle für einen Wert keine Entsprechung in der rechten Tabelle, dann wird die Zeile der linken Tabelle genommen und mit <NULL> - Werten für die rechte Tabelle ergänzt.
Genau diese Zeilen werden durch die WHERE-Bedingung aber wieder eliminiert.
WHERE ads.status = 1 impliziert ja, dass der Wert nicht <NULL> ist. Du kannst dann von vorne herein einen INNER JOIN verwenden.

SELECT GROUP_ID, USER_ID, NAME, SUM(COALESCE(STATUS,0))    as "COUNT(STATUS)"
FROM gruppe
LEFT JOIN ads
ON gruppe.GROUP_ID = ads.GROUP_ID
WHERE gruppe.USER_ID = ?
GROUP BY GROUP_ID, USER_ID, NAME

sollte das richtige Ergebnis bringen.
( Wobei ich USER_ID aus der SELECT-Liste und damit auch aus dem GROUP BY weglassen würde, da dieser Wert bereits bekannt ist )

 

[theHacker]

Stimmt, das is Quark. Dann müsste es heißen, dass status = 1 OR status IS NULL; dann bleiben die NULL-Werte erhalten.

 

[Aradiv]

Okay stimmt da war was mit den OUTER JOINS

(
    SELECT 
        gruppe.GROUP_ID, 
        user.USER_ID, 
        COUNT(1), 
        gruppe.NAME 
    FROM user 
    LEFT JOIN gruppe 
        ON user.id = gruppe.USER_ID 
    LEFT JOIN ads 
        ON ads.GROUP_ID=gruppe.GROUP_ID
) UNION (
    SELECT 
        gruppe.GROUP_ID, 
        user.USER_ID, 
        COUNT(ads.STATUS), 
        gruppe.NAME 
    FROM user 
    LEFT JOIN gruppe 
        ON user.id = gruppe.USER_ID 
    RIGHT JOIN ads 
        ON ads.GROUP_ID=gruppe.GROUP_ID
)
WHERE 
    ads.STATUS=1 OR ads.STATUS IS NULL
AND 
    user.USER_ID = ?

So sollte es dann trotzdem gehen

Oder

SELECT
    GROUP_ID,
    USER_ID,
    (SELECT COUNT(1) FROM ads WHERE ads.GROUP_ID = gruppe.GROUP_ID) AS ANZAHL,
    NAME
FROM gruppe
WHERE USER_ID = ?
    ON user.id = gruppe.USER_ID

Hat halt ein SubQuery ist davor aber im Vergleich verhältnismäßig gut lesbar

 

[transversalis]

Funktioniert die erste Query inzwischen rein von der Syntax her ?

( SELECT1  UNION SELECT2 ) WHERE ...

Das wäre ja ein nested-table-select und den kann doch nicht jedes System.

Müsste es nicht eher

SELECT spaltenliste FROM 
   (  SELECT1  UNION SELECT2 ) correlationname  
WHERE ...

heissen, oder

WITH CTE AS ( SELECT1  UNION SELECT2 ) 
SELECT spalten FROM CTE WHERE ...

bei der zweiten Query ist das

8:    ON user.id = gruppe.USER_ID

ein Kopierfehler ?
Die zweite Query würde zudem nicht zwischen Status 0 und 1 bei den ads unterscheiden

 

[Luckyze]

Lassdoch Mal group weg und schreibe:

FROM `???_groupusers`

FROM `???_groups`