[SOLVED] JOIN or something else to get multi records from second table

[CrustyDOD]

Hello!

 

I have a slight problem that i just figured out.

 

Since i'm naming fields in my own language, i'll just use a general example. Same structure tho.

 

table 1: CD info
(fields)
- id
- cd_title
- date_added

table 2: Music Genres
(fields)
- id
- genre_name

table 3: CD Genres
(fields)
- id
- cd_id (ID of CD record)
- genre_id (ID of genre record)

 

Table 1 contains list of CD's and each CD has 1 or more genres. Is it possible to get genre_name for each record with 1, 2 or 3 queries in total?

 

Basically table 3 is just a link between table 1 and table 2.

 

Right now, i first get let's say 50 CD's from table 1 and then inside a loop i do another query that get's CD Genres.

 

NOT ACTUAL CODE.. But it shows the current way

 

"Select * from table1
limit 50"

LOOP
(
"select * from table3
left join table 2 on table2.id = table3.genre_id
where table3.cd_id = 1"
)

This is just bad! I get 51 queries per page load.

[Barand]

you only need one query

SELECT cd.cd_title, GROUP_CONCAT(g.genre_name) AS genres
FROM table1 AS cd
    JOIN table3 AS cdg ON cd.id = cdg.cd_id
    JOIN table2 AS g ON cdg.genre_id = g.id
GROUP BY cd.cd_title

[CrustyDOD]

That is exactly what i want!

 

1 problem tho.

 

5 total, Query took 200.1998 sec

 

Limit was set to 5, tables contain (total records):

CD table: 129,997

genre table: 21

CD_Genres: 324,516

 

Index keys are set to fields that i'm looking...

 

Any ideas?

 

Oh and i tried this on mysql 5 version

[Barand]

I assume that the ids are the primary keys on table1 and table2. Do you have indexes on cd_id and genre_id on table3?

[CrustyDOD]

Yeah.

 

Running optimize solved it  Takes half a second now.

 

[Barand]

Quite an improvement!

[CrustyDOD]

Yeah, its actually my bad. Before going live i wanted to see how queries will hold againts large amount of data, so i inserted fake data to tables and forgot to optimize. Shit happens i guess.

 

Thanks for the GROUP_CONCAT solution!