SEO

#1 (**permalink**) 02-28-2008, 08:35 AM

Hello!

I've got a little problem with mySQL syntax.

I have two tables : candidate and CV_items. Cardinality is : for 1
candidate you can have multiple CV_items

I'd like to Select in ONE SINGLE ROW the id of the candidate
(id_candidate) and ALL the CV_items associated.
so far I used : "SELECT * FROM candidate c RIGHT JOIN cv_items
ON c.ID_CANDIDATE=cv.ID_CANDIDATE" , it gives me the infos I need BUT
on separated rows .

#2 (**permalink**) 02-28-2008, 08:35 AM

On 15 Nov 2006 10:18:56 -0800, reversed.K@gmail.com wrote:
> Hello!
>
> I've got a little problem with mySQL syntax.
>
> I have two tables : candidate and CV_items. Cardinality is : for 1
> candidate you can have multiple CV_items
>
> I'd like to Select in ONE SINGLE ROW the id of the candidate
> (id_candidate) and ALL the CV_items associated.
> so far I used : "SELECT * FROM candidate c RIGHT JOIN cv_items
> ON c.ID_CANDIDATE=cv.ID_CANDIDATE" , it gives me the infos I need BUT
> on separated rows .

The key word you're looking for is "pivot". I'll skip the rest of the
answer as I've never done them under MySQL.

--
Life does not cease to be funny when people die any more than it ceases
to be serious when people laugh.
-- George Bernard Shaw

#3 (**permalink**) 02-28-2008, 08:36 AM

reversed.K@gmail.com wrote:
> Hello!
>
> I've got a little problem with mySQL syntax.
>
> I have two tables : candidate and CV_items. Cardinality is : for 1
> candidate you can have multiple CV_items
>
> I'd like to Select in ONE SINGLE ROW the id of the candidate
> (id_candidate) and ALL the CV_items associated.
> so far I used : "SELECT * FROM candidate c RIGHT JOIN cv_items
> ON c.ID_CANDIDATE=cv.ID_CANDIDATE" , it gives me the infos I need BUT
> on separated rows .

I'll offer you a better key word :-)

Take a look at the GROUP_CONCAT function.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

SQL syntax problem