Solved

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

Posted on 2004-10-22
295 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
Question by:DragonSlayer
    21 Comments
     
    LVL 14

    Author Comment

    by:DragonSlayer
    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
    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
    Hi Ivan, thanks for the reply. Now my problem is... I'm not allowed to change the table structures... :'(
    0
     
    LVL 5

    Expert Comment

    by:Voodooman
    Comment:

    Is this homework/college work?

    Voodooman
    0
     
    LVL 14

    Author Comment

    by:DragonSlayer
    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
    > 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
    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
    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
    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
    Nope, I don't have other...
    0
     
    LVL 12

    Expert Comment

    by:Ivanov_G
    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
    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
    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
    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
    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
    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
    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
    A perfect example that normalized databases (although they occupy less space) aren't easy to operate with ;-)
    0
     
    LVL 14

    Author Comment

    by:DragonSlayer
    Hi Geo ;-)

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

    Author Comment

    by:DragonSlayer
    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:
    Closed, 500 points refunded.

    modulo
    Community Support Moderator
    Experts Exchange
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

     Java Android Coding Bundle

    Whether you're an Apple user or Android addict, learning to code for the Android platform is an extremely valuable, in-demand skill. It all starts with Java, the language behind the apps and games that make Android the top platform it is today.

    Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
    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…
    With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…
    This video Micro Tutorial is the first in a two-part series that shows how to create and use custom scanning profiles in Nuance's PaperPort 14.5 (http://www.experts-exchange.com/articles/17490/). But the ability to create custom scanning profiles al…

    933 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

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now