CODEWITHSUNDEEP
mysqlphpmyadmin
Tedd Persson
Tedd Persson

Reputation: 37

Correct select statement

I have this table called ps_product_supplier:

enter image description here

I want to select all products that has the product_supplier_reference occuring more than once and if it occurs on both the main product (id_product_attribute = 0) and on a combination.

I tried with this select but I get no result from it:

SELECT `id_product_supplier`, `id_product`, `id_product_attribute`,
    `product_supplier_reference`, COUNT(`product_supplier_reference`)
FROM `ps_product_supplier`
GROUP BY `id_product`
HAVING COUNT(`product_supplier_reference`) > 1
    AND `id_product_attribute` = 0
    AND `product_supplier_reference` > 0

But as you can see on the image I should get one there since product_supplier_reference is same on id_product_supplier 1426 and 4210.

Anyone that can help me?

Upvotes: 0

Views: 66

Answers (2)

Paul Maxwell
Paul Maxwell

Reputation: 35583

You need to reduce the number of columns listed in the select clause (this will reduce rows) OR add more column names into the group by clause (this increases number of rows) OR use an aggregate function on more columns.

Note that the HAVING clause is designed for evaluating aggregated values and that you can still use the WHERE clause for on-aggregated filtering.

## least rows
SELECT `id_product`
       Count(`product_supplier_reference`) 
FROM   `ps_product_supplier` 
WHERE  `id_product_attribute` = 0 
       AND `product_supplier_reference` > 0 
GROUP  BY `id_product`
HAVING Count(`product_supplier_reference`) > 1

OR

## most rows
SELECT 
       `id_product`, 
       `id_product_attribute`, 
       `product_supplier_reference`, 
       Count(`product_supplier_reference`) 
FROM   `ps_product_supplier` 
WHERE  `id_product_attribute` = 0 
       AND `product_supplier_reference` > 0 
GROUP  BY `id_product`, 
          `id_product_supplier`, 
          `id_product_attribute`, 
          `product_supplier_reference` 
HAVING Count(`product_supplier_reference`) > 1

Upvotes: 1

nacho
nacho

Reputation: 5397

You need to add all the columns to the group by clause so it works:

SELECT `id_product_supplier` , `id_product` , 
       `product_supplier_reference` , COUNT( `product_supplier_reference` )
FROM `ps_product_supplier`
WHERE `product_supplier_reference` >0
GROUP BY `id_product`,`id_product_supplier`,`product_supplier_reference`  
HAVING COUNT( `product_supplier_reference` ) >1

Upvotes: 1

Related Questions