I need to mount a VIEW with three fields, but one depends on the other to be calculated (two of them are from the same table in different lines I need to "link" them), how do I add those fields in one place? The select's are as follows:
SELECT Referencia FROM ZSF053 WHERE TpFat = 'ZREK'
SELECT Doc_orig FROM ZFI003 WHERE Referencia = (utiliza as referencias do select acima)
SELECT Referencia FROM ZFI003 WHERE Num_doc = (utiliza os Doc_orig do select acima)
From what I understand about pergutna, you can test the query as below:
SELECT ZS.Referencia, ZF1.Doc_orig, ZF2.Referencia
FROM ZSF053 ZS
JOIN ZFI003 ZF1 ON ZS.Referencia = ZF1.Referencia
JOIN ZFI003 ZF2 ON ZF1.Doc_orig = ZF2.Doc_orig
WHERE ZS.TpFat = 'ZREK'
As commented in the question by Quesado , you should take a look at join .
The @rLInhares solution handles this scenario well, just to cite as a second alternative if JOIN is not possible, you can also nest SELECT . Taking your example, it would look like this:
SELECT Referencia
FROM ZFI003
WHERE Num_doc IN (SELECT Doc_orig
FROM ZFI003
WHERE Referencia IN (SELECT Referencia
FROM ZSF053
WHERE TpFat = 'ZREK'))
In this case above, I used IN instead of = in case the query returned more than one record, but could also limit using TOP eg:
SELECT Referencia
FROM ZFI003
WHERE Num_doc = (SELECT TOP 1 Doc_orig
FROM ZFI003
WHERE Referencia IN (SELECT TOP 1 Referencia
FROM ZSF053
WHERE TpFat = 'ZREK'))
Just as note if someone needs another way to solve