CODEWITHSUNDEEP
sqlsql-server
mft25
mft25

Reputation: 427

SQL Server - only join if condition is met

I have three tables (at least, something similar) with the following relationships:

Item table:

 ID      | Val
---------+---------
 1       | 12
 2       | 5
 3       | 22

Group table:

 ID      | Parent  | Range
---------+---------+---------
 1       | NULL    | [10-30]
 2       | 1       | [20-25]
 3       | NULL    | [0-15]

GroupToItem table:

 GroupID | ItemID
---------+---------
 1       | 1
 1       | 3

And now I want to add rows to the GroupToItem table for Groups 2 and 3, using the same query (since some other conditions not shown here are more complicated). I want to restrict the items through which I search if the new group has a parent, but to look through all items if there is not.

At the moment I am using an IF/ELSE on two statements that are almost exactly the same, but for the addition of another JOIN row when a parent exists. Is it possible to do a join to reduce the number of items to look at, only if a restriction is possible?

My two queries as they stand are given below:

DECLARE @GroupID INT = 2;...

INSERT INTO GroupToItem(GroupID, ItemID)
SELECT  g.ID,
        i.ID,
FROM    Group g
JOIN    Item i ON i.Val IN g.Range
JOIN    GroupToItem gti ON g.Parent = gti.GroupID AND i.ID = gti.ItemID
WHERE   g.ID = @GroupID

-

DECLARE @GroupID INT = 3;...

INSERT INTO GroupToItem(GroupID, ItemID)
SELECT  g.ID,
        i.ID,
FROM    Group g
JOIN    Item i ON i.Val IN g.Range
WHERE   g.ID = @GroupID

So essentially I only want to do the second JOIN if the given group has a parent. Is this possible in a single query? It is important that the number of items that are compared against the range is as small as possible, since for me this is an intensive operation.

EDIT: This seems to have solved it in this test setup, similar to what was suggested by Denis Valeev. I'll accept if I can get it to work with my live data. I've been having some weird issues - potentially more questions coming up. 

SELECT  g.Id,
        i.Id
FROM    Group g
JOIN    Item i ON (i.Val > g.Start AND i.Val < g.End)
WHERE   g.Id = 2
AND     (
            (g.ParentId IS NULL)
            OR 
            (EXISTS(SELECT 1 FROM GroupToItem gti WHERE g.ParentId = gti.GroupId AND i.Id = gti.ItemId))
        )

SQL Fiddle

Upvotes: 0

Views: 4664

Answers (2)

Mike
Mike

Reputation: 132

If a Range column is a varchar datatype, you can try something like this:

INSERT INTO GROUPTOITEM (GROUPID, ITEMID)
SELECT A.ID, B.ID
FROM GROUP AS A
LEFT JOIN ITEM AS B
ON B.VAL BETWEEN CAST(SUBSTRING(SUBSTRING(A.RANGE,1,CHARINDEX('-',A.RANGE,1)-1),2,10) AS INT)
    AND CAST(REPLACE(SUBSTRING(A.RANGE,CHARINDEX('-',A.RANGE,1)+1,10),']','') AS INT)

Upvotes: 0

Denis Valeev
Denis Valeev

Reputation: 6015

Try this:

INSERT INTO GroupToItem(GroupID, ItemID)
SELECT  g.ID,
        i.ID,
FROM    Group g
JOIN    Item i ON i.Val IN g.Range
WHERE   g.ID = @GroupID
and (g.ID in (3) or exists (select top 1 1 from GroupToItem gti where g.Parent = gti.GroupID AND i.ID = gti.ItemID))

Upvotes: 1

Related Questions