SQL basic problem, apparently...

From: Jay <jerome.avoustin_at_insa-lyon.fr>
Date: 15 Mar 2005 09:02:25 -0800
Message-ID: <349f4455.0503150902.54413cfa@posting.google.com>

Hi !

Here it is :
Imagine this situation :
I've got 3 tables. A person can have several jobs (imagine... ;o). * means primary key
PERSON : IdPers*, FirstName, LastName
WORK : IdPers*, IdJob*
JOB : IdJob*, JobLabel

If I request my database to have all the jobs of everybody, I get that :

---

Firstname LastName JobLabel

Jean        DUPONT     Baker
Jean        DUPONT     Barman
Bernard     MARTIN     Baker
Bernard     MARTIN     Plumber
Bernard     MARTIN     Farmer
Bernard     MARTIN     Webmaster

etc...

---

This is very easy.
but what I expect is something like :

---

Firstname LastName Jobs

Jean        DUPONT     Baker, Barman
Bernard     MARTIN     Baker, Plumber, Farmer, Webmaster

etc...

---

You'll tell me that it is easy to get whith a GROUP BY ont the person, but which grouping function can I use to "concat" the Jobs in a typical sql query ??? Is there a non-"GROUP BY"-based solution ??

To help you, I don't know the maximum of jobs of a person.

Thanks to help me :o) Received on Tue Mar 15 2005 - 11:02:25 CST