0

I join two tables with left join. When I select all with Select, all rows come (query1). But when I pull some rows with select, I load only one data, not all data in Figure 1 is loaded. What is the reason?

query1 query2

Loading all data query1

query1:

SELECT * 
FROM oc_review AS OYLAMA 
JOIN oc_product AS PRODUCT 
    ON (PRODUCT.product_id = OYLAMA.product_id)
INNER JOIN oc_product_to_category AS KATEGORI 
    ON (KATEGORI.product_id = PRODUCT.product_id) 
WHERE KATEGORI.category_id = 69

Loading only 1 data query2

query2:

SELECT
    AVG(ALL OYLAMA.rating) AS ORTALAMA,
    COUNT(OYLAMA.rating) AS TOPLAMOY 
FROM oc_review AS OYLAMA 
JOIN oc_product AS PRODUCT
    ON (PRODUCT.product_id = OYLAMA.product_id) 
INNER JOIN oc_product_to_category AS KATEGORI
    ON (KATEGORI.product_id = PRODUCT.product_id) 
WHERE KATEGORI.category_id = 69
Slava Rozhnev
  • 9,510
  • 6
  • 23
  • 39
SH Yazılım Geliştirme
  • 844
  • 3
  • 11
  • 19

1 Answers1

2

MySQL creates a single group where all records are put as soon as you introduce aggregate functions such as AVG() and COUNT(). See also the question MySQL Aggregate Functions without GROUP BY clause for more info.

What you might want to do is explicitly specify a GROUP BY clause and add the field(s) you have after GROUP BY also to SELECT for example:

SELECT 
   PRODUCT.product_id, 
   AVG(ALL OYLAMA.rating) AS ORTALAMA,
   COUNT(OYLAMA.rating) AS TOPLAMOY 
FROM oc_review AS OYLAMA 
JOIN oc_product AS PRODUCT 
   ON(PRODUCT.product_id = OYLAMA.product_id) 
INNER JOIN oc_product_to_category AS KATEGORI 
   ON (KATEGORI.product_id = PRODUCT.product_id) 
WHERE KATEGORI.category_id = 69
GROUP BY PRODUCT.product_id
Christian
  • 1,250
  • 9
  • 12
  • Thank you but, Print only 2 values – SH Yazılım Geliştirme Sep 01 '20 at 17:12
  • So it seems the category 69 only has two products which account for the two rows. What would you like to have counts and averages for? With my example you get the count and average **per product**. It seems you want something different. Maybe per product and customer? Or just per customer? – Christian Sep 01 '20 at 17:13
  • Did you notice the `GROUP BY` at the end of my example code? – Christian Sep 01 '20 at 17:21
  • Although there are more than 2 products, only 2 of them are displayed, unfortunately the sql query does not work. – SH Yazılım Geliştirme Sep 01 '20 at 17:24
  • Maybe try removing the `ALL` in `AVG(ALL OYLAMA.rating)`. I don't know what that is for. Also try running the query outside of PHP directly against the database to ensure it isn't PHP that's messing with the query result. – Christian Sep 01 '20 at 17:32