| Author |
Topic |
|
EarlXT
Starting Member
10 Posts |
 Posted - 2009-08-21 : 11:16:09
|
| Suppose i have one parent the three child tables like thisCar Table (Parent)CarID12(Child Tables)Color TableCarID ColorName1 Red1 Blue1 Green1 Yellow2 Red Option TableCarID OptionName1 Radio 1 Leather2 Radio2 PSCountry TableCarID CountryName1 US2 US2 Canada2 UKNow I need to make these into a table that looks like thisCarID ColorName OptionName CountryName1 Red Radio US1 Blue Leather null1 Green null null1 Yellow null null2 Red Radio US2 null PS Canada2 null null UKMy head is starting to hurt just thinking about how to do this.Thanks for any helpEarlEarlXT |
|
|
rohitkumar
Constraint Violating Yak Guru
472 Posts |
Posted - 2009-08-21 : 11:33:32
|
| here the order of rows within the table is important and you have no keys to define the order, so not possible with the columns you have. |
 |
|
|
EarlXT
Starting Member
10 Posts |
Posted - 2009-08-21 : 11:45:00
|
| order is not important, as long as they are grouped correctly...and this is possible...its just not easy(for me, anyway)EarlXT |
|
|
|
EarlXT
Starting Member
10 Posts |
Posted - 2009-08-21 : 16:10:35
|
| I can solve this for one carid at at time, a putting it into a curor would solve my issue. It would still be nice to have a more elegent solution but I suppose that is the idealist comming out...declare @CarID intset @CarID = 1select car.id, colorname, optionname, countryname from (select row_number() over (order by carid) as rank, carid, colorname from color where carid=@CarID) colortablefull outer join (select row_number() over (order by carid) as rank, carid, optionname from [option] where carid=@CarID) optiontable on colortable.rank=optiontable.rank and colortable.carid=optiontable.caridfull outer join (select row_number() over (order by carid) as rank, carid, countryname from country where carid=@CarID) countrytable on countrytable.rank=optiontable.rank and countrytable.carid=optiontable.caridinner join car on car.id=colortable.carid or optiontable.carid = car.id or countrytable.carid = caridorder by car.idEarlXT |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2009-08-22 : 01:13:50
|
| I first thought it was just a bunch of outer joins but I can't even work out how your output relates to your input.Why does car 2 have red/radio/us and not red/ps/us for example? |
|
|
|
IncisiveOne
Starting Member
36 Posts |
Posted - 2009-08-22 : 05:41:10
|
| You are attempting to do two separate things in one step; each must be separated and done properly.Also, I agree with Lozt, there certainly are mistakes (the output cannot be derived from the input), so that needs correction.1 You do not have a fact table, any identification of exactly what events have taken place (which model of car has been made available or purchased). Right now, your tables look like possibilities (which Option/Colour/Country) might be available, or actually is available, or could be purchased. There is no record that "CarId 1 Red Radio US" might exist. Unless you get that fact (available/purchased) recorded, the report is meaningless, it is just a projection of all possibilities.2 The changing of rows into columns requires that you pivot the table. There are quite a few posts in various places which identify how to do that efficiently, and options to do so with or without limitations.-----a You do not need a cursor (unless you wish to turn the set-oriented engine on its head, and force it to go one row at a time).b You can do it with subqueries, but it will may ugly and limited (each time you want to add a column to the output, say Option2Name, you have to mess with the code).c Probably the most elegant solution is to treat it as a DSS query. (Not saying the source tables are Normalised, but) you need a Fact table, with all the possibilities (Option/Colour/Country) as Dimensions. Then the select is simple.CheersAnything worth doing, is worth doing right. |
|
|
|
cat_jesus
Aged Yak Warrior
547 Posts |
Posted - 2009-08-25 : 09:33:35
|
| Why is this in your output?1 Blue Leather nulland not this1 Blue Leather USAn infinite universe is the ultimate cartesian product. |
|
|
|
EarlXT
Starting Member
10 Posts |
Posted - 2009-08-28 : 14:42:36
|
| Sorry it took me a while to get back to this post. (real work and all). The childern are simply grouped by the parent ID (CarID in this case) They are not sorted in any particualar order but must be displayed from the top down within the parent ID. The child tables values do NOT relate to each other in any way except for the parent that they belong to. Child ID values mean nothing and even though my production tables have them, I do not use them to solve this. I am sorry if I did not explain this clearly in my example. The 'solution' I posted turned out not be not exactly correct (but close). For every child table, I have to do an extra 'OR' join so that is joins against all the existing children. I will post the correct solution after this post. In concept, what I did was create an extra column for each child table that was the row number. Then I did full outer joins on the childern to each of the other childern that have already been joined, using the generated row numbers as the join criteria. Finally, I joined back in the parent table with 'OR' joins to each of the child tables.I could only figure out how to do this for one parent ID at a time, I I put it into a cursor and did exactly that. earlt777@hotmail.com if you want to contact me.EarlEarlXT |
|
|
|
EarlXT
Starting Member
10 Posts |
Posted - 2009-08-28 : 14:55:39
|
| Let me go a bit further and explain where this model came from. A client wanted me to import there existing data that they needed to move from some (massive) word documents to excel so I could import it. (yes, the word documents were a bloody mess). At first I gave them seperate sheets for each table but they found that too confusing. Then they suggested this method of grouping by the parent (unique data) so they could put all the data on one sheet and it was all together. It also make it much easier for them to validate the correctness of the data. So far everything was easy for me ot import because I could create views that represented each table form this master sheet I needed to import then then write the import scripts on the views. The crazy part started then they said that they would like to see the data exported in the same format as they where giving me for import. Thats how I got myself into this mess to start with... EarlEarlXT |
|
|
|
EarlXT
Starting Member
10 Posts |
Posted - 2009-08-28 : 15:15:47
|
| Here is the script with the extra 'or' join on the last table. Again, for every child table you add, you have to 'or' join to every other child table. In real life, I had an entire tree of tables so I had do this at each level starting from the bottom up, and then take those results as the children for the next level. The uglist sql I have ever written.The 'correted' sql solution (for one CarID at a time)declare @CarID intset @CarID = 1select car.id, colorname, optionname, countryname from (select row_number() over (order by carid) as rank, carid, colorname from color where carid=@CarID) colortablefull outer join (select row_number() over (order by carid) as rank, carid, optionname from [option] where carid=@CarID) optiontable on colortable.rank=optiontable.rank and colortable.carid=optiontable.caridfull outer join (select row_number() over (order by carid) as rank, carid, countryname from country where carid=@CarID) countrytable on (countrytable.rank=optiontable.rank and countrytable.carid=optiontable.carid)or (countrytable.rank=colortable.rank and countrytable.carid=colortable.carid)inner join car on colortable.carid = car.id or optiontable.carid = car.id or countrytable.carid = car.idorder by car.idEarlXT |
|
|
|
IncisiveOne
Starting Member
36 Posts |
Posted - 2009-08-30 : 14:19:55
|
| Hey, MVJ, how dangerous and how scaleable, do you think this code is ? Why don't you post first (please be my guest) and I will go second.Anything worth doing, is worth doing right. |
|
|
|
|
Tricky Join Query