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
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
Microsoft SQL ServerSQL
Last Comment
arnololo123
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:
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.
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 |
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.
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
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:
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
ASKER
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 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
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
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
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
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
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thanks
Open in new window