combine 2 sql's from different tables into one query

Question

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

Combine how? Show us sample result for the two select's, plus the combined result. — jarlh, Sep 21 '16 at 10:22

score 1 · Answer 1 · edited May 23 '17 at 10:34

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

StevieG · Answer 2 · 2016-09-23T11:09:25.600

0

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

edited Sep 23 '16 at 11:09

answered Sep 21 '16 at 10:32

StevieG

8,639
23
31

Tried this. This only seems to return the items found in table vo. I also don't know how to get for example ararnr or aoarom. I tried to add it in the first select but that doesn't work .... – Marc Jonkers Sep 22 '16 at 14:47
When I change SUM(ststan) to just ststan the code works, but of course I d"ont get the sum of ststan. I definitly need the sum of ststan. The error message I get when the SUM is in the code : SELECT WITH NO GROUP BY HAS COL NAME/FUNC OR COL NAME NOT IN GROUP BY. VOVOAN – Marc Jonkers Sep 26 '16 at 13:42

score 0 · Answer 3 · answered Sep 21 '16 at 10:48

0

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

answered Sep 21 '16 at 10:48

Husen

1,541
10
14

Tried this but getting an error :"ILLEGAL USE OF KEYWORD LEFT; TOKEN FOR USE SKIP WAIT WITH FETCH LIMIT OFFSET OPTIMIZE WAS EXPECTED" – Marc Jonkers Sep 22 '16 at 13:15

Jaydip Jadhav · Answer 4 · 2016-09-21T12:33:21.090

0

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

edited Sep 21 '16 at 12:33

answered Sep 21 '16 at 12:21

Jaydip Jadhav

12,179
6
24
40

I'm using sql on db2. It seems it doesn't support Outer Apply. – Marc Jonkers Sep 21 '16 at 12:39

combine 2 sql's from different tables into one query

4 Answers4