SQL Query for retrieving search results

Question

I need help designing a query that will be used to return a website's search results. Users search by selecting items from a list of attributes. Each search result item returned must have ALL the attributes selected by the user.

The challenge (for me at least!) is figuring out how to only return results that have ALL the attributes versus just ANY of them.

The search result items (let's call them WIDGETs) are in the WIDGET table. Possible widget attributes are in the ATTRIBUTE table. A junction table (WIDGETATTRIBUTEJUNCTION) stores the 0,1..n actual attributes for each WIDGET.

I can't figure out a query that, when supplied a list of widget attributes, will return rows that have every one of these attributes. I suspect I might use an ALL subquery and/or an INTERSECT but not sure how.

Answer 1

You could use something similar to the following,

SELECT WidgetID FROM Widget INNER JOIN WidgetAttributes WA ON WA.Key = SearchAttributes.Key AND WA.WidgetID = Widget.WidgetID GROUP BY WidgetID HAVING COUNT(Widget.WidgetID) > @SearchAttributesCount

The key being the GROUP BY HAVING statement which limits it to only include all Widget rows which match all attributes.

Answer 2

If only SQL supported arrays...

There are a couple of ways you can go about this, my preferred method is to send a string (containing the attribute IDs) to SQL then split the string into a table.

Something like:

create function dbo.fn_makeArray ( @value nvarchar(max) )
    returns @table table([key] nvarchar(256))
    begin
        declare @start int;
        declare @end int;

        select @start = 1, @end = charindex(',', @value);

        while (@start < len(@value) + 1)
        begin
            if (@end = 0)
                set @end = len(@value) + 1;

            insert into @table ([key]) 
            values(substring(@value, @start, @end - @start));

            set @start = @end + 1;
            set @end = charindex(',', @value, @start);
        end
        return;
    end

Answer 3

We had a problem like this a while back:

select WidgetName,AttributeName 
 from Widgets
 left join WALinks    on WALinks.wid = WidgetID
 left join Attributes on WALinks.aid = AttributeID

where WidgetID in 
 (
  select wId 
  from waLinks 
  where aid in (1,3) 
  group by wId 
  having count(aId) = 2
 )

You can then set the attributes to the list "in (1,3)" and always adjust the number for the count query to the number of attributes you are matching.