0
 customers table     
| id | name | email           |
| 1  | richa| richa@gmail.com |``
| 2  | sam  | sam@gmail.com   |
| 3  | hella| hella@gmail.com |

orders table
| id | customer_id |      
| 101   |    1     |
| 102   |    2     |
| 103   |    3     |

order_product table
| id | order_id | product_id |    
|    |    101   |   1        | 
|    |    102   |   1        |
|    |    103   |   2        |

 products table     
 | id   | product_name |     
 | 1    | Mobile       |      
 | 2    | AC           |      
 | 3    | Fridge       |

These are tables, I want to display customers who buy only one type of products(can be multiple) but did not buy any other products.

$resultArr = $this->db->table('customers')``
            ->join('orders', 'orders.customer_id = customers.id')
            ->join('order_products', 'order_products.order_id = orders.id')
            ->select('customers.id, customers.name, customers.email');
            ->groupBy('customers.id');
            ->where('order_products.product_id', > 0);
        return $resultArr;

I added this query but i did not get required data. can anyone give any idea or explain me if i did any mistake in it let me know. Thanks in advance.

jose_bacoy
  • 12,227
  • 1
  • 20
  • 38
Goldy-566
  • 11
  • 1
  • Compare `COUNT(product_id)` and `SUM(product_id IN ( {products list} ))` in HAVING - they must be equal. – Akina Nov 16 '22 at 20:04
  • can you please explain it little bit more? i did not get required result – Goldy-566 Nov 17 '22 at 05:59
  • I can show only on SQL code level, not as framework code. PS. Show desired output for shown source data. PPS. *customers who buy only one type of products* I do not see "the type of product" in src data. Maybe you mean "only one product, maybe in multiple orders"? – Akina Nov 17 '22 at 06:02
  • SELECT * FROM customers JOIN orders ON customers.id = orders.customer_id JOIN order_products ON orders.id = order_products.order_id GROUP BY customers.id HAVING SUM(order_products.product_id <>'1') = 0; – Goldy-566 Nov 17 '22 at 06:27
  • Alter `HAVING SUM(order_products.product_id <>'1') = 0` to `HAVING COUNT(DISTINCT product_id) = 1`. Also alter `SELECT *` to `SELECT customers.*`. I hope `id` is PK in `customers`? – Akina Nov 17 '22 at 07:15

0 Answers0