Hi,
I am generating SQL script from my C# code and pass it to the stored procedure to return result.
I have a few Items which belong to different Categories.
The users have a Query Builder where they can choose category1, status; then category2,staus etc. There is also a condition box for each row which displays 'AND' and 'OR'
For each 'AND', I am planning to use JOIN to join to the next row; but instead of 'AND' if the user chooses 'OR', how will I modify the query.
I have to use the same query for both.
Please see what I have done.
DECLARE @Person as table(
[id] [int],
[personname] [varchar](50))
INSERT INTO @Person Values
(1,'abc'),
(2,'def'),
(3,'ghi'),
(4,'jkl'),
(5,'mno')
DECLARE @Person_Items as table(
[personId] [int],
[ItemId] [int],
[statusId] [smallint])
INSERT INTO @Person_Items Values
(1,100,50),
(1,101,50),
(1,200,50),
(2,200,50),
(3,102,51),
(5,201,51),
(1,300,50)
DECLARE @Item_Category as table(
[ItemId] [int] NOT NULL,
[CategoryId] [int] NOT NULL)
INSERT INTO @Item_Category Values
(100,900),
(101,900),
(102,900),
(200,901),
(201,901),
(300,902),
(301,902)
SELECT
p.id,
p.personname
FROM @Person p
JOIN @Person_Items pit1 ON p.id = pit1.personId
AND pit1.statusId=50
JOIN @Item_Category itcat1 ON itcat1.ItemId = pit1.ItemId
AND itcat1.CategoryId=900
JOIN @Person_Items pit2 ON p.id = pit2.personId
AND pit2.statusId=50
JOIN @Item_Category itcat2 ON itcat2.ItemId = pit2.ItemId
AND itcat2.CategoryId=901
JOIN @Person_Items pit3 ON p.id = pit3.personId
AND pit3.statusId=50
JOIN @Item_Category itcat3 ON itcat3.ItemId = pit3.ItemId
AND itcat3.CategoryId=902
I will be passing the above query to the procedure, the procedure will execute the query.
There can be nearly 10 conditions with a combinations of 'AND's and 'OR's.
Any idea?
Thanks,