WHERE clause in an SQL query

Question

I THINK what is happening with this query is if there are no records in the GenericAttribute table associated with the Product, then that product is not displayed. See line below in WHERE clause: "AND GenericAttribute.KeyGroup = 'Product'"

Is there a way to reword so that that part of the WHERE is ignored if no associated record in the GenericAttribute table?

Also, looking at my ORDER BY clause, will a record from the product table still show up if it has no associated record in the Pvl_AdDates table?

Thanks!

SELECT DISTINCT Product_Category_Mapping.CategoryId, Product.Id, Product.Name, Product.ShortDescription, Pvl_AdDates.Caption, Pvl_AdDates.EventDateTime, convert(varchar(25), Pvl_AdDates.EventDateTime, 120) AS TheDate, Pvl_AdDates.DisplayOrder, Pvl_Urls.URL, [Address].FirstName, [Address].LastName, [Address].Email, [Address].Company, [Address].City, [Address].Address1, [Address].Address2, [Address].ZipPostalCode, [Address].PhoneNumber 
FROM [Address] 
RIGHT JOIN (GenericAttribute  
RIGHT JOIN (Pvl_Urls RIGHT JOIN (Pvl_AdDates 
RIGHT JOIN (Product_Category_Mapping 
LEFT JOIN Product 
ON Product_Category_Mapping.ProductId = Product.Id) 
ON Pvl_AdDates.ProductId = Product.Id) 
ON Pvl_Urls.ProductId = Product.Id) 
ON GenericAttribute.EntityId = Product.Id) 
ON Address.Id = convert(int, GenericAttribute.Value) 
WHERE 
Product_Category_Mapping.CategoryId=12 
AND GenericAttribute.KeyGroup = 'Product'
AND Product.Published=1 
AND Product.Deleted=0 
AND Product.AvailableStartDateTimeUtc <= getdate() 
AND Product.AvailableEndDateTimeUtc >= getdate() 
ORDER BY 
Pvl_AdDates.EventDateTime DESC, 
Product.Id, 
Pvl_AdDates.DisplayOrder

If possible add sample data and expected result to get answers in minutes — Pரதீப்
– Pரதீப், Commented Dec 10, 2016 at 13:52

Gordon Linoff · Accepted Answer · 2016-12-10 13:55:35Z

I strongly encourage you to not mix left join and right join. I have written many SQL queries and cannot think of an occasion when that was necessary.

In fact, just stick to left join.

If you want all products (or at least all products not filtered out by the where clause), then start with the products table and go from there:

FROM Products p LEFT JOIN
     Product_Category_Mapping pcm
     ON pcm.ProductId = p.Id LEFT JOIN
     Pvl_AdDates ad
     ON ad.ProductId = p.id LEFT JOIN
     Pvl_Urls u
     ON u.ProductId = p.id LEFT JOIN
     GenericAttribute ga
     ON ga.EntityId = p.id LEFT JOIN
     Address a
     ON a.Id = convert(int, ga.Value)

Note that I added table aliases. These make queries easier to write and to read.

I would add a caution. It looks like you are combining data along different dimensions. You are likely to get a Cartesian product of the dimension attributes for each dimension. Perhaps that is what you want or the WHERE clause takes care of the additional rows.

Charles Bretana · Accepted Answer · 2016-12-10 14:14:26Z

Yes put constraints (restrictions) on tables on the outer side of outer joins in the on conditions of the outer join, not in the where clause. Conditions in where clauses are not evaluated and applied until after the outer joins are evaluated, so where there is not record in the outer table, the predicate will be false and entire row will be eliminated, undoing the outer-ness. Conditions in the join are evaluated during the join, before the rows from the inner side are added back in, so the result set will still include them.

Second, formatting formatting, formatting! Stick to one direction of join (left is easier) and use Aliases for tables names!

SELECT DISTINCT m.CategoryId, p.Id, 
    p.Name, p.ShortDescription, d.Caption, d.EventDateTime, 
    convert(varchar(25), d.EventDateTime, 120) TheDate, 
    d.DisplayOrder, u.URL, a.FirstName, a.LastName, 
    a.Email, a.Company, a.City, a.Address1, a.Address2, 
    a.ZipPostalCode, a.PhoneNumber 
FROM Product_Category_Mapping m
    left join Product p on p.Id = m.ProductId 
        and p.Published=1 
        and p.Deleted=0 
        and p.AvailableStartDateTimeUtc <= getdate() 
        and p.AvailableEndDateTimeUtc >= getdate() 
    left join Pvl_AdDates d ON d.ProductId = p.Id 
    left join Pvl_Urls u ON u.ProductId = p.Id
    left join GenericAttribute g ON g.EntityId = p.Id
        and g.KeyGroup = 'Product'
    left join [Address] a ON a.Id = convert(int, g.Value) 
WHERE m.CategoryId=12                  
ORDER BY d.EventDateTime DESC, p.Id, d.DisplayOrder

Collectives™ on Stack Overflow

WHERE clause in an SQL query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related