How can I select the required records?

Tables:

• Product: [id, name, brand_id, is_published]
• Brand: [id, name, is_published]
• Awards: [id, name]
• ProductAwards [product_id, award_id]

How do I select the list of PUBLISHED brands along with the number of AWARDS of brand's products that are Published.

I am cool with all the part except issuing the "is_published" restriction when counting Awards.

I开发者_如何学运维 hope this is clear; can anyone just suggest where to dig?

---

I'm guessing this is the query you're looking for:

SELECT Brand.name, COUNT(Awards.id) AS Awards
FROM Brand
LEFT JOIN Product ON product.brand_id = Brand.id AND Product.is_published = TRUE /* This may need to be changed based on the data type of is_published */
LEFT JOIN ProductAwards ON ProductAwards.product_id = Product.id
LEFT JOIN Awards ON Awards.id = ProductAwards.award_id
WHERE Brand.is_published = TRUE /* As with Product.is_published, this clause is dependent on the column's data type */

For a more informed answer, you may want to post the results of SHOW CREATE TABLE for each of your tables, as well as an example of the sort of result you are looking for. Hope this helps!

---

Assuming that referential integrity is enforced so there are no fallacious entries in the ProductAwards table, you do not actually need to select from the Awards table:

SELECT B.Name, COUNT(PA.Award_ID) AS Num_Awards
  FROM Brand              AS B
  LEFT JOIN Product       AS P  ON P.Brand_ID = B.ID AND P.Is_Published = TRUE
  LEFT JOIN ProductAwards AS PA ON PA.Product_ID = P.ID
 WHERE B.Is_Published = TRUE

If some brand has no awards, then COUNT(PA.Award_ID) will return zero because COUNT(column) counts the number of non-null values.

---

SELECT b.name, count(pa.award_id) as Quantity
FROM PRODUCT p 
      LEFT JOIN Brand b ON p.brand_id = b.id
      LEFT JOIN ProductAwards pa on p.Product_id = pa.product_id
      LEFT JOIN Awards a ON a.award_id = pa.award_id
WHERE p.is_published = 1
GROUP BY b.name

Something like this?

---

If you have a query which (if I read the question correctly) works 100% except for published restriction, making that restriction is trivial - just add the AND is_published = 1 to the WHERE clause (or add WHERE is_published = 1 if your query has no WHERE clause).

The above assumes that is_published is an integer containing 0 or 1 for non-published or published. If the column is different, you need to change the expression accordingly.

---

You can try this query to get the required count for each brand ID

SELECT b1.id, COUNT(pa.award_id) [No of awards]
FROM Brand1 b1 left join Product p
ON p.brand_id=b1.id AND p.is_published=1 AND b1.is_published=1
LEFT JOIN ProductAwards pa
ON pa.product_id = p.id
LEFT JOIN Awards a
ON a.id=pa.award_id
GROUP BY b1.id