Join 2 consultations

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
Post Reply
marquesjr
Rank: Analista Júnior
Rank: Analista Júnior
Posts: 77
Joined: Wed, 05 Dec 2007 10:51 am
Location: Fortaleza - CE

Good night people.

How can I join (via CD_Product) the result of these 2 consultations? So that the resulting SELECT is the fields?

Select all

a.CD_PRODUTO, a.CD_CONTAGEM, SUM(h.QT_ORCAMENTARIO)

Select all

 
SELECT 
    a.CD_PRODUTO, 
    a.CD_CONTAGEM 
FROM 
    ITCONTAGEM a 
WHERE 
    a.CD_CONTAGEM = 6551 

Select all

 
SELECT 
    h.CD_PRODUTO, 
    SUM(h.QT_ORCAMENTARIO) 
FROM 
    COPIA_ESTOQUE h 
WHERE 
    h.CD_CONTAGEM = 6551 
GROUP BY 
    h.CD_PRODUTO 
User avatar
stcoutinho
Moderador
Moderador
Posts: 850
Joined: Wed, 11 May 2011 5:15 pm
Location: são Paulo - SP

Hello Marquesjr,

From what I understand, what you really need to do is relate the two queries (and not unite).

In SQL, you can treat these two queries as if they were "views" .. and do select and joints with them.

You could do something like:

Select all

padrão "SQL antigo": 
 
SELECT <colunas> 
  FROM  (<primeira querie>) A, 
        (<segunda querie>) B 
 WHERE  <condicoes> 
 
NOVO padrão "ANSI": 
 
SELECT <colunas> 
  FROM  (<primeira querie>) A 
   JOIN (<segunda querie>) B 
     ON (<condicoes>) 
 
 
Only you need to take some care:
[ (99] a) Columns with AVG, count, sum, etc. Need to have an associated alias / header
b) the two queries you raised .. they return exactly the same amount of products? If you do not return, you need to evaluate if at least one of them presents all products and use an Outer Join (which could be a Left Outer Join or a Right Outer Join - "in the ANSI standard" - or the use of (+) in the old Oracle standard)
c) If none of the two queries feature all products and you need a complete relationship, you may have to add a third table (eg products) in your queries.

Hugs,

Sergio Coutinho
marquesjr
Rank: Analista Júnior
Rank: Analista Júnior
Posts: 77
Joined: Wed, 05 Dec 2007 10:51 am
Location: Fortaleza - CE

Stcoutinho, my access is only inquiry I do not have privileges to create visions.
User avatar
stcoutinho
Moderador
Moderador
Posts: 850
Joined: Wed, 11 May 2011 5:15 pm
Location: são Paulo - SP

Hello Marquesjr,

In fact, you do not need to create any view.

What I'm trying to explain is that you can use sub-queries as if they were "views".

Example: If you have a name "vw_dual" view whose queries would be

Select all

SELECT 1 TOTAL FROM DUAL;
you can do the same query queries of two Ways:

Select all

SELECT TOTAL FROM VW_DUAL

Select all

SELECT TOTAL FROM (SELECT 1 TOTAL FROM DUAL);
Note that in the "second option" I am not using the "view" but a "sub-querie".

Your queries you posted here can be consulted in the same way. Put it in parentheses, place aliases / nicknames in them and mount a SQL that do join between these two results.

Search a little more about sub-queries and how to apply it in SQL. As I said in the previous explanation, you can solve everything in a querie, and without the need to create views or other objects.

Hugs,

Sergio
User avatar
stcoutinho
Moderador
Moderador
Posts: 850
Joined: Wed, 11 May 2011 5:15 pm
Location: são Paulo - SP

Hello Marquesjr,

I searched a little more and the correct term would be "in-line view".
But it is not a view like that we usually create in the database.
See if this link clarifies your doubt: http://www.orafaq.com/wiki/Inline_view
Hugs,

Sergio
marquesjr
Rank: Analista Júnior
Rank: Analista Júnior
Posts: 77
Joined: Wed, 05 Dec 2007 10:51 am
Location: Fortaleza - CE

Stcoutinho, looking here I did this:

Select all

 
SELECT 
    CONTAGEM, 
    PRODUTO, 
    SUM(QT_ESTOQUE)   AS QT_APOS, 
    SUM(QT_ORCAMENTO) AS QT_ANTERIOR 
FROM 
    ( 
        SELECT 
            a.CD_CONTAGEM    CONTAGEM, 
            a.CD_PRODUTO     PRODUTO, 
            SUM(a.QT_ESTOQUE)QT_ESTOQUE, 
            NULL             QT_ORCAMENTO 
        FROM 
            ITCONTAGEM a 
        WHERE 
            a.CD_CONTAGEM = 6551 
        GROUP BY 
            a.CD_CONTAGEM, 
            a.CD_PRODUTO, 
            a.QT_ESTOQUE 
        UNION 
        SELECT 
            h.cd_contagem          CONTAGEM, 
            h.CD_PRODUTO           PRODUTO, 
            NULL                   QT_ESTOQUE, 
            SUM(h.QT_ORCAMENTARIO) QT_ORCAMENTO 
        FROM 
            COPIA_ESTOQUE h 
        WHERE 
            h.CD_CONTAGEM = 6551 
        GROUP BY 
            h.cd_contagem, 
            h.CD_PRODUTO, 
            h.QT_ORCAMENTARIO) 
GROUP BY 
    CONTAGEM, 
    PRODUTO 
endrigocm
Rank: Estagiário Júnior
Rank: Estagiário Júnior
Posts: 1
Joined: Thu, 09 Feb 2017 9:13 am

I was able to use this syntax.

Select all

SELECT A.*, B.*,C.* FROM (SELECT * FROM DUAL) A,(SELECT * FROM DUAL) B,(SELECT * FROM DUAL) C;
Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 16 guests