0

I'm having an issue with mysql when doing a JOIN of two tables and filtering using HAVING for these two tables.

Assume my tables look like this:

Products:

id | Name
1  | Product 1
2  | Product 2
3  | Product 3

Reviews:

Name     | Product_id | score
Review 1 | 1          | 10000

My best guess was the following, but it doesn't work: 

SELECT "products".*, sum("reviews".score) FROM "products" INNER JOIN "reviews" ON "reviews"."empire_id" = "products"."id" GROUP BY products.id HAVING sum("reviews".score)=0;

And I would like to get the products that have a score of 0, meaning that they have not been reviewed. I feel like this should be easy, but I can't seem to figure out.

Asaph
  • 159,146
  • 25
  • 197
  • 199
Marco A
  • 109
  • 1
  • 2
  • 10

2 Answers2

1

It sounds like you want products that show up in the "products" table but not the "reviews" table. Perhaps take a look at SQL - find records from one table which don't exist in another?

Alternatively, replacing your INNER JOIN with a LEFT JOIN might work.

Community
  • 1
  • 1
jvns
  • 281
  • 2
  • 8
  • 1
    You helped me figure it out! Thanks SELECT products.id, products.name, sum("reviews".score) FROM products LEFT JOIN reviews ON reviews.product_id= products.id GROUP BY products.id, products.name HAVING SUM("reviews".SCORE) IS NULL; – Marco A Feb 28 '12 at 00:50
0
SELECT products.id, products.name, sum("reviews".score)
FROM products
LEFT JOIN reviews ON reviews.product_id = products.id
GROUP BY products.id, products.name
HAVING sum(reviews.score)=0;

"reviews"."empire_id" = "products"."id"

Should be "reviews"."product_id" = "products"."id"

Actually your group by should have all your field name from product

Churk
  • 4,556
  • 5
  • 22
  • 37
  • You SQL should get what u r asking for, just the join have some error – Churk Feb 28 '12 at 00:15
  • mmm, it didn't really work. It only works for the products that have reviews "attached" to them. But if I want to get the products that dont have anything attached to them (ie, there's no review that has that product's id in the product_id field, then it doesnt work...) – Marco A Feb 28 '12 at 00:26