Query Help

[Canman2005]

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

[Canman2005]

Well that advert didnt help any ideas anyone?

[Canman2005]

Can anyone help? Still trying to crack this one

[zenag]

can u show database struct and desired

output struct

 

--fenway: stop using code tags for everything.

[Canman2005]

It's just that the databases and code connected are quite large.

 

Is my message confusing?

[fenway]

You can not use * with GROUP BY.

[Barand]

Also "SELECT * " restricts the help we can can give as we have no idea which tables those output columns are coming from

[ashishag67]

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

[fenway]

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...