CODEWITHSUNDEEP
sql
Marc Jonkers
Marc Jonkers

Reputation: 506

combine 2 sql's from different tables into one query

I have for example as first query: (ararnr = article number)

Select ararnr,ararir,aoarom from ar left join ao ON AR.ARARNR=AO.AOARNR WHERE AR.ARARKD=1389

the second query uses the result from the first column from the first query to search in another table

Select votgan, sum(ststan) as totalStock from vo INNER JOIN st  on vo.voarnr=st.starnr where voarnr = ararnr

How could I combine both ?

Please note : Not all articlenumbers from the first query will be found in the second, but I need them in my result.

In the result I need the columns from both queries.

EDIT

for example :

first query returns article numbers and the description:

+---------+--------------+
|  ararnr |    aoarom    |
+---------+--------------+
| a123456 | description1 |
| b123456 | description2 |
| 0123456 | description3 |
+---------+--------------+

second query returns the totalstock for those articles:

+---------+--------------+
|  ararnr |  totalstock  |
+---------+--------------+
| a123456 |      12      |
| b123456 |              |
| 0123456 |       6      |
+---------+--------------+

Note the second one doesn't return a value since the articlenumber doesn't exist in this table.

In my result I would like to get the articlenumber with corresponding description and stock.

+---------+--------------+-----------+---------+
|  ararnr |    aoarom    | totalStock| vovoan  |
+---------+--------------+-----------+---------+
| a123456 | description1 |     12    |   2     |
| b123456 | description2 |           |   1     |
| 0123456 | description3 |      6    |         |
+---------+--------------+-----------+---------+

I'm using sql on db2

SECOND EDIT

The first query will select some article numbers (ararnr) from table ar and find the corresponding description (aoarom) in another table ao.

The second query finds the stock (vovoan and sum ststan) from two differend tables vo and st for the article numbers found in the first query.

The result should have the article number with corresponding description with corresponding stock from vo and st

Upvotes: 0

Views: 158

Answers (4)

StevieG
StevieG

Reputation: 8709

You'd get a much more complete answer if you were to post the table structure and desired result, but..

You can use the first query as a resultset for your second query, and join to it. something like:

Select 
  votgan, 
  sum(ststan) as totalStock 
from vo 
inner join (Select 
              ararnr,
              ararir,
              ararom 
            from ar 
            left join ao .....) z on vo.voarnr = z.ararnr

EDIT:

Select 
  votgan, 
  sum(ststan) as totalStock,
  z.ararnr,
  z.aoarom
from vo 
inner join (Select 
              ararnr,
              ararir,
              ararom 
            from ar 
            left join ao .....) z on vo.voarnr = z.ararnr

Upvotes: 0

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

If you are using SQL Server as database then this can be done with help of OUTER APPLY

SELECT ararnr,aoarom ,temp.totalStock 
FROM ar 
LEFT JOIN ao ON AR.ARARNR=AO.AOARNR 
OUTER APPLY(
    SELECT sum(ststan) as totalStock 
    FROM vo 
    INNER JOIN st on vo.voarnr=st.starnr 
    where voarnr = ar.ararnr
)temp
WHERE AR.ARARKD=1389

Upvotes: 0

Husen
Husen

Reputation: 1587

You can use this query.

SELECT ar.ararnr, ar.ararir, ar.ararom, vo.votgan, SUM(vo.ststan) as totalStock 
FROM ar 
LEFT JOIN ao ON ao.ararnr = ar.ararnr 
LEFT JOIN vo ON vo.voarnr = ao.ararnr

Upvotes: 0

D'Arcy
D'Arcy

Reputation: 423

I can't fully understand what you're asking, but another join may assist you.

example:

SELECT ar.ararnr, ar.ararir, ar.ararom, vo.votgan, SUM(vo.ststan) as totalStock
FROM ar LEFT JOIN ao ON [id=id] LEFT JOIN vo ON [id=id]

Because I can't tell what your tables structure are, or what you're really asking for, this is the best response I can give you.

This also may be what you're looking for: Combining 2 SQL queries and getting result set in one

Upvotes: 1

Related Questions