CODEWITHSUNDEEP
sqlsql-server
ScotterMonkey
ScotterMonkey

Reputation: 1054

WHERE clause in an SQL query

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

Upvotes: 0

Views: 52

Answers (2)

Charles Bretana
Charles Bretana

Reputation: 146567

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270873

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.

Upvotes: 2

Related Questions