View with double SELECT from the same table

Asked

Viewed 1,867 times

0

I need to build a VIEW with three fields, but one depends on the other to be calculated (two of them are from the same table on different lines need "link them"), how do I aggregate these 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)
sql sql-server select select-sql sql-view

  • You already know how to do inner join two-table? I’m asking this to know how to explain to you, I don’t want to seem like a "know-it-all" or judging you down, I just want to know the terrain

  • The last two select are from the same table?

  • @Caiqueromero vide rLinhares response

  • Yes I know how to make two tables Inner Join,

  • My problem is that the last two selects are from the same table, under different conditions, and to find a need of the other’s select

  • 1

    @Jeffersonquesado I was going to answer similar putting alias in the tables, I just wanted to confirm if they were from the same table(I thought I could do with a single Join)

  • @W.S.Silva do autojunction, or junction/ Join of a table with itself, it works exactly the same as a traditional junction, but forces you to use it alias for the tables. See rLinhares' response, it made an autojunction of ZFI003 with herself

  • @Caiqueromero we are 3 so xD

  • Related: https://answall.com/q/214563/64969

  • 1

    I didn’t know I could join a table with itself, that’s what I need, thank you very much

Show 5 more comments

2 answers

5


From what I understand of the question, 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 on in the question by Quesado, it is good you give a studied in the commands of Join.

  • 1

    @Jeffersonquesado is because you are the reference :p already corrected, vlw

  • 1

    Thanks for the compliment =] I would recommend as an in-depth reading some answers already existing here at Sopt. I found that answer of mine in the subject, addressing the assembly of consultation through relational algebra. I will link the question as related, so feel free to quote or not in your answer

2

The @rLInhares solution solves this scenario well, just to quote, as a second alternative case the JOIN not possible, can also nest the 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 in place of = in the case of query return more than one record, but could also limit using TOP for example:

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 a note in case someone needs another way to solve

Browser other questions tagged sql sql-server select select-sql sql-view

You are not signed in. Login or sign up in order to post.