January 13, 2011 at 6:02 pm
Let’s say we have 3 tables: fruit name, color and taste that have data like this:
Fruits:
Apple
Color:
Red
Green
Yellow
Taste:
Sweet
Bitter
I need to come up with a single SQL statement that will combine all 3 tables so that for each fruit there will be as many records as max number of records in any of other tables (in this case 3 records because the Color table has 3 of them) and values will be in order how they appear in source tables like:
Result of a statement I can’t come up with:
Apple Red Sweet
Apple Green Bitter
Apple Yellow NULL
January 16, 2011 at 6:28 pm
j2bmw (1/13/2011)
Let’s say we have 3 tables: fruit name, color and taste that have data like this:Fruits:
Apple
Color:
Red
Green
Yellow
Taste:
Sweet
Bitter
I need to come up with a single SQL statement that will combine all 3 tables so that for each fruit there will be as many records as max number of records in any of other tables (in this case 3 records because the Color table has 3 of them) and values will be in order how they appear in source tables like:
Result of a statement I can’t come up with:
Apple Red Sweet
Apple Green Bitter
Apple Yellow NULL
Based on what you have provided, I use a CROSS JOIN, but that would result in 6 records:
Apple Red Sweet
Apple Red Bitter
Apple Green Sweet
Apple Green Bitter
Apple Yellow Sweet
Apple Yellow Bitter
The query whould look like this:
select
* -- I'd actually name all the columns
from
dbo.Fruit
CROSS JOIN dbo.Color
CROSS JOIN dbo.Taste;
I'm not sure how I would come up with the result set you are looking at returning as there really is no relation between the tables.
January 16, 2011 at 9:11 pm
> ...Based on what you have provided, I use a CROSS JOIN, but that would result in 6 records...
6 records are not good. For the given data statement should generate just 3.
January 16, 2011 at 10:25 pm
If you really want the results as you described, add an ID (identity) column onto color and taste tables and then run the following script
select
* --pick the column info you want to see
from
dbo.Fruit
CROSS JOIN dbo.Color
LEFT JOIN dbo.Taste ON Color.ID = Taste.ID
If the data in color and taste table is sorted by the value when entering, you can using ROW_NUMBER() function to achieve the same results without modify the table
select
* --pick the column info you want to see
from
dbo.Fruit
CROSS JOIN (SELECT *, ID = ROW_NUMBER() OVER(ORDER BY ColorName) FROM dbo.Color) Color
LEFT JOIN (SELECT *, ID = ROW_NUMBER() OVER(ORDER BY TasteName) FROM dbo.Taste) Taste ON Color.ID = Taste.ID
January 16, 2011 at 11:08 pm
picant (1/16/2011)
> ...Based on what you have provided, I use a CROSS JOIN, but that would result in 6 records...6 records are not good. For the given data statement should generate just 3.
Problem is that there is no way to relate the tables together, unless you have some magical formula you use to determine what goes with what.
What would your results be if you added a second fruit to the fruit table, such as Pear?
January 16, 2011 at 11:17 pm
Problem is that there is no way to relate the tables together, unless you have some magical formula you use to determine what goes with what
Of course there is a magic formula. It was stated in the original post "values will be in order how they appear in source tables". The algorithm sounds like:
For each value in the Fruit table create a record and put there first values from Taste and Color tables. Add another record using the same Fruit value and put there second values from Taste and Color tables. Continue untill there are no more values in Taste and Color tables. Than repead for the second value from Fruit table and so on.
January 17, 2011 at 12:38 am
picant (1/16/2011)
Problem is that there is no way to relate the tables together, unless you have some magical formula you use to determine what goes with what
Of course there is a magic formula. It was stated in the original post "values will be in order how they appear in source tables". The algorithm sounds like:
For each value in the Fruit table create a record and put there first values from Taste and Color tables. Add another record using the same Fruit value and put there second values from Taste and Color tables. Continue untill there are no more values in Taste and Color tables. Than repead for the second value from Fruit table and so on.
Okay, any particular order? Remember, in SQL order is only guaranteed if there is an order by clause. You aren't guaranteed to retreive the data from the color and taste table in the same order each time they are queried. Also,
why is the Red Apple Sweet and the Green Apple Bitter? Why couldn't it be the other way around? And does the Yellow Apple have not taste?
January 17, 2011 at 3:18 pm
...Okay, any particular order? ... Also,
why is the Red Apple Sweet and the Green Apple Bitter? Why couldn't it be the other way around? And does the Yellow Apple have not taste?
Order does not matter. Red Apple can be Sweet or Bitter. But each of the attributes must be used only once for each fruit. So the results can look like:
Apple Red Sweet
Apple Green Bitter
Apple Yellow NULL
or
Apple Red Bitter
Apple Green Sweet
Apple Yellow NULL
or
Apple Red NULL
Apple Yellow Sweet
Apple Green Bitter
you got the idea...
January 17, 2011 at 5:23 pm
picant (1/17/2011)
...Okay, any particular order? ... Also,
why is the Red Apple Sweet and the Green Apple Bitter? Why couldn't it be the other way around? And does the Yellow Apple have not taste?
Order does not matter. Red Apple can be Sweet or Bitter. But each of the attributes must be used only once for each fruit. So the results can look like:
Apple Red Sweet
Apple Green Bitter
Apple Yellow NULL
or
Apple Red Bitter
Apple Green Sweet
Apple Yellow NULL
or
Apple Red NULL
Apple Yellow Sweet
Apple Green Bitter
you got the idea...
January 17, 2011 at 6:55 pm
picant (1/17/2011)
...Okay, any particular order? ... Also,
why is the Red Apple Sweet and the Green Apple Bitter? Why couldn't it be the other way around? And does the Yellow Apple have not taste?
Order does not matter. Red Apple can be Sweet or Bitter. But each of the attributes must be used only once for each fruit. So the results can look like:
Apple Red Sweet
Apple Green Bitter
Apple Yellow NULL
or
Apple Red Bitter
Apple Green Sweet
Apple Yellow NULL
or
Apple Red NULL
Apple Yellow Sweet
Apple Green Bitter
you got the idea...
If I tell you how, will you tell us why? I mean what are the business rules that allow for what most people would consider to be a huge mistake in an RDBMS?
--Jeff Moden
Change is inevitable... Change for the better is not.
January 17, 2011 at 7:32 pm
If I tell you how, will you tell us why? I mean what are the business rules that allow for what most people would consider to be a huge mistake in an RDBMS?
The business rule is quite simple: we have some existing code that deals with this kind of data structures. It's not feasible to change the code.
While it doesn't deal with fruits the logic is very similar. We have a number of objects and a number of attributes that can belong to any of the objects. Each attribute (actually a link to an index in another table) is stored in a dedicated column. 2 attributes of an object that belong to the same column will generate 2 records in the table (using fruit analogy one record for an apple can have the attribute green and another red). If object has more attributes from other columns they can be stored in empty spots in any records that belong to this object (like sweet can be in the record with red value but it can be in a record with green value as well). I do have a feeling that this isn't very "relational" way to store data but can't quite figure out exactly why. Does anybody have a simple explanation what's wrong with such structure?
January 17, 2011 at 7:32 pm
If I tell you how, will you tell us why? I mean what are the business rules that allow for what most people would consider to be a huge mistake in an RDBMS?
The business rule is quite simple: we have some existing code that deals with this kind of data structures. It's not feasible to change the code.
While it doesn't deal with fruits the logic is very similar. We have a number of objects and a number of attributes that can belong to any of the objects. Each attribute (actually a link to an index in another table) is stored in a dedicated column. 2 attributes of an object that belong to the same column will generate 2 records in the table (using fruit analogy one record for an apple can have the attribute green and another red). If object has more attributes from other columns they can be stored in empty spots in any records that belong to this object (like sweet can be in the record with red value but it can be in a record with green value as well). I do have a feeling that this isn't very "relational" way to store data but can't quite figure out exactly why. Does anybody have a simple explanation what's wrong with such structure?
January 17, 2011 at 8:00 pm
I guess I am missing how the business reason behind what you are doing isn't meshing with your fruit analogy.
Using analogies doesn't always help us help you solve your problem.
January 17, 2011 at 8:10 pm
I guess I am missing how the business reason behind what you are doing isn't meshing with your fruit analogy.
Using analogies doesn't always help us help you solve your problem.
Let's pretend there is no business reason. Can anybody still solve the fruit task?
January 17, 2011 at 8:23 pm
picant (1/17/2011)
Does anybody have a simple explanation what's wrong with such structure?
Yes... it produces data that doesn't actually exist in the real world. Instead, it produces randomized results from randomized joins or artificially ordered joins for the empty slots... or so you have described it. The data ends up being false or, at the very best, meaningless.
It also doesn't explain the actual business reason as to why we want to do this. Yes, I understand that you're trying to duplicate a method. I want to know "What on Earth were they thinking" when they designed this problem into the schema. There are two reasons for my curiosity... one is that I just want to know because I'm amazed that anyone would have a good reason for doing this and the other is to possibly suggest an alternative but I have to know the real reason before I or anyone else can suggest such a thing.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply