One to Many Relationships - Tabular Display
-
I have designed my database with a one to many relationship where one user can have many statistics, but now I need to display the data in a single row format. The UserStatistics table has a foreign key to the User table (user_id in UserStatistics) Statistics might be height, weight, etc... The table should look like this: Name | Height | Weight Joe 6' 150 And so on. The header will always be the same (Name, Height, Weight or whatever) but any time the user does not have that particular statistic I need a blank column. Do I need to rejoin against the UserStatistics table over and over again to get the results I need or what?
-
I have designed my database with a one to many relationship where one user can have many statistics, but now I need to display the data in a single row format. The UserStatistics table has a foreign key to the User table (user_id in UserStatistics) Statistics might be height, weight, etc... The table should look like this: Name | Height | Weight Joe 6' 150 And so on. The header will always be the same (Name, Height, Weight or whatever) but any time the user does not have that particular statistic I need a blank column. Do I need to rejoin against the UserStatistics table over and over again to get the results I need or what?
Assuming your statistics table looks like this:
userid
property
value
1
height
180
1
width
100
2
height
170
2
width
110
This query:
Select userid
,max(case when property = 'height' then value else null end) as height
,max(case when property = 'width' then value else null end) as width
from properties
group by useridWould get this result:
userid
height
width
1
180
100
2
170
110
Depending on what database you're using, the pivot function might already be included and in that case it's easy enough to google how to use it.
Light moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions
-
I have designed my database with a one to many relationship where one user can have many statistics, but now I need to display the data in a single row format. The UserStatistics table has a foreign key to the User table (user_id in UserStatistics) Statistics might be height, weight, etc... The table should look like this: Name | Height | Weight Joe 6' 150 And so on. The header will always be the same (Name, Height, Weight or whatever) but any time the user does not have that particular statistic I need a blank column. Do I need to rejoin against the UserStatistics table over and over again to get the results I need or what?
-
Assuming your statistics table looks like this:
userid
property
value
1
height
180
1
width
100
2
height
170
2
width
110
This query:
Select userid
,max(case when property = 'height' then value else null end) as height
,max(case when property = 'width' then value else null end) as width
from properties
group by useridWould get this result:
userid
height
width
1
180
100
2
170
110
Depending on what database you're using, the pivot function might already be included and in that case it's easy enough to google how to use it.
Light moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions
This is MySql. Thank you for the suggestion, between you and Mycroft I will get it sorted out either using pivot or when ... then. Cheers, --EA
-
I have designed my database with a one to many relationship where one user can have many statistics, but now I need to display the data in a single row format. The UserStatistics table has a foreign key to the User table (user_id in UserStatistics) Statistics might be height, weight, etc... The table should look like this: Name | Height | Weight Joe 6' 150 And so on. The header will always be the same (Name, Height, Weight or whatever) but any time the user does not have that particular statistic I need a blank column. Do I need to rejoin against the UserStatistics table over and over again to get the results I need or what?
eddieangel wrote:
rejoin against the UserStatistics table over and over again
That's what I would do.
-
I have designed my database with a one to many relationship where one user can have many statistics, but now I need to display the data in a single row format. The UserStatistics table has a foreign key to the User table (user_id in UserStatistics) Statistics might be height, weight, etc... The table should look like this: Name | Height | Weight Joe 6' 150 And so on. The header will always be the same (Name, Height, Weight or whatever) but any time the user does not have that particular statistic I need a blank column. Do I need to rejoin against the UserStatistics table over and over again to get the results I need or what?
You can do this by using simple Join on these two tables.. Taking selected column in SELECT statement..
- Happy Coding - Vishal Vashishta
-
This is MySql. Thank you for the suggestion, between you and Mycroft I will get it sorted out either using pivot or when ... then. Cheers, --EA
MySQL doesn't have a specific PIVOT functionality AFAIK.
Light moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions
-
MySQL doesn't have a specific PIVOT functionality AFAIK.
Light moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions
It does not have a native pivot feature, but your aggregate / group by solution will work perfectly. Cheers, --EA