INNER JOIN between two SELECT

Asked

Viewed 27 times

0

I can do the INNER JOIN between two SELECT following the structure below? For the idea is to add a few more SELECT   

/* Select 1 */

        SELECT

        AVG (UR1_Demanda) as UR1_DemandaAVG,

        DATEPART(dayofyear, E3TimeStamp) as id

        FROM MTC_CF_MM 

        WHERE UR1_Demanda <> 0

        GROUP BY DATEPART(dayofyear, E3TimeStamp)

        ORDER BY id

/* Select 2 */

        SELECT

        AVG (UR2_Demanda) as UR2_DemandaAVG,

        DATEPART(dayofyear, E3TimeStamp) as id

        FROM MTC_CF_MM 

        WHERE UR2_Demanda <> 0

        GROUP BY DATEPART(dayofyear, E3TimeStamp)

        ORDER BY id

 /* JOIN  */

        SELECT *

        FROM Select1 as A 

        INNER JOIN Select2 as B ON A.id = B.id

I’m not sure how to reference/mention SELECT within the syntax of JOIN

select join

3 answers

0

Yes it is possible to do JOIN with a SELECT, in that case each SELECT will be a subquery.

For this, include the SELECT in parentheses and give him a alias (name), something like that:

SELECT A.*
  FROM (SELECT Avg (ur1_demanda)                AS UR1_DemandaAVG,
               Datepart(dayofyear, e3timestamp) AS id
        FROM   mtc_cf_mm
        WHERE  ur1_demanda <> 0
        GROUP  BY Datepart(dayofyear, e3timestamp)
        ORDER  BY id) AS A
  INNER JOIN (SELECT Avg (ur2_demanda)                AS UR2_DemandaAVG,
                     Datepart(dayofyear, e3timestamp) AS id
                FROM   mtc_cf_mm
                WHERE  ur2_demanda <> 0
                GROUP  BY Datepart(dayofyear, e3timestamp)
                ORDER  BY id) AS B ON A.id = B.id

0

The approach will depend on the bank you are using, but in general the idea is to think of relationships.

When we say that a bank is relational we say that it works with relationships, and contrary to what some people think relationships are not relationships between tables.

Suppose you have an x table as below:

Coluna - Tipo
id - number
nome - varchar
sobrenome - varchar
email - varchar

In this case something like:

SELECT DISTINCT nome, email FROM x

is a relationship.

Armed with this information we can now think about how to work relationships.

Some banks have the WITH figure, which allows creating relationships to then use in queries.

Armed with this two information we can do something like this:

WITH relacao_um as (

        SELECT

        AVG (UR1_Demanda) as UR1_DemandaAVG,

        DATEPART(dayofyear, E3TimeStamp) as id

        FROM MTC_CF_MM 

        WHERE UR1_Demanda <> 0

        GROUP BY DATEPART(dayofyear, E3TimeStamp)

        ORDER BY id

), relacao_dois as (

        SELECT

        AVG (UR2_Demanda) as UR2_DemandaAVG,

        DATEPART(dayofyear, E3TimeStamp) as id

        FROM MTC_CF_MM 

        WHERE UR2_Demanda <> 0

        GROUP BY DATEPART(dayofyear, E3TimeStamp)

        ORDER BY id

)

        SELECT *

        FROM relacao_um 

        INNER JOIN relacao_2 ON relacao_um.id = relacao_2.id

Another approach that is possible in some banks (not all will accept): is to include the direct link in JOIN:


SELECT *

FROM (
  SELECT

  AVG (UR1_Demanda) as UR1_DemandaAVG,

  DATEPART(dayofyear, E3TimeStamp) as id

  FROM MTC_CF_MM 

  WHERE UR1_Demanda <> 0

  GROUP BY DATEPART(dayofyear, E3TimeStamp)

  ORDER BY id

) as A 

INNER JOIN (
  SELECT

  AVG (UR2_Demanda) as UR2_DemandaAVG,

  DATEPART(dayofyear, E3TimeStamp) as id

  FROM MTC_CF_MM 

  WHERE UR2_Demanda <> 0

  GROUP BY DATEPART(dayofyear, E3TimeStamp)

  ORDER BY id
) as B ON A.id = B.id

I hope I’ve helped, keeping in mind that relational banks work with relationships makes life a lot easier.

0

One approach you can use is to use the operator UNION

/* Select 1 */
    (SELECT

    AVG (UR1_Demanda) as UR1_DemandaAVG,

    DATEPART(dayofyear, E3TimeStamp) as id

    FROM MTC_CF_MM 

    WHERE UR1_Demanda <> 0

    GROUP BY DATEPART(dayofyear, E3TimeStamp)

    ORDER BY id)
    
    UNION
    
    /* Select 2 */

    (SELECT

    AVG (UR2_Demanda) as UR2_DemandaAVG,

    DATEPART(dayofyear, E3TimeStamp) as id

    FROM MTC_CF_MM 

    WHERE UR2_Demanda <> 0

    GROUP BY DATEPART(dayofyear, E3TimeStamp)

    ORDER BY id)

  • And how does this Union make Join, which is the question of the question? in addition, it is important to add a note that this only works if both selects have the same number of fields/types

Browser other questions tagged select join

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