SELECT b.Code ,b.Name AS Bird ,NVL(g.Grain,'NULL') AS Grain ,NVL(f.Fruit,'NULL') AS Fruit FROM PLC1_BIRDS b LEFT JOIN ( (SELECT DENSE_RANK() OVER(PARTITION BY Code ORDER BY Fruit) RowNumber ,Code ,Fruit FROM PLC1_FRUITS) f FULL JOIN (SELECT DENSE_RANK() OVER(PARTITION BY Code ORDER BY Grain) RowNumber ,Code ,Grain FROM PLC1_GRAINS) g ON f.Code = g.Code AND f.RowNumber = g.RowNumber )ON b.Code IN(g.Code,f.Code);
Tags: DENSE_RANK(), FULL JOIN