2

first table : "wp_pc_products"

feed_product_name   feed_product_image   price   deeplink    id_merchant
sony xperia c3      abc.png              12000   abc.html     fkt
sony xperia         xyz.png              11000   aaa.html     snd
sony xperia M       pqr.png              10000   bbb.html     fkt
sony xperia z       abc.png              12000   abc.html     fkt
sony xperia z3      xyz.png              11000   aaa.html     snd
sony xperia k       pqr.png              10000   bbb.html     fkt

second table: "wp_pc_products_merchants"

slug     image
fkt      logo1.png
snd      logo2.png

My existing query

SELECT p.feed_product_name,
       p.feed_product_image,
       p.price,
       p.deeplink,
       p.id_merchant,
       m.image
FROM wp_pc_products p
JOIN wp_pc_products_merchants m ON m.slug=p.id_merchant
AND (feed_product_name LIKE'%sony%'
     OR feed_product_name LIKE'%xperia%'
     OR feed_product_name LIKE'%c3%')
AND price BETWEEN "11829.3" AND "21968.7"
GROUP BY id_merchant

Above query is not working with group by clause. How can i use group by in above query

shopeeon
  • 151
  • 2
  • 3
  • 14
  • You should pretty much always group by any and all values you want returned excepting things like `max()` or `greatest()`. Also, in order to get the highest single value, you're going to want to wrap the column in `max()`. – Richard Theobald Feb 04 '16 at 05:38
  • You cannot get the results you want using `GROUP BY`. Look at your query and the data. For `id_merchant = 'fkt'` there are at least two different values for `feed_product_name`. Which one should `MySQL` select in your query for the group having `id_merchant = 'fkt'`? Such a query is not even valid SQL. `MySQL` [accepts it](http://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html) until version 5.7.5 and picks one of the values. It cannot guarantee that the query will always return the same value for `p.feed_product_name`. – axiac Feb 04 '16 at 07:06
  • i was using this query before=> SELECT wp_pc_products.*,wp_pc_products_merchants.image,cashback.percentage FROM wp_pc_products JOIN wp_pc_products_merchants ON wp_pc_products.id_merchant = wp_pc_products_merchants.slug JOIN cashback ON wp_pc_products.shipping = cashback.category_name AND wp_pc_products.id_merchant = cashback.merchant WHERE feed_product_name LIKE '%sony%' and feed_product_name LIKE '%xperia%' and feed_product_name LIKE '%c3%' group by id_merchant – shopeeon Feb 04 '16 at 07:26
  • it was working fine for me. In similar fashion i want one product from one merchant this query was looking for a match in order of position like first word then second so on. Now we modify it to random match that is the only difference. – shopeeon Feb 04 '16 at 07:29

1 Answers1

-1

Change your query to

select distinct(m.image),p.feed_product_name,p.feed_product_image,p.price,p.deeplink from wp_pc_products p JOIN wp_pc_products_merchants m ON m.slug=p.id_merchant and (p.feed_product_name like'%sony%' or p.feed_product_name like'%xperia%' or p.feed_product_name like'%c3%' ) and p.price BETWEEN "11829.3" and "21968.7" group by p.id_merchant

Or

select distinct(m.image),p.feed_product_name,p.feed_product_image,p.price,p.deeplink from wp_pc_products p JOIN wp_pc_products_merchants m ON m.slug=p.id_merchant where (p.feed_product_name like'%sony%' or p.feed_product_name like'%xperia%' or p.feed_product_name like'%c3%' ) and p.price BETWEEN "11829.3" and "21968.7" group by p.id_merchant
Md. Sahadat Hossain
  • 3,210
  • 4
  • 32
  • 55
  • forget distinct(m.image) from above query that i edited, then suggest me the best one. I also trying above two queries that you suggest – shopeeon Feb 04 '16 at 06:09
  • I think second one would be best – Md. Sahadat Hossain Feb 04 '16 at 06:19
  • Its not my expected result. I have id_merchant column in wp_pc_product table and its repeating the name of merchant and I have slug column in wp_pc_product_merchant table in which merchant name is appearing once this is the only relation i have in both the table. I am expecting one result from each merchant. – shopeeon Feb 04 '16 at 06:57
  • why '-1' Can you please explain? – Md. Sahadat Hossain Feb 06 '16 at 06:04