Question

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

Asked by: DragonSlayer

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.

This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

  • Plus...
  • 30 Day FREE access, no risk, no obligation
  • Collaborate with the world's top tech experts
  • Unlimited access to our exclusive solution database
  • Never be left without tech help again

Subscribe Now

Asked On
2004-10-22 at 14:56:09ID21179419
Tags

result

,

ibobjects

,

query

Topic

Delphi Programming

Participating Experts
4
Points
0
Comments
22

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

  • "The time we save is the biggest benefit of Experts Exchange to Warner Bros. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange." Mike Kapnisakis, Warner Bros.
  • "Our team likes having a resource that is more secure than just using Google and most experts using this service really know their stuff. It's nice to look here first versus using Google." Dayna Sellner, Lockheed Martin
  • "Anytime that I've been stumped with a problem, 9 out of 10 times Experts Exchange has either the accepted solution or an open discussion of the potential solution to the problem." Kenny Red, eBay Inc.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.

Screenshot of Experts Exchange Knowledgebase

Need individual assistance?

Our experts are ready to help.

If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.

Screenshot of Experts Exchange Knowledgebase

Want to learn from the best?

Read articles from industry experts.

Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.

Screenshot of an Article

Working on a long term project?

Store your work and research.

Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.

Screenshot of Experts Exchange Knowledgebase

Access the answers to your technology questions today.

Subscribe Now

30-day free trial. Register in 60 seconds.

What Makes Experts Exchange Unique?

Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Trusted by the world's most respected brands.

image of each brand's logo

Faithfully serving IT professionals since 1996.

Experts Exchange Logo

Try it out and discover for yourself.

Subscribe Now

30-day free trial. Register in 60 seconds.

Related Solutions

  1. Firebird css problems
    Hello, if someone good with css could please have a look at the homepage www.vipcctv.com both with explorer and Firebird, the page looks as intended in explorer, but it's a bloody mess in Firebird, could someone please tell me what am I doing wrong? thanks, ark
  2. last_day function on firebird
    On Firebird which function we can use instead of last_day function on oracle.
  3. Charset Problem in Firebird
    Hi there I've got a big problem in my Firebird VB.net environment. I have a firebird databases witch i connect from VB.net over the Firebird .NET Provider 1.7.1 RC3. The FB Server is a 1.5.1 version server. There are tables with content äöüéàè ect. in the fields. If i read a...
  4. FireBird speed
    I have a program that use a FireBird database. All work perfect when navigate, but when insert new records I must spend to much time. For just 30 records with 15 fields I must wait 30-40 sec. Is normal ? In BDE is instantly.
  5. [VB.NET] Use a datagridview linked to a firebird db
    Hi, I use a datagridview linked to a dataset loaded by a table stored on a firebird db. The table contains a coluimn deifne as boolean but in the datagridview vb.net displays it as textbox and not as a checkbox column. I thik the datagridview considers the firebird boolean ...

Free Tech Articles

  1. WARNING: 5 Reasons why you should NEVER fix a computer for free.
    It is in our nature to love the puzzle. We are obsessed. The lot of us. We love puzzles. We love the challenge. We thrive on finding the answer. We hate disarray. It bothers us deep in our soul. W...
  2. SCCM OSD Basic troubleshooting
    SCCM 2007 OSD is a fantastic way to deploy operating systems, however, like most things SCCM issues can sometimes be difficult to resolve due to the sheer volume of logs to sift through and the dispe...
  3. Migrate Small Business Server 2003 to Exchange 2010 and Windows 2008 R2
    This guide is intended to provide step by step instructions on how to migrate from Small Business Server 2003 to Windows 2008 R2 with Exchange 2010. For this migration to work you will need the fo...
  4. Create a Win7 Gadget
    This article shows you how to create a simple "Gadget" -- a sort of mini-application supported by Windows 7 and Vista. Gadgets can be dropped anywhere on the desktop to provide instant information, ...
  5. Outlook continually prompting for username and password
    There have been a lot of questions recently regarding Outlook prompting for a username and password whilst using Exchange 2007. There are a few reasons why this would happen and I will try to cover t...
  6. Backup Exchange 2010 Information Store using Windows Backup
    There seems to be quite a lot of confusion around the ability to backup Exchange 2010 using the built in Windows Backup feature. This stems from the omission of this feature prior to Exchange 2007 s...

Cloud Class Webinars

  1. Avoiding Bugs in Microsoft Access
    Alison Balter takes and in-depth look at avoiding bugs in Access. In this webinar you will learn about using the immediate window to debug your applications, invoking the debugger, using breakpoints to troubleshoot, stepping through code, setting the next statement to execute, ...
  2. Top 10 Best New Features in Visio 2010
    Scott Helmers gives live demonstrations of the top 10 new features in Visio 2010. This webinar will teach you how to create compelling diagrams by adding shapes to the page with a single click, linking the shapes in a diagram to data in Excel (or SQL Server, or SharePoint), ...
  3. IT Consultant Business Secrets Revealed
    Michael Munger, Experts Exchange tech pro and IT consultant, pulls back the curtain on his very successful businesses and answers question on every IT consultant and business owner should know about. He shares secrets on what he did to solve the 5 most common problems in IT, ...
  4. Disaster Recovery and Business Continuity
    Quest CTO, Mike Billon, gives an overview of the steps involved in building a dunamic disaster recovery plan. Through case studies and an examination of software/hardware tooles for monitoring and testing, you'll gain a better understandin of where you are, where you want ...
  5. Organize Your Visio Diagrams with Containers and Lists
    Scott Helmers uses cross functional flowcharts, wireframe diagrams, data graphic legends and seating charts to teach you: how to ustilize all three new structured diagram components in Visio 2010, the best practices for organizeing shapes in previous version of Visio, how to organize ...
  6. How to Us Objects, Properties, Events and Methods in Microsoft Access
    Alison Dalter gives an in-depbth look at objects, properties, events and methods in Microsoft Access. In this webinar you will learn about using the object browser, referring to objects, working with properties and methods, working with object variables, understanding the ...

Join the Community

Give a Little. Get a Lot.

Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.

Join the Community

Answers

 

by: DragonSlayerPosted on 2004-10-22 at 14:57:00ID: 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!

 

by: Ivanov_GPosted on 2004-10-23 at 02:16:48ID: 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

 

by: DragonSlayerPosted on 2004-10-23 at 10:36:47ID: 12388961

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

 

by: VoodoomanPosted on 2004-10-23 at 12:24:38ID: 12389501

Comment:

Is this homework/college work?

Voodooman

 

by: DragonSlayerPosted on 2004-10-23 at 17:42:30ID: 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.

 

by: Ivanov_GPosted on 2004-10-24 at 00:40:29ID: 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 ...

 

by: DragonSlayerPosted on 2004-10-24 at 04:18:16ID: 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...

 

by: Ivanov_GPosted on 2004-10-24 at 08:52:25ID: 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

 

by: DragonSlayerPosted on 2004-10-24 at 09:45:40ID: 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?

 

by: Ivanov_GPosted on 2004-10-24 at 10:04:04ID: 12394244

Nope, I don't have other...

 

by: Ivanov_GPosted on 2004-10-24 at 10:09:22ID: 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...

 

by: DragonSlayerPosted on 2004-10-24 at 10:13:01ID: 12394281

well, that's because the real database I'm accessing is actually from another software, and my software is only granted certain permissions.

 

by: VoodoomanPosted on 2004-10-24 at 12:07:01ID: 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

 

by: DragonSlayerPosted on 2004-10-24 at 23:00:11ID: 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.

 

by: Ivanov_GPosted on 2004-10-24 at 23:42:21ID: 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...

 

by: DragonSlayerPosted on 2004-10-24 at 23:50:49ID: 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.

 

by: Ivanov_GPosted on 2004-10-25 at 00:06:33ID: 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...

 

by: geobulPosted on 2004-10-25 at 01:11:57ID: 12398173

A perfect example that normalized databases (although they occupy less space) aren't easy to operate with ;-)

 

by: DragonSlayerPosted on 2004-10-25 at 04:29:24ID: 12399028

Hi Geo ;-)

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

 

by: DragonSlayerPosted on 2004-11-25 at 09:45:49ID: 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?

 

by: moduloPosted on 2004-12-07 at 06:59:20ID: 12764365

Closed, 500 points refunded.

modulo
Community Support Moderator
Experts Exchange

20120131-EE-VQP-002

3 Ways to Join

30-Day Free Trial

The Experts

98% positive feedback on 31,087 answers since March 2000. angeliii is a Microsoft Most Valuable Professional for his work with MS SQL Server & Develoment.

He has also proven his knowledge of Visual Basic Programming, PHP Scripting and Oracle Databases.

The Experts

97% positive feedback on 10,752 answers since July 2000. lrmoore has more than 18 years experience in the networking industry.

The six-time Mircosoft MVPs specialties include firewalls, virtual private networking, and network management.

Testimonials

"...and excellent source for support... Kind of like having your very own IT dept." Electriciansnet

Testimonials

"I was apprehensive at signing up at first. However... it has already made my life as an IT administrator much easier." JaCrews

Testimonials

"WOW! You guys have great, active, and knowledgeable people on here." moore50

Business Clients

Business Clients

In the Press

"If you’ve got a question... Experts Exchange can supply an answer.”

In the Press

"...an invaluable aid for both IT professionals and those who require tech support."

In the Press

"where IT professionals provide quick answers on just about any topic"

Business Account Plans

Loading Advertisement...