Link to home
Create AccountLog in
Avatar of arnololo123
arnololo123

asked on

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
Lemon

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


Thanks
Avatar of Nathan Riley
Nathan Riley
Flag of United States of America image

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

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


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

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

ASKER

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?

Regards
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
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
ASKER CERTIFIED SOLUTION
Avatar of awking00
awking00
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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?

Thanks
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Thanks