Recently met with a problem, where in I need to lookup a table using LINQ query where the JOIN clause is not supporting non-equi joins. The Cross Join came as handy and helped. Here is the sample SQL query which unit tested the same.
DROP TABLE BucketList
GO
CREATE TABLE BucketList
(
StartNum INT,
EndNum INT,
Bucket VARCHAR(20)
)
INSERT INTO BucketList values
( 1,10,'[01-10]')
,( 11,20,'[11-20]')
,( 21,30,'[21-30]')
,( 31,40,'[31-40]')
drop TABLE #T1
go
CREATE TABLE #T1
(
IP INT,
Label VARCHAR(40)
)
INSERT INTO #T1 VALUES
(4,'Four')
,(9,'Nine')
,(34,'Thirty four')
,(44,'FortyFour')
GO
SELECT StartNum, EndNum, Label, Bucket
FROM #T1
CROSS JOIN BucketList
WHERE IP >= StartNum AND IP <= EndNum
