How to use JOIN for both 'AND' and 'OR' conditions in SQL qu

Question

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,