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

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nathan RileyFounderCommented:
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 AdvisorCommented:
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.
arnololo123Author Commented:
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?

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Vitor MontalvãoMSSQL Senior EngineerCommented:
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

arnololo123Author Commented:
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 AdvisorCommented:
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
awking00Information Technology SpecialistCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
arnololo123Author Commented:
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 AdvisorCommented:
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
arnololo123Author Commented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.