Trying to get a column from a Subquery

I need to extract information out of 2 Tables. I can’t join them because there is a resource limit for SQL Queries, which is very low. So I came up with following query.

This works quite well, but I am missing a column from “PROD.R050100”.

How can I add column Name in a way, that name is corresponding to the correct KUNDNR?

Answer

You may try this. This will give you name from R050100 corresponding to R050200.

Select Distinct(R2.KUNDNR),R2.PRODNR
, (SELECT DISTINCT NAME FROM PROD.R050100 AS R1 WHERE R1.KUNDNR=R2.KUNDNR AND R1.PERSON = 'J'
   AND R1.KZBETR <> 'A' AND R1.STORNO = 99999999) AS NAME    --- THIS WILL ADD NAME FIELD FROM R050100
FROM PROD.R050200 AS R2
Where R2.Sparte = '9FN'
AND R2.KUNDNR IN (
SELECT KUNDNR 
FROM PROD.R050100 
WHERE PERSON = 'J'
AND KZBETR <> 'A'
AND STORNO = 99999999 )

If this will not work for you then sample data of your record is needed.