CODEWITHSUNDEEP
mysql
Pedro
Pedro

Reputation: 21

Select 2 or more rows in mysql

I need an sql query where I get supplier price from the column supplierX but I only want results where are 2 or more prices to compare. For 2 suppliers I use this code:

select * 
from ps_product 
join ps_product_lang on ps_product.id_product = ps_product_lang.id_product 
join pricecompare on ps_product.id_product = pricecompare.id_product 
where 
    supplier1 > 0 and supplier2 > 0 
order by 
    ps_product.id_product desc

How can I do this for 6 suppliers?

Sample table:

id   prod_name  ref  supplier1   supplier2   supplier3   supplier4
------------------------------------------------------------------
12   abcd       123    0            0           0         12,00 
13   sdsd       423   10,4          0           0         12,00 
14   aaa        413   10,4         10,00        0         12,00

I want the result to be:

13   sdsd    423    10,4    0      0      12,00 
14   aaa     413    10,4   10,00   0      12,00

I want all the lines that have 2 or more supplier prices.

Regards Pedro

Upvotes: 0

Views: 56

Answers (2)

Aleksandar Miladinovic
Aleksandar Miladinovic

Reputation: 1027

Hi i agree with Andriy the design is broken you should have separate table suppliers where you will have all your supplier and than connect that table with all of this with many to many relationship, but if you want to continue this way you can try to do something like this:

SELECT *
FROM myTable
WHERE ((CASE WHEN supp1 > 0 THEN 1 ELSE 0 END) +
       (CASE WHEN supp2 > 0 THEN 1 ELSE 0 END) +
       (CASE WHEN supp3 > 0 THEN 1 ELSE 0 END) +
       (CASE WHEN supp4 > 0 THEN 1 ELSE 0 END)) >= 2

Here is SQL Fiddle for thatto see how it work: http://sqlfiddle.com/#!9/b96e5/21

GL!

Upvotes: 1

Andriy
Andriy

Reputation: 1037

I would suggest you to move suppliers to separate database, and add column supplier_id into your table.

SO the query will look like this:

select *,count(distinct supplier_id) As count_suppliers from ps_product join ps_product_lang on ps_product.id_product=ps_product_lang.id_product join pricecompare on ps_product.id_product=pricecompare.id_product HAVING count_suppliers>6 order by ps_product.id_product desc

Upvotes: 0

Related Questions