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.
LVL 14
DragonSlayerAsked:
Who is Participating?
 
moduloCommented:
Closed, 500 points refunded.

modulo
Community Support Moderator
Experts Exchange
0
 
DragonSlayerAuthor Commented:
PS: I will be outstation for 2-3 days, so I might take time to reply, but experts, please post your opinions/suggestions.

Thanks!
0
 
Ivanov_GCommented:
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
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
DragonSlayerAuthor Commented:
Hi Ivan, thanks for the reply. Now my problem is... I'm not allowed to change the table structures... :'(
0
 
VoodoomanCommented:
Comment:

Is this homework/college work?

Voodooman
0
 
DragonSlayerAuthor Commented:
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.
0
 
Ivanov_GCommented:
> 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 ...
0
 
DragonSlayerAuthor Commented:
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...
0
 
Ivanov_GCommented:
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
0
 
DragonSlayerAuthor Commented:
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?
0
 
Ivanov_GCommented:
Nope, I don't have other...
0
 
Ivanov_GCommented:
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...
0
 
DragonSlayerAuthor Commented:
well, that's because the real database I'm accessing is actually from another software, and my software is only granted certain permissions.
0
 
VoodoomanCommented:
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
0
 
DragonSlayerAuthor Commented:
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.
0
 
Ivanov_GCommented:
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...
0
 
DragonSlayerAuthor Commented:
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.
0
 
Ivanov_GCommented:
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...
0
 
geobulCommented:
A perfect example that normalized databases (although they occupy less space) aren't easy to operate with ;-)
0
 
DragonSlayerAuthor Commented:
Hi Geo ;-)

Yea, normalizations of higher levels are evil, especially for data warehousing solutions...
0
 
DragonSlayerAuthor Commented:
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?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.