Query problem

[mattofdoom]

Hello everyone, I'm a relative newbie to mysql, and find myself having to form a rather complicated query that is above my current ability. I know it's possible, however I'm not sure how to find out any more infomation, so after a few hours of fustrated googleing, I thought I'd try here.

 

First off, my server version is 5.0.45.

 

I'm modifying sombody elses old code here, and they have three tables set up (which concern this problem):

 

Case:

id_casetitle

1x

2y

3z

This table records cases, each with an ID, a title and a pile of other fields.

 

Keyword:

id_keywordid_grouptitle

11tall

21short

32red

42blue

This table records keywords. Keywords have a unique ID, and another number describing which "group" the keyword is from. Also, a title.

 

Each case may have a number of keywords associated with it, however it will only have one keyword from each keyword group.

 

These assoications are managed by a thrid table:

Case_keyword:

id_caseid_keyword

11

14

22

24

32

33

 

As you can see, each case as two keywords asigned to it (in this example), one keyword from group one, and one keyword from group two, so case x is tall and blue, case y is short and blue, and so on.

 

My question is, I want to form a query where the result is something like this:

case.id_casecase.title12

1xlongblue

2yshortblue

3zshortred

 

Whereby, each keyword that is associated with a pitcular case is shown in the same row (perhaps organised into columns by id_group). Problem is, I have no idea how to go about doing this!

 

If anyone can give me any help, or just point me in the right direction, I would be very grateful!

-Matt

[fenway]

GROUP_CONCAT() may help you out.

[mattofdoom]

Thanks for the tip, I'll read up on that.

 

However, any other hints and tips people might offer would allways be welcome 

 

-Matt

[mattofdoom]

Hi again,

 

Thanks again fenway for the tip. I've looked up what you sugested, and have almost got it doing exactly what I want!

 

You recall that it was my desire to get a final table something like this:

 

case.id_casecase.title12

1xlongblue

2yshortblue

3zshortred

 

And using GROUP_CONCAT, I have managed to get this:

 

case.id_casecase.titleGROUP_CONCAT(..)

1xlong, blue

2yshort, blue

3zshort, red

 

Clearly heading in the right direction, however you'll notice that the result of the GROUP_CONCAT is all sitting togther in one column, seperated by commas. Is there any feasable method to seperate that column out into two?

 

The query I am using is:

 

SELECT

c.id_case,

c.title,

GROUP_CONCAT(kw.title ORDER BY kc.id_keyword SEPARATOR ', ')

FROM keyword_case AS kc

LEFT JOIN lcm_keyword AS kw ON kc.id_keyword = kw.id_keyword

LEFT JOIN lcm_case AS c ON kc.id_case = c.id_case

GROUP BY kc.id_case

ORDER BY kc.id_case

;

 

Any more tips welcome,

-Matt.