Designing And Querying Product / Review System

I created a product / review system from scratch and I´m having a hard time to do the following query in SQL Server. My schema has different tables, for: products, reviews, catego

Solution 1:

For count the number of product you can use case when and sum assigning 1 there the value is not r.bloqueado=0 or r.hidden=0 and 0 for these values (so you can avoid the filter in where)

"select top 20 p.id, p.brand, m.nome, c.name, sum(
                              case  when  r.bloqueado=0 then 0 
                                    when  r.hidden=0 then 0 
                                    else 1 
                              end )  AS NoReviews, 
  Avg(r.nota) AS AvgRating, f.id as cod_foto,f.nome as nome_foto 
  from tblBrands AS m 
  inner join (tblProducts AS p 
  left join tblProductsReviews AS r ON p.id=r.product ) ON p.brand = m.id 
  left join tblProductsCategorias as c on p.categoria=c.id 
  left join (select id_product,id,nome from tblProductsFotos O 
  where id = (SELECT min(I.id) FROM tblProductsFotos I 
            WHERE I.id_product = O.id_product)) as f on p.id = f.id_product where p.bloqueado=0
  group by p.id, p.brand, p.modalidade, m.nome, c.name, f.id,f.nome"

for avg could be you can do somethings similar

Solution 2:

It's very easy to lose records when combining a where clause with an outer join. Rows that do not exist in the outer table are returned as NULL. Your filter has accidentally excluded these nulls.

Here's an example that demonstrates what's happening:

/* Sample data.
 * There are two tables: product and review.
 * There are two products: 1 & 2.
 * Only product 1 has a review.
 */DECLARE@ProductTABLE
    (
        ProductId   INT
    )
;

DECLARE@ReviewTABLE
    (
        ReviewId    INT,
        ProductId   INT,
        Blocked     BIT
    )
;

INSERTINTO@Product
    (
        ProductId
    )
VALUES
    (1),
    (2)
;

INSERTINTO@Review
    (
        ReviewId,
        ProductId,
        Blocked
    )
VALUES
    (1, 1, 0)
;

Outer joining the tables, without a where clause, returns:

Query

-- No where.SELECT
    p.ProductId,
    r.ReviewId,
    r.Blocked
FROM@ProductAS p   
        LEFTOUTERJOIN@ReviewAS r        ON r.ProductId = p.ProductId
;

Result

ProductId   ReviewId    Blocked
1102NULLNULL

Filtering for Blocked = 0 would remove the second record, and therefore ProductId 2. Instead:

-- With where.SELECT
    p.ProductId,
    r.ReviewId,
    r.Blocked
FROM@ProductAS p   
        LEFTOUTERJOIN@ReviewAS r        ON r.ProductId = p.ProductId
WHERE
    r.Blocked =0OR r.Blocked ISNULL
;

This query retains the NULL value, and ProductId 2. Your example is a little more complicated because you have two fields.

SELECT
    ...
WHERE
    (
        Blocked =0AND Hidden =0
    )
    OR Blocked ISNULL
;

You do not need to check both fields for NULL, as they appear in the same table.