Advertisement
Guest User

Untitled

a guest
Oct 19th, 2017
69
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 1.60 KB | None | 0 0
  1.     SELECT p.Name AS ProductName,
  2.            AVG(f.Rate) AS ProductAverageRate,
  3.            d.Name AS DistributorName,
  4.            c.Name AS DistributorCountry
  5.       FROM Products AS p
  6. INNER JOIN ProductsIngredients AS pri
  7.         ON pri.ProductId = p.Id
  8. INNER JOIN Ingredients AS i
  9.         ON i.Id = pri.IngredientId
  10. INNER JOIN Distributors AS d
  11.         ON d.Id = i.DistributorId
  12. INNER JOIN Feedbacks AS f
  13.         ON f.ProductId = p.Id
  14. INNER JOIN Countries AS c
  15.         ON c.Id = d.CountryId
  16.   GROUP BY p.Name, d.Name, c.Name, p.Id
  17.     HAVING p.Name IN
  18.                     (SELECT a.ProductName
  19.                        FROM
  20.                         (SELECT p.Name AS ProductName,
  21.                                 AVG(f.Rate) AS ProductAverageRate,
  22.                                 d.Name AS DistributorName,
  23.                                 c.Name AS DistributorCountry
  24.                            FROM Products AS p
  25.                      INNER JOIN ProductsIngredients AS pri
  26.                              ON pri.ProductId = p.Id
  27.                      INNER JOIN Ingredients AS i
  28.                              ON i.Id = pri.IngredientId
  29.                      INNER JOIN Distributors AS d
  30.                              ON d.Id = i.DistributorId
  31.                      INNER JOIN Feedbacks AS f
  32.                              ON f.ProductId = p.Id
  33.                      INNER JOIN Countries AS c
  34.                              ON c.Id = d.CountryId
  35.                        GROUP BY p.Name, d.Name, c.Name, p.Id) AS a
  36.                        GROUP BY a.ProductName
  37.                          HAVING COUNT(a.ProductName) = 1)
  38. ORDER BY p.Id
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement