[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Database Question (Query Result as Columns instead of Rows?)

Posted on 2004-10-22
22
Medium Priority
?
339 Views
Last Modified: 2010-04-05
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.
0
Comment
Question by:DragonSlayer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 7
  • 2
  • +2
22 Comments
 
LVL 14

Author Comment

by:DragonSlayer
ID: 12385529
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
 
LVL 12

Expert Comment

by:Ivanov_G
ID: 12387588
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
 
LVL 14

Author Comment

by:DragonSlayer
ID: 12388961
Hi Ivan, thanks for the reply. Now my problem is... I'm not allowed to change the table structures... :'(
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 5

Expert Comment

by:Voodooman
ID: 12389501
Comment:

Is this homework/college work?

Voodooman
0
 
LVL 14

Author Comment

by:DragonSlayer
ID: 12390791
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
 
LVL 12

Expert Comment

by:Ivanov_G
ID: 12391869
> 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
 
LVL 14

Author Comment

by:DragonSlayer
ID: 12392330
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
 
LVL 12

Expert Comment

by:Ivanov_G
ID: 12393852
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
 
LVL 14

Author Comment

by:DragonSlayer
ID: 12394153
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
 
LVL 12

Expert Comment

by:Ivanov_G
ID: 12394244
Nope, I don't have other...
0
 
LVL 12

Expert Comment

by:Ivanov_G
ID: 12394266
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
 
LVL 14

Author Comment

by:DragonSlayer
ID: 12394281
well, that's because the real database I'm accessing is actually from another software, and my software is only granted certain permissions.
0
 
LVL 5

Expert Comment

by:Voodooman
ID: 12394842
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
 
LVL 14

Author Comment

by:DragonSlayer
ID: 12397589
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
 
LVL 12

Expert Comment

by:Ivanov_G
ID: 12397770
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
 
LVL 14

Author Comment

by:DragonSlayer
ID: 12397812
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
 
LVL 12

Expert Comment

by:Ivanov_G
ID: 12397903
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
 
LVL 17

Expert Comment

by:geobul
ID: 12398173
A perfect example that normalized databases (although they occupy less space) aren't easy to operate with ;-)
0
 
LVL 14

Author Comment

by:DragonSlayer
ID: 12399028
Hi Geo ;-)

Yea, normalizations of higher levels are evil, especially for data warehousing solutions...
0
 
LVL 14

Author Comment

by:DragonSlayer
ID: 12676278
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
 

Accepted Solution

by:
modulo earned 0 total points
ID: 12764365
Closed, 500 points refunded.

modulo
Community Support Moderator
Experts Exchange
0

Featured Post

What’s Wrong with Your Cloud Strategy ?

Even as many CIOs are embracing a cloud-first strategy, the reality is that moving to the cloud is a lengthy process and the end-state is likely to be a blend of multiple clouds—public and private. Learn why multicloud solutions matter in this webinar by Nimble Storage.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question