Returning single column, multiple rows in a single row

Hi All,

I'm a SQL newbie, so please be gentle with me! We have a CRM system
with Potentials and Contacts, and my boss wants a report laid out with
one row per potential including contacts, the issue being that each
potential has zero or more contacts which are held in a separate table
linked by a contact-potential-relationship table, ! So I need to see
something like, potential name, amount, "contact list", notes, etc.
etc.

I searched the web and came across this solution in Oracle -
http://www.dba-oracle.com/t_display_..._same_rows.htm,
but I can't see how I can break the back of that solution to fit it
into MySQL. I also found a couple of solutions that required you to
know how many rows would be returned (how many contacts in my case),
but that's also not possible. There was also a solution that required
writing to a temporary table, but I have no experience of that, so I
got a bit lost. Any thoughts or help would be much appreciated.

TIA,

Russ,

On Thu, 08 May 2008 18:54:17 +0200, Redkins <russelledkins@gmail.com>
wrote:

> Hi All,
>
> I'm a SQL newbie, so please be gentle with me! We have a CRM system
> with Potentials and Contacts, and my boss wants a report laid out with
> one row per potential including contacts, the issue being that each
> potential has zero or more contacts which are held in a separate table
> linked by a contact-potential-relationship table, ! So I need to see
> something like, potential name, amount, "contact list", notes, etc.
> etc.
>
> I searched the web and came across this solution in Oracle -
> http://www.dba-oracle.com/t_display_..._same_rows.htm,
> but I can't see how I can break the back of that solution to fit it
> into MySQL. I also found a couple of solutions that required you to
> know how many rows would be returned (how many contacts in my case),
> but that's also not possible. There was also a solution that required
> writing to a temporary table, but I have no experience of that, so I
> got a bit lost. Any thoughts or help would be much appreciated.

A terrible one in pure MySQL indeed.
The way I usually solve this:

SELECT p.id, p.name, GROUP_CONCAT(c.contact_id SEPARATOR ',')
FROM potentials p
LEFT JOIN potentials_contacts c
ON p.id = c.potential_id
GROUP BY p.id

And if I need more information, depending on the circumstances, I either
join to contacts &format it allready with a
GROUP_CONCAT(CONCAT(last_name,', ' ,first_name SEPARATOR ' | '), or I keep
the list of id's in the using script, and fetch all their information with
one big WHERE id IN <string of comma seperated id's>.
--
Rik Wasmus

That's fantastic. I was expecting something hideously complex. Many
thanks Rik.

Russ.