Left Join returning only 1 record for each relationship

Asked

Viewed 3,026 times

1

I need to join between 2 tables where I need only the last record of the 2nd table (only 1 record of table 2 for each record of table 1).

SELECT a.Campo1, a.Campo2, a.Campo3, a.Campo4, b.Campo1, b.Campo2
FROM Tabela1 a
LEFT JOIN Tabela2 b ON b.Campo1 = a.Campo1 AND  (b.Campo2 = (SELECT b.Campo2 FROM Tabela2 c  WHERE c.Campo1 = a.Campo1) AND ROWNUM = 1)

trying this way returns the error:

ORA-01799: a column cannot be externally connected to a sub-consumption

Can someone help me?

sql join

  • What’s wrong with SELECT a.Campo1, a.Campo2, a.Campo3, a.Campo4, b.Campo1, b.Campo2 FROM Tabela1 a LEFT JOIN Tabela2 b ON b.Campo1 = a.Campo1? I do not understand what is the purpose of this subselect and from what I see, it seems completely innocuous and unnecessary.

  • A having together with the grouping would not solve?

  • The select in question returns more than 1 record from the second table, I only need the last record recorded in this table.

1 answer

0


See if it works: 

SELECT a."Campo1"
     , a."Campo2"
     , a."Campo3"
     , a."Campo4"
     , b."Campo1"
     , b."Campo2"
  FROM Tabela1 a
  LEFT JOIN (SELECT MAX(Tabela2."Campo2") AS "Campo2"
                  , Tabela2."Campo1" 
               FROM Tabela2 
              GROUP BY Tabela2."Campo1") b ON b."Campo1" = a."Campo1"

  • Give an error, invalid identifier.

  • I believe your bank is Case Sensitive, see now... (I edited the answer).. pq tested here and worked correctly. http://sqlfiddle.com/#! 4/294d85/1/0

  • The same error occurs

  • This is the SQL you need, as you can see on the link http://sqlfiddle.com/#! 4/294d85/1/0 it is working (at least your logic is right). Maybe it lacks some detail, try to put the quotes as in the link... but the logic is this

  • It worked, thank you so much for your help.

Browser other questions tagged sql join

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