We help IT Professionals succeed at work.

SQL query - line up result

Hello to make it simple I will use an easy example

I have one table with a list of father:
1 Paul
2 John

Another table with list of Kids for each father
Paul Jr       2010      Boy         1
John Jr       2011      Boy         2
Cindy         2009     Girl          2

And another table with list of favorite food for each father
Chocolate  1
Bannana     1
Pineapple   2
Raspberry   2

Now my issue is to query those table in order to show the result this way
Paul      Paul Jr      2010    Boy   Chocolate
Paul                                               Banana
John      John Jr      2011    Boy   Pineapple
John     Cindy        2009    Girl    Raspberry
John                                               Lemon

Watch Question

Nathan RileyFounder

I'm not sure what you field or table names are so you will have to modify for that.

select father_name, child_name, birth_date, gender, favorite_food
from father_table ft
left outer join child_table ct on ft.id = ct.father_id
left outer join food_table fdt on ft.id = fdt.father_id

Open in new window

PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

The query above will certainly work, but the result t will not appear like you have asked for.  This is not a criticism of Nathan's query! I'm just pointing out that your expectation won't be met by standard SQL.

SQL will repeat the information (where it is relevant), and the nature of the joins will produce more rows than you have requested. The result (for the sample data) would be:
| ID | Name | ChildName | YearBirth | Gender | FatherID |  foodName | FatherID |
|  1 | Paul |   Paul Jr |      2010 |    Boy |        1 | Chocolate |        1 |
|  1 | Paul |   Paul Jr |      2010 |    Boy |        1 |   Bannana |        1 |
|  2 | John |   John Jr |      2011 |    Boy |        2 | Pineapple |        2 |
|  2 | John |   John Jr |      2011 |    Boy |        2 | Raspberry |        2 |
|  2 | John |   John Jr |      2011 |    Boy |        2 |     Lemon |        2 |
|  2 | John |     Cindy |      2009 |   Girl |        2 | Pineapple |        2 |
|  2 | John |     Cindy |      2009 |   Girl |        2 | Raspberry |        2 |
|  2 | John |     Cindy |      2009 |   Girl |        2 |     Lemon |        2 |

Open in new window

I assume the food Lemon is supposed to have a fatherID of 2

when providing data also provide headings and the tablenames, this way answers will be more consistent and easier to use for you also.


Hello Paul,

You are exactly right, you are pointing out the exact problem I am having with all of those duplicates row,  any idea of how to prevent that or reduce it?

Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Your example may be not the best one because I'll need to ask why mixing father's favorite food with their kids?
Why not separate things like:
select f.name, c.name, c.birthyear, c.genre, null
from father f
inner join children c on f.id = c.fatherid
union all
select father.name, null, null, null, food.name
from father
inner join food on father.id = food.fatherid
order by 1, 2, 3

Open in new window


Thank Vitor,

You approach is good now how to I keep every thing in line because with you query
it gives: How could I have the "Favorite food" to start at the first row at least?
Paul      Paul Jr      2010    Boy      null
 Paul     null          null      null        Banana
Paul      Null         Null      Null       Chocolate
 John      John Jr      2011    Boy  
 John     Cindy        2009    Girl    
 John     Null         Null      Null      Lemon
 John    Null         Null      Null      Pineapple
 John     Null         Null      Null       Rasberry
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

You are expecting SQL to do things it just does not do, or does not do very well.

In what are you presenting this information? a HTML page? a "report" (e.g. Crystal)

I ask because you may need some other tool to achieve this presentation of that data
Information Technology Specialist
SQL> select * from dads;

        ID NAME
---------- ----------
         1 Paul
         2 John

SQL> select * from kids;

NAME       YOB  SEX          FK
---------- ---- ---- ----------
Paul Jr    2010 Boy           1
John Jr    2011 Boy           2
Cindy      2009 Girl          2

SQL> select * from foods;

FOOD                 FK
------------ ----------
Chocolate             1
Bananas               1
Pineapple             2
Raspberry             2
Lemon                 2

SQL> select dad, child, yob, sex, food from
  2  (select d.id, d.name father, k.name child, k.yob, k.sex,
  3   row_number() over (partition by d.name order by k.sex) rn
  4   from kids k
  5   left join
  6   dads d
  7   on d.id = k.fk
  8   order by d.name desc) x
  9  right join
 10  (select d.name dad, f.fk, f.food,
 11   row_number() over (partition by fk order by rownum) rn
 12   from foods f, dads d
 13   where f.fk = d.id) y
 14  on x.rn = y.rn
 15  and x.father = y.dad
 16  order by dad desc, child desc nulls last;

DAD        CHILD      YOB  SEX  FOOD
---------- ---------- ---- ---- ------------
Paul       Paul Jr    2010 Boy  Chocolate
Paul                            Bananas
John       John Jr    2011 Boy  Pineapple
John       Cindy      2009 Girl Raspberry
John                            Lemon


Thanks, this is exactly what I was looking for.

Now I am trying to understand you query,   I  don't understand this part "partition by fk order by rownum"   there is no rownum in the query and shouldn't we use "d.name" instead of "FK" like for the first partition?

PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
The query provided is using Oracle syntax ("rownum" is an Oracle reference for each row in a result).

The second ROW_NUMBER() can be partition by Dad's name and can use any order you deem suitable (e.g. order by food)  it does not matter to the logic of the query.

Note, I believe the query assumes there is always equal or more favourite foods than children. If there are more children than foods for a Dad I think you will need a FULL OUTER JOIN