-2

query 1:

select products 
from buyde_deal 
where displayflag = '1' 
and end_date> now() 
and start_date < now() limit 1

output : query 1 output

query 2: 

SELECT id,productname ,cat_id ,subcat_id,shortdescription1,shortdescription2,shortdescription3 ,sellingprice,sellpricevat,mrp,regularprice,costprice,sku,qty,pweight,seller_id,shippingcost,color,size,discount 
FROM `buyde_product` 
WHERE id IN ( 
    select products 
    from buyde_deal 
    where displayflag = '1' 
    and end_date> now() 
    and start_date < now() ) 
ORDER BY `buyde_product`.`id` "

output :query 2 output

If i run the second query, only one record is returned. I need all the records from table 1 .

Sᴀᴍ Onᴇᴌᴀ
  • 8,218
  • 8
  • 36
  • 58
inrsaurabh
  • 602
  • 1
  • 8
  • 17
  • 1
    Possible duplicate of [Select by varchar column with IN() part in condition and int value returns all rows](http://stackoverflow.com/questions/2064766/select-by-varchar-column-with-in-part-in-condition-and-int-value-returns-all-r) – Sᴀᴍ Onᴇᴌᴀ Feb 27 '17 at 07:34

1 Answers1

0

Try to use find_in_set:

SELECT id, 
       productname, 
       cat_id, 
       subcat_id, 
       shortdescription1, 
       shortdescription2, 
       shortdescription3, 
       sellingprice, 
       sellpricevat, 
       mrp, 
       regularprice, 
       costprice, 
       sku, 
       qty, 
       pweight, 
       seller_id, 
       shippingcost, 
       color, 
       size, 
       discount 
FROM   `buyde_product` 
       JOIN (SELECT products 
             FROM   buyde_deal 
             WHERE  displayflag = '1' 
                    AND end_date > Now() 
                    AND start_date < Now()) t 
         ON FIND_IN_SET(`buyde_product`.`id`, t.products) 
ORDER  BY `buyde_product`.`id`
Blank
  • 12,308
  • 1
  • 14
  • 32
  • @SaurabhRanjan FIND_IN_SET is the savior to non-1NF database tables. https://en.wikipedia.org/wiki/First_normal_form If you have the authority/capacity to modify the table, move csv columns into a separate table – mickmackusa Feb 27 '17 at 07:35
  • First of all Thank You man. You gave me one more method to solve this problem .
    Can you please tell me what do you mean by "FIND_IN_SET is the savior to non-1NF database tables"     – inrsaurabh Mar 03 '17 at 06:44