Skip to main content
A
Anonymous
October 28, 2010
Answered

How to display a db record with a variable number of joined records

  • October 28, 2010
  • 2 replies
  • 494 views

I'm building a website for an airshow http://www.hollisterairshow.com and the organiser would like to post a list of things he needs for the show and have people respond via the website. For example, he might need 20 tents and potentially up to 20 people could respond, each offering one tent, or fewer people could each offer several tents. I'd like to create a view showing the item needed and underneath it one row for each offer he receives. I already have a couple of tables defined, one called "needs" and the other called "offers" , the "offers" table has a column called "needId" which is the index from the needs table.

So far I have created a recordset to join the tables but each row contains the need and the offer so I'm seeing the need repeated on each row and I only want to see it once. Here's the SQL generated by DW CS4

SELECT needs.needId, needs.title, needs.`description`, needs.quantity, needs.needMet, offers.needId, offers.offerId, offers.name, offers.email, offers.phone, offers.quantity, offers.`comment`
FROM needs, offers
WHERE needs.needId = offers.needId
ORDER BY needs.title

I'm sure there must be a simple solution to this but I'm unable to figure it out. I'm new to SQL and doing this as a volunteer for the airshow.

Thanks,

Tony

This topic has been closed for replies.
Correct answer bregent

>Is there a way to do this? Would I

>need a different recordset or am I still

>looking at a layout issue?

You do not need a different recordset. It's only a slight modification. For each record, test if the ID has changed. If it has, print the header, a line break, and then the row details. If it has not, print only the row details.

2 replies

A
Anonymous
October 28, 2010

David explained the solution with php example in post #3 of the following thread:

http://forums.adobe.com/thread/739317

A
Anonymous
October 29, 2010

Thanks so much, for pointing this out. This looks like it'll do what I need. I think I understand the code so I'll modify it and try it.

It looks like it will create an output that looks something like this

Need1    Offer 1

             Offer 2

             Offer 3

Need 2     Offer 1

               Offer 2

etc....

This is certainly workable. Ideally I'd like to create something like this

Need 1

     Offer 1

     Offer 2

     Offer 3

Need 2

     Offer 1

     Offer 2

Is there a way to do this? Would I need a different recordset or am I still looking at a layout issue?

Thanks again for the help.

Tony

B
bregentCorrect answer
Participating Frequently
October 29, 2010

>Is there a way to do this? Would I

>need a different recordset or am I still

>looking at a layout issue?

You do not need a different recordset. It's only a slight modification. For each record, test if the ID has changed. If it has, print the header, a line break, and then the row details. If it has not, print only the row details.

B
bregent
Participating Frequently
October 28, 2010

>I'm new to SQL and doing this

>as a volunteer for the airshow.

This isn't really a SQL problem, it's a server side scripting problem. If you have a one-to-many relationship then your query will have to return 'need' data mulitple times. What you want to do is supress the display of that data. You can do this by comparing the needId in the loop from the previous needId and only displaying the data when it has changed.

A
Anonymous
October 29, 2010

Hi,

Thanks for helping me out - again, I appreciate the direction. Please see my reply to the other response to this question.

Tony

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices