Reputation: 9645
SELECT `a`.`department`, `a`.`category`, `a`.id,
(SELECT group_concat(t_0) FROM images WHERE ad_id=a.id) t_0,
(SELECT group_concat(t_1) FROM images WHERE ad_id=a.id) t_1,
(SELECT group_concat(t_1) FROM images WHERE ad_id=a.id) t_8
FROM `ads` AS `a`
WHERE (a.department = 1) AND (a.category = 12) AND (a.charged=1)
ORDER BY `a`.`id` DESC
Is there better solution than this? I need to have all images from images table for each a.id(ad_id)
Thanks :)
edit...
aha it seems this is working:
SELECT `a`.`department`, `a`.`category`, `a`.id, group_concat(t_0), group_concat(t_1), group_concat(t_8)
FROM `ads` AS `a`
LEFT JOIN images i ON i.ad_id=a.id
WHERE (a.department = 1) AND (a.category = 12) AND (a.charged=1)
GROUP BY a.id DESC
Don't know if this is right solution thou... :)
Upvotes: 1
Views: 507
Reputation: 29874
your second approach looks not so bad but it depends on the size of tables and the number of successful joins... this will probably perform faster if you have a lot of imagse, but then you have only the images and have to link them to the other information in a second run:
select t_0,t_1,t_8,ad_id from images where ad_id is in (select id from ads WHERE department = 1 AND category = 12 AND charged=1)
to sum it up if i understand your usecase correctly: its better to first see which ads need to be displayed, and then fetch the images for exactly this ad without any joins!
Upvotes: 0
Reputation: 28686
I don't think it's a good solution because you use group_concat function and the function has the limit known as group_concat_max_len. Therefore it may happen that you won't get the whole result. Even if the limit is high I would rather not use the group_concat because you may move your application to another server and the settings may be different.
I would simply divide the query in simpler queries and process them in PHP (if it is your case). Premature optimazation is not good.
Upvotes: 2