Canman2005 Posted December 3, 2008 Share Posted December 3, 2008 Hi all I have the following QUERY SELECT * FROM product_category_associations INNER JOIN products ON product_category_associations.product_id = products.id AND products.date_available <= '2008-11-05' WHERE products.product LIKE '%Sample%' AND `december` !=0 AND product_category_associations.live =1 GROUP BY `linked_id` ORDER BY product_category_associations.live DESC It returns something similar to `product` `linked_id` `live` iPod Nano 232 1 iPod Classic 112 1 there is one more row in the database, but as it has a `live` value of 0, then it does not display (but for the record that row is) `product` `linked_id` `live` iPhone 232 0 If you notice, the 'iPhone' and 'iPod Nano' share the same `linked_id` (232) but because the 'iPhone' has a `live` value of 0 it's not returned in the main QUERY. How can I alter my QUERY so that if the database finds that any rows with indentical `linked_id` values, and either of them have a `live` value of 0, then they won't display either, so in this case, because the `iPhone` has the same `linked_id` as the `iPod Nano`, but the `iPhone` has a `live` value of 0, then it won't display either of those rows, so if I ran the main QUERY again, it would just return `product` `linked_id` `live` iPod Classic 112 1 Can anyone help? Thanks Link to comment https://forums.phpfreaks.com/topic/135319-query-help/ Share on other sites More sharing options...
Canman2005 Posted December 3, 2008 Author Share Posted December 3, 2008 Well that advert didnt help any ideas anyone? Link to comment https://forums.phpfreaks.com/topic/135319-query-help/#findComment-705033 Share on other sites More sharing options...
Canman2005 Posted December 4, 2008 Author Share Posted December 4, 2008 Can anyone help? Still trying to crack this one Link to comment https://forums.phpfreaks.com/topic/135319-query-help/#findComment-705698 Share on other sites More sharing options...
zenag Posted December 4, 2008 Share Posted December 4, 2008 can u show database struct and desired output struct --fenway: stop using code tags for everything. Link to comment https://forums.phpfreaks.com/topic/135319-query-help/#findComment-705716 Share on other sites More sharing options...
Canman2005 Posted December 4, 2008 Author Share Posted December 4, 2008 It's just that the databases and code connected are quite large. Is my message confusing? Link to comment https://forums.phpfreaks.com/topic/135319-query-help/#findComment-705785 Share on other sites More sharing options...
fenway Posted December 4, 2008 Share Posted December 4, 2008 You can not use * with GROUP BY. Link to comment https://forums.phpfreaks.com/topic/135319-query-help/#findComment-706223 Share on other sites More sharing options...
Barand Posted December 6, 2008 Share Posted December 6, 2008 Also "SELECT * " restricts the help we can can give as we have no idea which tables those output columns are coming from Link to comment https://forums.phpfreaks.com/topic/135319-query-help/#findComment-707557 Share on other sites More sharing options...
ashishag67 Posted December 6, 2008 Share Posted December 6, 2008 Try this code in case if it works SELECT * FROM product_category_associations, products where product_category_associations.product_id = products.id AND products.date_available <= '2008-11-05' and products.product LIKE '%Sample%' AND `december` !=0 AND product_category_associations.live =1 GROUP BY `linked_id` ORDER BY product_category_associations.live DESC For mysql help for visit at http://mysqlandphp.net Link to comment https://forums.phpfreaks.com/topic/135319-query-help/#findComment-707580 Share on other sites More sharing options...
fenway Posted December 8, 2008 Share Posted December 8, 2008 Try this code in case if it works SELECT * FROM product_category_associations, products where product_category_associations.product_id = products.id AND products.date_available <= '2008-11-05' and products.product LIKE '%Sample%' AND `december` !=0 AND product_category_associations.live =1 GROUP BY `linked_id` ORDER BY product_category_associations.live DESC For mysql help for visit at http://mysqlandphp.net How is that helpful? We just metioned above that you CAN'T use * with GROUP BY... Link to comment https://forums.phpfreaks.com/topic/135319-query-help/#findComment-708927 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.