CODEWITHSUNDEEP
sqldb2
Zo Has
Zo Has

Reputation: 13018

Inner join on inner join?

      **TableFruits**                        **TableNames**
   RecId    RId      FruitId       Price         RId  FruitId       Name
     1      A1        Null           3$           A1      1        Mango
     2      A2         2             4$           A2      2       Tomato

Since there is Null under FruitId in TableFruits I want to fetch it from TableNames like

Select a.RecId,a.RId,b.FruitId from TableFruits a INNER JOIN TableNames b on a.RId=b.RId
  1. How do I apply a join in this SQL statement to include FruitNames from Table 2 (I cannot change the order of tables)

Upvotes: 0

Views: 244

Answers (2)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

SELECT a.RecId
     , a.RId
     , COALESCE(a.FruitId, b.FruitId) AS FruitId
     , b.Name
FROM TableFruits a 
  JOIN TableNames b 
    ON b.RId = a.RId  ;

Since you can have mismatches in RId, you need an outer join. LEFT, RIGHT or FULL, depending on which side the mismatches can occur.

MySQL does not have FULL JOIN, so you can do something like this:

SELECT a.RecId
     , a.RId
     , COALESCE(a.FruitId, b.FruitId) AS FruitId
     , b.Name
FROM TableFruits a 
  LEFT JOIN TableNames b 
    ON b.RId = a.RId 

UNION ALL

SELECT NULL
     , NULL
     , b.FruitId
     , b.Name
FROM TableFruits a 
  RIGHT JOIN TableNames b 
    ON b.RId = a.RId 
WHERE a.RId IS NULL  ;

Upvotes: 2

juergen d
juergen d

Reputation: 204756

Select a.RecId,a.RId,a.FruitId, b.Name
from TableFruits a 
LEFT OUTER JOIN TableNames b on a.RId=b.RId

Upvotes: 2

Related Questions