SQL: How can I get a UniqueIdentifier Column in this query

user_8695970 0
Viewed 33

Some background explanations:
I have some tables and want to get the lowest date of each Position. A Position has multiple departments in which the dates are stored.

Sample:
Position 1 has
Department date 1: 30.05.2019,
Department date 2: 01.06.2019,
Department date 3: 05.06.2019.... and so on

I have a problem to get the UniqueIdentifier Column id_abteilungen in the following output:

Result of Query

The used Query to get the above result is this:

SQL

SELECT tbl_Auftrag.Auftragsnummer
     , tbl_Positionen.Bezeichnung
     , MIN(tbl_Positionen_Abteilungen.Abteilungstermin) AS Abteilungstermin
FROM tbl_Auftrag 
INNER JOIN tbl_Positionen 
ON tbl_Auftrag.auftrag_id = tbl_Positionen.id_auftrag 
INNER JOIN tbl_Positionen_Abteilungen 
ON tbl_Positionen.positionen_id = tbl_Positionen_Abteilungen.id_positionen
WHERE tbl_Positionen_Abteilungen.fertiggestellt = '0'
GROUP BY tbl_Positionen_Abteilungen.id_positionen
       , tbl_Auftrag.Auftragsnummer
       , tbl_Positionen.Bezeichnung

To get this result I needed to GROUP BY id_positionen. As soon as I try to GROUP BY id_positionen, id_abteilungen
the result will show all Positions with all departments and their dates again.

Output with GROUP BY id_positionen, id_abteilungen

The achieved result has to be like this but I simply dont know how to get this result:

Output I wish like to have

If there are questions regarding the Tables and the primary and foreign keys, or may something different you need to know - feel free to ask. All I need is the correct query to get the id_abteilungen Unique Identifier, beside the other columns shown in Result of Query image, of each lowest date found in a position.

mysql sql
1 Answers

You don't shoudl use tbl_Positionen_Abteilungen.id_positionen in group by
instead you should select your ersult joining a subqiery for min Abteilungstermin 

    select  * 
    FROM tbl_Auftrag 
    INNER JOIN tbl_Positionen ON tbl_Auftrag.auftrag_id = tbl_Positionen.id_auftrag 
    INNER JOIN tbl_Positionen_Abteilungen ON tbl_Positionen.positionen_id = tbl_Positionen_Abteilungen.id_positionen
    INNER JOIN  (
        SELECT tbl_Auftrag.Auftragsnummer
            , tbl_Positionen.Bezeichnung
            , MIN(tbl_Positionen_Abteilungen.Abteilungstermin) AS Abteilungstermin
        FROM tbl_Auftrag 
        INNER JOIN tbl_Positionen ON tbl_Auftrag.auftrag_id = tbl_Positionen.id_auftrag 
        INNER JOIN tbl_Positionen_Abteilungen ON tbl_Positionen.positionen_id = tbl_Positionen_Abteilungen.id_positionen
        WHERE tbl_Positionen_Abteilungen.fertiggestellt = '0'
        GROUP BY  tbl_Auftrag.Auftragsnummer, tbl_Positionen.Bezeichnung
    ) t on t.Auftragsnummer = tbl_Auftrag.Auftragsnummer 
        and t.Bezeichnung =  tbl_Positionen.Bezeichnung 
        and t.Abteilungstermin = tbl_Positionen_Abteilungen.Abteilungstermin
    WHERE tbl_Positionen_Abteilungen.fertiggestellt = '0'
user_35223120
Related