Is there any problem in this query

[asamaiya]

p.products_image, p.products_quantity, p.products_id, p.products_msrp, p.products_price, p.products_tax_class_id,
m.manufacturers_id, pd.products_name,
IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price,
IF(s.status, s.specials_new_products_price, p.products_price) as final_price
from

products p left join manufacturers m using(manufacturers_id) left join specials s on p.products_id = s.products_id,

products_description pd,
categories c,
products_to_categories p2c ,
category_attributes_product_creation capc

where p.products_status = '1' and p.permission= '1' and pd.language_id = '1'

and p.products_id = pd.products_id
and p.products_id = p2c.products_id
and p2c.categories_id = c.categories_id


and c.categories_id = capc.category_id


and ((pd.products_name like '%rio%' or p.products_model like '%rio%' or m.manufacturers_name like '%rio%') )

and capc.product_id in(SELECT capc.product_id FROM `category_attributes_product_creation` capc WHERE capc.options_chaecked = '23') group by p.products_id order by pd.products_name

:confused:

help would be appreciated ..

there is no error but it is giving desired result ??

thanks

[Fumigator]

Does it give you an error? If so what is the error?

[asamaiya]

there is no error but still there is no desired result ??

[Fumigator]

You need to give us your schema and then describe what results you are trying to achieve for us to have any hope of giving you any useful feedback.

I can tell you, when you use GROUP BY and you include columns in your SELECT clause that are not in your GROUP BY clause, the stuff that is returned is going to be pretty random. That may be acceptable, but it could cause problems.

[asamaiya]

select distinct
p.products_image, p.products_quantity, p.products_id, p.products_msrp, p.products_price, p.products_tax_class_id,
m.manufacturers_id, pd.products_name,
IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price,
IF(s.status, s.specials_new_products_price, p.products_price) as final_price
from

products p left join manufacturers m using(manufacturers_id) left join specials s on p.products_id = s.products_id,

products_description pd,
categories c,
products_to_categories p2c ,
category_attributes_product_creation capc

where p.products_status = '1' and p.permission= '1' and pd.language_id = '1'

and p.products_id = pd.products_id
and p.products_id = p2c.products_id
and p2c.categories_id = c.categories_id

group by p.products_id order by pd.products_name




now I want to add follwing query in that query

SELECT *
FROM `category_attributes_product_creation`
WHERE `options_chaecked` =23
..now i want to make a query from which include both of query .....which i did on my 1st post .... some times it's just given the result of 1st query only

thanks for showing interest FUMIGATOR

[asamaiya]

will this query performance better than 1st I post in this thread ..??

select distinct p.products_image, p.products_quantity, p.products_id, p.products_msrp, p.products_price, p.products_tax_class_id,
m.manufacturers_id, pd.products_name,
IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price,
IF(s.status, s.specials_new_products_price, p.products_price) as final_price,
IF(CD.category_discount_status, CD.category_discount_price, NULL) as specials_new_products_price12,
IF(CD.category_discount_status, CD.category_discount_price, p.products_price) as final_price12 ,
CD.category_discount_expires_date ,
capc.category_id,capc.options_id,capc.options_values_id,capc.set_omn_product_add ,capc.options_chaecked

from

products p left join manufacturers m using(manufacturers_id) left join specials s on p.products_id = s.products_id,

products p left join manufacturers m using(manufacturers_id)
left join specials s on p.products_id = s.products_id
join products_description pd on p.products_id=pd.products_id
join products_to_categories p2c on p.products_id = p2c.products_id
left join category_discount CD on p2c.categories_id = CD.category_id ,category_attributes_product_creation capc

where
p.products_status = '1' and p.permission= '1' and pd.language_id = '1'
and (p2c.categories_id = '114')
and (pd.products_name like '%cc%' or p.products_model like '%cc%' or m.manufacturers_name like '%cc%')
and p.products_id in(SELECT capc.product_id FROM `category_attributes_product_creation` capc WHERE capc.options_chaecked = '80') group by p.products_id order by p.products_price asc, pd.products_name



and capc.product_id in(SELECT capc.product_id FROM `category_attributes_product_creation` capc WHERE capc.options_chaecked = '23') group by p.products_id order by pd.products_name

[bucket]

Can you please unhighlight it and put it in the

BBCode called PHP


PUT UR MYSQL CODE HERE