Database Question (Query Result as Columns instead of Rows?)
Here's a hypothetical (simplified) database structure:
Members (Table)
MemberID - Primary Key, type GUID
MemberName - type Varchar(128)
Age - type Integer
Status - type SmallInt (Single, Married, Widow, etc)
Hobbies (Table)
HobbyID - Primary Key, type Integer
HobbyName - type Varchar(256)
HobbyType - type SmallInt (Indoors, Outdoors)
MembersHobbies (Table)
MemberID - Foreign Key, type GUID
HobbyID - Foreign Key, type Integer
Now, each Member can have from 0 up to 3 hobbies in the MembersHobbies table.
1. Sounds simple enough, but here's the first obstacle... the resulting query (will be later displayed in a ListView) needs to be in this format:
MemberID MemberName Age Status Hobby1 Hobby2 Hobby3
--------------------------
if the member has 1 hobby, Hobby2 and 3 will have the value "-". If the member has 0 hobby, all the 3 Hobby columns will be a dash "-".
Well, since there is a max. limit of 3 columns, I was thinking of making use of Calculated fields, so that I will do an external query for each of the Member row.
2. However, here comes the 2nd obstacle...
Calculated fields is fine, as long as my searching part of the programme allows users to only search on criterias such as Age and Status. However, part of the search criteria would also involve filtering by Hobby.
e.g. the user can ask for something like "Show me records of everyone aged between 20 and 25 with 1 or more hobby that is of type Outdoor"... or ... "Show me records of all <30 year old singles with Fishing as one of their hobbies".
The result will still need to be in the above format, and all (up to 3) hobbies of members who match the given criteria needs to be displayed, e.g. for the above criteria, the result might be
SomeGUID Alex 20 Fishing Reading -
SomeGUID Meikl 22 Programming Fishing Jogging
Do hope someone can shed some light on how to go about tackling this without compromising on the performance (calculated fields can be very very tedious and time-consuming, on large databases). And if it's of any use, I'm using FireBird 1.5 with the IBObjects 4.x components.
DragonSlayer.
Members (Table)
MemberID - Primary Key, type GUID
MemberName - type Varchar(128)
Age - type Integer
Status - type SmallInt (Single, Married, Widow, etc)
Hobbies (Table)
HobbyID - Primary Key, type Integer
HobbyName - type Varchar(256)
HobbyType - type SmallInt (Indoors, Outdoors)
MembersHobbies (Table)
MemberID - Foreign Key, type GUID
HobbyID - Foreign Key, type Integer
Now, each Member can have from 0 up to 3 hobbies in the MembersHobbies table.
1. Sounds simple enough, but here's the first obstacle... the resulting query (will be later displayed in a ListView) needs to be in this format:
MemberID MemberName Age Status Hobby1 Hobby2 Hobby3
--------------------------
if the member has 1 hobby, Hobby2 and 3 will have the value "-". If the member has 0 hobby, all the 3 Hobby columns will be a dash "-".
Well, since there is a max. limit of 3 columns, I was thinking of making use of Calculated fields, so that I will do an external query for each of the Member row.
2. However, here comes the 2nd obstacle...
Calculated fields is fine, as long as my searching part of the programme allows users to only search on criterias such as Age and Status. However, part of the search criteria would also involve filtering by Hobby.
e.g. the user can ask for something like "Show me records of everyone aged between 20 and 25 with 1 or more hobby that is of type Outdoor"... or ... "Show me records of all <30 year old singles with Fishing as one of their hobbies".
The result will still need to be in the above format, and all (up to 3) hobbies of members who match the given criteria needs to be displayed, e.g. for the above criteria, the result might be
SomeGUID Alex 20 Fishing Reading -
SomeGUID Meikl 22 Programming Fishing Jogging
Do hope someone can shed some light on how to go about tackling this without compromising on the performance (calculated fields can be very very tedious and time-consuming, on large databases). And if it's of any use, I'm using FireBird 1.5 with the IBObjects 4.x components.
DragonSlayer.
Add one more field to MembersHobbies table:
MemberID - Foreign Key, type GUID
HobbyNum - values from 1, 2 or 3
HobbyID - Foreign Key, type Integer
and then something like:
SELECT M.MemberID, M.MemberName, M.Age, M.Status,
MH1.HobbyID As Hobby1,
MH2.HobbyID AS Hobby2,
MH3.HobbyID AS Hobby3
FROM Members M
JOIN MembersHobbies MH1 ON MH1.MemberID = M.MemberID
JOIN MembersHobbies MH2 ON MH2.MemberID = M.MemberID
JOIN MembersHobbies MH3 ON MH3.MemberID = M.MemberID
WHERE MH1.HobbyNum = 1
AND MH2.HobbyNum = 2
AND MH3.HobbyNum = 3
MemberID - Foreign Key, type GUID
HobbyNum - values from 1, 2 or 3
HobbyID - Foreign Key, type Integer
and then something like:
SELECT M.MemberID, M.MemberName, M.Age, M.Status,
MH1.HobbyID As Hobby1,
MH2.HobbyID AS Hobby2,
MH3.HobbyID AS Hobby3
FROM Members M
JOIN MembersHobbies MH1 ON MH1.MemberID = M.MemberID
JOIN MembersHobbies MH2 ON MH2.MemberID = M.MemberID
JOIN MembersHobbies MH3 ON MH3.MemberID = M.MemberID
WHERE MH1.HobbyNum = 1
AND MH2.HobbyNum = 2
AND MH3.HobbyNum = 3
ASKER
Hi Ivan, thanks for the reply. Now my problem is... I'm not allowed to change the table structures... :'(
Comment:
Is this homework/college work?
Voodooman
Is this homework/college work?
Voodooman
ASKER
How I wish it was! LoLx... Left varsity life years ago ;-)
The reason I came up with a hypothetical database is because the real database structure has much more fields than this and I do not want to over complicate things.
The reason I came up with a hypothetical database is because the real database structure has much more fields than this and I do not want to over complicate things.
> I'm not allowed to change the table structures
This will definitely make the things more complex... The other way is to select the first columns: MemberID MemberName Age Status. And the execute another SQL for selecting the hobbies. And the same way for each line ...
This will definitely make the things more complex... The other way is to select the first columns: MemberID MemberName Age Status. And the execute another SQL for selecting the hobbies. And the same way for each line ...
ASKER
But if I use that approach, I can't have a filter to filter out the hobbies (see requirement #2) without involving too many queries... and that is why I'm trying to avoid too...
try this query (if your DB supported sub-selects):
SELECT M.MemberID, M.MemberName, M.Age, M.Status,
subselect.hobbyid1,
subselect.hobbyid2,
subselect.hobbyid3
FROM Members M,
(select m1.memberid, m1.hobbyid as hobbyid1,
m2.hobbyid as hobbyid2, m3.hobbyid as hobbyid3
from MembersHobbies m1,
MembersHobbies m2,
MembersHobbies m3
where m1.hobbyid != m2.hobbyid
and m2.hobbyid != m3.hobbyid
and m1.memberid = m2.memberid
and m2.memberid = m3.memberid ) subselect
where subselect.memberid = m.memberid
SELECT M.MemberID, M.MemberName, M.Age, M.Status,
subselect.hobbyid1,
subselect.hobbyid2,
subselect.hobbyid3
FROM Members M,
(select m1.memberid, m1.hobbyid as hobbyid1,
m2.hobbyid as hobbyid2, m3.hobbyid as hobbyid3
from MembersHobbies m1,
MembersHobbies m2,
MembersHobbies m3
where m1.hobbyid != m2.hobbyid
and m2.hobbyid != m3.hobbyid
and m1.memberid = m2.memberid
and m2.memberid = m3.memberid ) subselect
where subselect.memberid = m.memberid
ASKER
Well, Interbase doesn't support subselects, but nevertheless, I tried by taking out your subselect to create a View.
However, 2 flaws:
1. If I were to have Member ID 123 with HobbyIDs 1, 2 and 3, I will end up having various combinations:
123, 1, 2, 3
123, 1, 3, 2
123, 2, 1, 3
123, 2, 3, 1
123, 3, 1, 2
123, 3, 2, 1
Or if you prefer, I get all the possible combinations (factorial, n!)
2. As mentioned earlier, the Members can have from 0 to 3 hobbies. Using your SELECT statement from the subselect portion, I get duplicate values if the Hobbies are less than 3, and I don't get any results at all from Members without Hobbies.
Thanks for your feedback thus far, though. Any other suggestions?
However, 2 flaws:
1. If I were to have Member ID 123 with HobbyIDs 1, 2 and 3, I will end up having various combinations:
123, 1, 2, 3
123, 1, 3, 2
123, 2, 1, 3
123, 2, 3, 1
123, 3, 1, 2
123, 3, 2, 1
Or if you prefer, I get all the possible combinations (factorial, n!)
2. As mentioned earlier, the Members can have from 0 to 3 hobbies. Using your SELECT statement from the subselect portion, I get duplicate values if the Hobbies are less than 3, and I don't get any results at all from Members without Hobbies.
Thanks for your feedback thus far, though. Any other suggestions?
Nope, I don't have other...
When you said VIEW - why not a stored procedure with CURSOR inside, thus you can loop the select from membershobbies and fill temporary table for e.g...
ASKER
well, that's because the real database I'm accessing is actually from another software, and my software is only granted certain permissions.
Hi
You said
'well, that's because the real database I'm accessing is actually from another software, and my software is only granted certain permissions'
In that case why not import the tables into your own database where you are free to manipulate it anyway you want?
This would be an obvious solution to your problem.
Voodooman
You said
'well, that's because the real database I'm accessing is actually from another software, and my software is only granted certain permissions'
In that case why not import the tables into your own database where you are free to manipulate it anyway you want?
This would be an obvious solution to your problem.
Voodooman
ASKER
Hi Voodooman,
The database is remote, and currently having only a few hundred K records (but which will grow in the future). It seems pretty impractical to me, that as soon as someone starts my application, I will connect to a remote database, attempt to download all the data from it, and duplicate it at the client side. Also, the client will not have a full fledged DBMS, and I don't think I would want to duplicate the data onto an Access database.
DragonSlayer.
The database is remote, and currently having only a few hundred K records (but which will grow in the future). It seems pretty impractical to me, that as soon as someone starts my application, I will connect to a remote database, attempt to download all the data from it, and duplicate it at the client side. Also, the client will not have a full fledged DBMS, and I don't think I would want to duplicate the data onto an Access database.
DragonSlayer.
Another option - execute 2 queries from the remote DB - one for member details and other for the hobbies for specific MemberID. Then you can merge/loop through them locally however you want...
ASKER
Yea, that is how I am currently doing it, but it's slow... :-(
And also, it means that I have to retrieve all data before I allow the user to perform the searches as mentioned above.
And also, it means that I have to retrieve all data before I allow the user to perform the searches as mentioned above.
You have a lot of restrictions using this remote DB, so ... the speed is not something you can achieve here. It seems that this is the only possible good solution til now...
A perfect example that normalized databases (although they occupy less space) aren't easy to operate with ;-)
ASKER
Hi Geo ;-)
Yea, normalizations of higher levels are evil, especially for data warehousing solutions...
Yea, normalizations of higher levels are evil, especially for data warehousing solutions...
ASKER
OK I'm back to this Q... and I have been working on other projects since, and haven't spent time thinking about this.
So what should I do now? Wait for further input? Reward someone with points (Hmm... now that's another problem... whom to reward?)? Delete the Q? No idea... anyone?
So what should I do now? Wait for further input? Reward someone with points (Hmm... now that's another problem... whom to reward?)? Delete the Q? No idea... anyone?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks!