CODEWITHSUNDEEP
sqlt-sql
Romeo
Romeo

Reputation: 1093

Conditionally joining tables

I want to create conditional join to a table in my T-SQL query. Tables used in this example are from Northwind database (with only one additional table ProductCategories)

Table Products and table Categories have many-to-many relationship, hence table ProductCategories comes into picture.

I need the sum of Quantity column on table OrderDetails for each of the products falling under certain category. So I have a query like one below

Select p.ProductName, Sum(od.Quantity) As Qty
From Products p
    Join OrderDetails od On od.ProductID = p.ProductID
    Join ProductCategories pc On pc.ProductID = p.ProductID
        And pc.CategoryID = @CategoryID
Group By p.ProductName

@CategoryID is an optional parameter. So in case it's not supplied, the join to table ProductCategories will not be required and query should look like one below

Select p.ProductName, Sum(od.Quantity) As Qty
From Products p
    Join OrderDetails od On od.ProductID = p.ProductID
Group By p.ProductName

I want to achieve this without repeating the whole query with If conditions (as below)

If @CategoryID Is Null
    Select p.ProductName, Sum(od.Quantity) As Qty
    From Products p
        Join OrderDetails od On od.ProductID = p.ProductID
    Group By p.ProductName
Else
    Select p.ProductName, Sum(od.Quantity) As Qty
    From Products p
        Join OrderDetails od On od.ProductID = p.ProductID
        Join ProductCategories pc On pc.ProductID = p.ProductID
            And pc.CategoryID = @CategoryID
    Group By p.ProductName

This is simplified version of the query which has many other tables and conditions like ProductCategories. And will require multiple multiple If conditions and repetition of the query. I have also tried dynamically generating the query. It works but query is not readable at all.

Any solutions? Thank you.

Upvotes: 2

Views: 179

Answers (3)

Jordan
Jordan

Reputation: 2758

I believe you can left join the tables vs the implicit inner join you are doing.

In an inner join it matches the key on the source table with each instance of the key on the destination table.

Each instance of a match on the destination table generates a set of rows displaying the match types.

With an outer join it will display the source row EVEN IF there is no matching row in the other table. If there is it will do essentially the same as an inner join and you'll get a row back for each instance of match. So you're getting the data you need when it's available and not getting what data is unavailable.

Take this as an example

select * from Products

    Left join ProductAndCategory on ProductAndCategory.ProductID = Products.ProductID
    left join Categories on Categories.CategoryID = ProductAndCategory.CategoryID

Where I have a simple Product table with a ProductID and a ProductName a ProductAndCategory table with a ProductID and a CategoryID and a Categories table with a CategoryID and a CategoryName

It will show the rows that have categories with the joined categories and the rows that don't have categories will just show the one row with null for the values that don't exist.

Upvotes: 1

Oleg Dok
Oleg Dok

Reputation: 21756

Try this, if you'll use properly parametrized query - there will be no performance impact but may be gain:

Select p.ProductName, Sum(od.Quantity) As Qty
From Products p
    Join OrderDetails od On od.ProductID = p.ProductID
WHERE @CategoryID IS NULL OR EXISTS (SELECT * FROM ProductCategories WHERE CategoryID = @CategoryID AND ProductID = p.ProductID)
Group By p.ProductName

Actually

in your query if there can be multiple rows in ProductCategories for one row in OrderDetails - then you get duplicates of od.Quantity in your SUM - is it an intended behavior?

Upvotes: 2

atxdba
atxdba

Reputation: 5216

I'm not very familiar with T-SQL so I don't know if this will cut it but in mysql you could do something like

Select p.ProductName, Sum(od.Quantity) As Qty
From Products p
    Join OrderDetails od On od.ProductID = p.ProductID
    Join ProductCategories pc On pc.ProductID = p.ProductID
        And pc.CategoryID = if(@CategoryID is null , pc.CategoryID, @CategoryId)
Group By p.ProductName

Yes and if() still remains but just "one query", if that's what you're looking for.

At the same time, you say you were able to dynamically generate a single query. If so is the readability that much of an issue? If your generated query was more performant over what I suggested above I'd go with that. It's generated; you won' be manually tweaking/reading the result.

Upvotes: 0

Related Questions