Do not use your filter in the where clause. Even though you have a right join to bring in all of the clubid columns. the where clause will filter it...

Here is my query modified. I created an additional cte that uses the initial result set. It will filter through and only show the records that uses the original id.

declare...

Below is a little script I created which seems to work the way you want. Check it out and see if it works for you.

declare @item varchar(50) = 'headrest'

;with cte...