sql join problem

[vladua]

Hey all,

I wonder if anyone could help me with my problem.

I have this query

$query = "

SELECT 
r.artist as artist, 
r.release_date as release_date, 
r.type as type, 
r.price as price, 
r.title as title, 
r.label as label, 
r.id as id, 
r.record_desc as record_desc, 
r.genre as genre, 
r.sub_genre as sub_genre, 
r.quantity as quantity, 
r.sku as sku, 
p.id as pid, 
g.name as genre_name 

FROM 

mdjs_records as r, 
mdjs_products as p, 
mdjs_records_genres as g 

WHERE p.type = 'rec' 

AND r.id = p.prod_id 
AND g.id = r.genre 
AND r.genre = '5'

ORDER BY 
g.name, 
r.sub_genre,
r.release_date DESC 
";

the mdjs_records_genres table records look like this:

ID, NAME, PARENT_ID

so we have stored a genre and a sub_genre in one table using parent id.

In the query i need to return a genre and a sub_genre name for each item, but that is the same column in the records_genres table.

Is there anyway I can use an IF to assign the sub_genre name?

Thanks for all your help.

[sivasankari_kv]

Why don't u do a self join with that table like

inner join mdjs_records_genres g on g.id = g.parent_id

[ultraslacker]

You need two instances of the genres table.

JOIN mdjs_records_genres AS g
ON r.genre = g.id
JOIN mdjs_records_genres AS g_sub
ON r.sub_genre = g_sub.id

[vladua]

Ok i got that, but some items do not have subgenres and only genres but also have to be selected. Thats my biggest problem 🙁

Thanks for the help tho.
-vlad

[ultraslacker]

Then change the second JOIN to a LEFT JOIN