Mar 27, 2009, updated at 08:16:15 (UTC)

4406

MY SQL Abfragen kombinieren

Hallo und Guten Morgen

ich habe ein Problem mit meine SQL-Abfragen, jede einzeln für sich klappt super, ich möchte die beiden aber kombinieren, hab aber keine Ahnung wie. Es ist eine MySQL-Datenbank.

Abfrage 1 lautet

SELECT j.id as ID,j.pkey AS Issue,i.pname As Type ,prio.pname AS Prio,
CI.OLDSTRING AS 'from',  
CI.NEWSTRING AS 'to',  
CG.CREATED AS Timestamp
FROM jiraissue j, changegroup CG, changeitem CI, issuetype i,priority prio, project proj
WHERE j.ID=CG.ISSUEID AND CG.ID=CI.GROUPID
AND j.issuetype = i.ID
AND j.priority = prio.ID
and j.project = proj.id
AND j.PKEY like 'projekt_a-%'  
AND CI.FIELD = 'status'  

UNION ALL


Select j.id as ID,j.pkey as Issue, i.pname as Type, prio.pname as Prio,
'Issue Created' as 'from','Issue Created' as 'to', created as Timestamp  

from jiraissue j,issuetype i, priority prio, project proj
where j.pkey like 'projekt_a-%'  
and j.priority=prio.id
and j.issuetype=i.id
and j.project = proj.id

Order by ID desc,timestamp asc;

Abfrage 2 lautet:

SELECT j.id AS ID,MAX(b.vname) AS Solution FROM jiraissue j,nodeassociation n,projectversion b
where pkey like 'projekt_a-%'  
and n.association_type='IssueFixVersion'  
and j.id=n.source_node_id
and n.sink_node_id = b.id
GROUP BY n.source_node_id
order by j.id desc;

Abfrage 1 liefert 2541 Zeilen
Abfrage 2 liefert 702 Zeilen

In Abfrage 2 wird zu jeder ID eine Lösungsversion mitgegeben. Jede ID gibt es nur ein mal

Jetzt sollen diese Lösungsvrsionen den ID in Abfrage I zugeordnet werden.

D.h. Sind ID in Abfrage 1 und 2 gleich soll die ID mitgegeben werden

In der Gesamtabfrage müssen alle 2541 Zeilen geliefert werden.
Nur soll noch die Lösungversion (Abfrage 2) in Abfrage 1 eingebaut werden
Die ID aus Abfrage 1 die keine Lösungsversion haben sollen trotzdem aufgeführt werden

Ist das verständlich?

Please also mark the comments that contributed to the solution of the article

Content-Key: 112524

Url: https://administrator.de/contentid/112524

Printed on: April 26, 2024 at 03:04 o'clock

12 Comments

Latest comment

Hm ich glaub so könnts gehen:

SELECT j.id as ID,j.pkey AS Issue,i.pname As Type ,prio.pname AS Prio,
CI.OLDSTRING AS 'from',  
CI.NEWSTRING AS 'to',  
CG.CREATED AS Timestamp,
'Issue Created' as 'from',  
'Issue Created' as 'to',  
MAX(b.vname) AS Solution
FROM 
jiraissue j, 
changegroup CG, 
changeitem CI, 
issuetype i,
priority prio, 
project proj, 
issuetype i,
nodeassociation n, 
projectversion b
WHERE 
j.ID=CG.ISSUEID and CG.ID=CI.GROUPID
and j.issuetype = i.ID
and j.priority = prio.ID
and j.project = proj.id
and j.PKEY like 'projekt_a-%'  
and CI.FIELD = 'status'  
and n.association_type='IssueFixVersion'  
and j.id=n.source_node_id
and n.sink_node_id = b.id
GROUP BY n.source_node_id
UNION ALL
Order by ID desc,timestamp asc;

Hi godlie,

folgende Fehlermeldung:

Not unique table/alias: 'i'

Hast eine Idee was geändert werden muss?

ah ja habs grad gesehen das bei den Tables 2mal die issuetype i, drinne is gehört einmal raus.

und an welcher stelle?? ;)
Kannst den code bitte posten? ;)

Folgender Code benutzt:

SELECT j.id as ID,j.pkey AS Issue,i.pname As Type ,prio.pname AS Prio,
CI.OLDSTRING AS 'from',  
CI.NEWSTRING AS 'to',  
CG.CREATED AS Timestamp,
'Issue Created' as 'from',  
'Issue Created' as 'to',  
MAX(b.vname) AS Solution
FROM 
jiraissue j,
changegroup CG, 
changeitem CI, 
priority prio,
project proj,
issuetype i,
nodeassociation n, 
projectversion b
WHERE 
j.ID=CG.ISSUEID and CG.ID=CI.GROUPID
and j.issuetype = i.ID
and j.priority = prio.ID
and j.project = proj.id
and j.PKEY like 'projekt_a-%'  
and CI.FIELD = 'status'  
and n.association_type='IssueFixVersion'  
and j.id=n.source_node_id
and n.sink_node_id = b.id
GROUP BY n.source_node_id
UNION ALL
Order by ID desc,timestamp asc;

Folgende Fehlermeldung:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Order by ID desc,timestamp asc' at line 29

Hm das kann jetzt haarig werden.

SELECT j.id as ID,j.pkey AS Issue,i.pname As Type ,prio.pname AS Prio,
CI.OLDSTRING AS 'from',  
CI.NEWSTRING AS 'to',  
CG.CREATED AS Timestamp,
'Issue Created' as 'from',  
'Issue Created' as 'to',  
MAX(b.vname) AS Solution
FROM 
jiraissue j,
changegroup CG, 
changeitem CI, 
priority prio,
project proj,
issuetype i,
nodeassociation n, 
projectversion b
WHERE 
j.ID=CG.ISSUEID and CG.ID=CI.GROUPID
and j.issuetype = i.ID
and j.priority = prio.ID
and j.project = proj.id
and j.PKEY like 'projekt_a-%'  
and CI.FIELD = 'status'  
and n.association_type='IssueFixVersion'  
and j.id=n.source_node_id
and n.sink_node_id = b.id
GROUP BY n.source_node_id
Order by ID desc,timestamp asc;

falls das nicht zum gewünschten Effekt führt evtl ein DISTINCT nach MAX(b.vname) AS Solution einfügen.

Hi godlie,

erstmal vielen dank für deine mühe

Der Code wird ausgeführt liefert aber nicht das gewünschte Ergebnis

Jede ID taucht nur 1 mal auf. Aber es kann mehrere Einträge zu einer ID geben
Und es werden nur die ID`S angezeigt die eine Lösung haben

Ich brauch aber alle Zeilen aus Abfrage 1. Und wenn diese Eine Lösung hat dann soll diese hingeschrieben werden. Ansonsten wenn keine Lösung dann NULL
Hab ich jetzt 3 mal die selbe ID und diese ID hat die Lösung XY. dann soll 3 mal XY stehen das stört nicht

Hm da muss man dann mit Left und Right Joins arbeiten aber ohne einer Beispieldatenbank wird das schwer.
Ich schau mir das am abend mal genauer an.

Das wäre super wenn du das irgendwie hinbekommst

SELECT
    a1.*,
    a2.ID AS 'ID2'  
FROM
    ( 
{deine Abfrage1}
    ) a1 LEFT JOIN (
{deine Abfrage2}
    ) a2 ON a1.ID = a2.ID

... meinst du so ?

Hab es so gemacht:

SELECT
    a1.*,
    a2.ID
FROM
    ( 
{SELECT j.id as ID,j.pkey AS Issue,i.pname As Type ,prio.pname AS Prio,
CI.OLDSTRING AS 'from',  
CI.NEWSTRING AS 'to',  
CG.CREATED AS Timestamp
FROM jiraissue j, changegroup CG, changeitem CI, issuetype i,priority prio, project proj
WHERE j.ID=CG.ISSUEID AND CG.ID=CI.GROUPID
AND j.issuetype = i.ID
AND j.priority = prio.ID
and j.project = proj.id
AND j.PKEY like 'proja-%'  
AND CI.FIELD = 'status'  

UNION ALL


Select j.id as ID,j.pkey as Issue, i.pname as Type, prio.pname as Prio,
'Issue Created' as 'from','Issue Created' as 'to', created as Timestamp  

from jiraissue j,issuetype i, priority prio, project proj
where j.pkey like 'proja-%'  
and j.priority=prio.id
and j.issuetype=i.id
and j.project = proj.id

Order by ID desc,timestamp asc}
    ) a1 LEFT JOIN (
{SELECT j.id AS ID,MAX(b.vname) AS Solution FROM jiraissue j,nodeassociation n,projectversion b
where pkey like 'proja-%'  
and n.association_type='IssueFixVersion'  
and j.id=n.source_node_id
and n.sink_node_id = b.id
GROUP BY n.source_node_id
order by j.id desc}
    ) a2 ON a1.ID = a2.ID;

Folgender Fehler

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT j.id as ID3,j.pkey AS Issue,i.pname As Type ,prio.pname AS Prio,
CI.OLDS' at line 6

wo steht '.. ID3..' ?
und '{' und '}' müssen weg !

edit
.. as ID .. -> .. as 'ID' ...

German Question Databases Development

Hotly discussed

Check of ZFW Firewallgleixnerd - 5 Comments