Re: merging multiple records into one

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Alex Vorobiev (sasha_at_mathforum.com)
Date: 06/07/04 

Date: 7 Jun 2004 11:58:05 -0700

bob,

thank you for your reply.
the projects table is the only one that contains project information.
projects2persons is just a linking table, projects2persons contains
only foreign keys from both projects and persons table.

so, to get all projects, i don't need a union 'cause all the project
info is already in the project table. the problem is with persons.
there is an arbitrary number of persons that can be associated with
each project. if i am doing a simple join on the three tables, i will
get multiple records for each project that has more than one person.

i am guessing it is possible to write a stored procedure that will
loop through all projects, fetch the number of persons for each
project, and then concatenate their names into one string. seems like
a lot of work for something that i imagine would be a fairly common
reporting task. is there an easier way in crystal to group
information like this?

thanks

"Bob Holmes" <rholmes@REMOVEmmwec.org> wrote in message news:<utLFxmlSEHA.1508@TK2MSFTNGP11.phx.gbl>...
> Hi Sasha,
> If you need to get a list of the projects that are in 'table projects'
> and projects that are in table 'projects2persons' and display them as a
> single list, I think you might benefit by using a database method, such as a
> view or stored procedure. By doing this, you can create a query using a
> 'UNION ALL' join which will return all of the projects no matter which table
> they are in. You might also have to join to this the 'projects2persons'
> table a second time in order to get a second record for the project, which
> will allow you to get the second person_id.
> I hope this is enough to get you started. If you have specific questions as
> you start to build this, post them here.
>
> --
> Bob Holmes MCNGP #31
> "Alex Vorobiev" <sasha@mathforum.com> wrote in message
> news:e9b2000a.0406040751.580883bb@posting.google.com...
> > the subject doesn't do a good job of describing it, but here it
> > goes...
> >
> > crystal 8.5, ms sql server 7.0
> >
> > table projects (project_id)
> > table persons (person_id)
> > table projects2persons (project_id, person_id)
> >
> > projects to persons is many to many. there are zero or more persons
> > per project. my goal is to return all distinct projects, and return
> > all persons for each project more or less in the following format:
> >
> > project 1
> > persons: one, two, three
> >
> > project 2
> > persons: one, three, five, nine
> >
> > what is the best way in crystal to return one record for each project
> > that would list all persons associated with the project.
> >
> > thanks,
> >
> > --sasha

Relevant Pages

  • Re: Do u have what it takes to be a real mariner?
    ... mariner career bob and how you avanced yourself. ... Show your lack of pratical knowledge Bob. ... I fa perso ndoes the homework union shipping has excellent pay. ...
    (rec.boats.cruising)
  • Re: Guardian reports failing safety standards uncovered by Grayrigg enquiry
    ... I trust Bob Crow and his chums are doing their very best to assist the ... police in their investigations. ... course of an enquiry - and wouldn't instructing members not to ... In this case shouldn't the union be assisting in bringing forward ...
    (uk.railway)
  • Re: Checking Database for Existence
    ... > Chris Guimbellot wrote: ... >> to create a stored procedure, but the code to check for existence)? ... > Bob Barrows ... > quicker response by posting to the newsgroup. ...
    (microsoft.public.scripting.vbscript)
  • Re: Output Parameters Return Nothing
    ... >> when I query the output parameters, they return a value of Nothing. ... >> the stored procedure in Query Analyzer, ... >> Bob L. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: How To Use Remote_Host in a query?
    ... WOW Bob you went way beyond help on this. ... My preference would be to create a stored procedure in your sql server ... response.write "IP address was not provided in the request" ... dim cn,cmd,rs,ip, sql ...
    (microsoft.public.sqlserver.programming)