Join two Mariadb SQL queries

Asked

Viewed 47 times

0

Two tables tab_Ord_Ser and tab_Pos_Alb. In two different queries I can know the values but I’m not able to unite them in just one. Are they:

SELECT
    Prazo_Producao as Data, 
    COUNT( `Numero OS` ) AS Produzir,
    Tipo_Album 
FROM
    tab_Ord_Ser 
WHERE
    Prazo_Producao >= "2018-03-01" 
GROUP BY
    Prazo_Producao,
    Tipo_Album

and

SELECT
    tab_Pos_Alb.`Data`,
    COUNT( tab_Pos_Alb.`Numero OS` ) AS Produzido,
    tab_Ord_Ser.Tipo_Album 
FROM
    tab_Ord_Ser
    INNER JOIN tab_Pos_Alb ON tab_Ord_Ser.`Numero OS` = tab_Pos_Alb.`Numero OS` 
WHERE
    ( tab_Pos_Alb.STATUS = "15 - Limpeza Saída" OR tab_Pos_Alb.STATUS = "46 - Finalizado" ) 
    AND tab_Pos_Alb.`Data` >= "2018-03-01" 
GROUP BY
    tab_Pos_Alb.`Data`,
    tab_Ord_Ser.Tipo_Album

Someone can help me?

  • what you have tried?

  • I have tried to use sub-consultations but it goes a little beyond my knowledge, despite having researched you enough I could not build the query

1 answer

0

You can use the UNION ALL:

SELECT
    Prazo_Producao as Data, 
    COUNT( `Numero OS` ) AS Produzir,
    Tipo_Album 
FROM
    tab_Ord_Ser 
WHERE
    Prazo_Producao >= "2018-03-01" 
GROUP BY
Prazo_Producao,
Tipo_Album

UNION ALL

SELECT
    tab_Pos_Alb.`Data`,
    COUNT( tab_Pos_Alb.`Numero OS` ) AS Produzido,
    tab_Ord_Ser.Tipo_Album 
FROM
    tab_Ord_Ser
    INNER JOIN tab_Pos_Alb ON tab_Ord_Ser.`Numero OS` = tab_Pos_Alb.`Numero OS` 
WHERE
    ( tab_Pos_Alb.STATUS = "15 - Limpeza Saída" OR tab_Pos_Alb.STATUS = "46 - Finalizado" ) 
    AND tab_Pos_Alb.`Data` >= "2018-03-01" 
GROUP BY
tab_Pos_Alb.`Data`,
tab_Ord_Ser.Tipo_Album

But never forget that the UNION ALL requires that the number of columns of both tables be the same.

  • unfortunately the amount of columns are not the same

  • @Márciojosérosa even if you perform a query in tables with different structures (assuming that one has three columns and the other five columns for example), its select should bring the same amount of columns to the UNION ALL work: SELECT col1, col2 FROM tab1 UNION ALL SELECT col3, col4 FROM tab2. Note that they are separate tables but the query selects the same amount of columns between such tables.

  • A brief explanation here

  • I must be missing something because the result is not expected

Browser other questions tagged

You are not signed in. Login or sign up in order to post.