SQL - Returning Each Record Only Once

Here's my tables:

tblBusiness
BusinessID, BusinessName

tblTags
TagID, Tag

tblBusinessTagLink
BusinessID, TagID

Any business can have multiple tags applied to it. Now lets say a user is filtering down so that they find only businesses that are tagged 'Office Supplies' and 'Technology'

What SQL statement should I use? Is there a开发者_如何学JAVA better design for my tables than what I've presented here?

SELECT
  b.BusinessId,
  b.BusinessName 
FROM
  tblBusiness AS b
  INNER JOIN tblBusinessTagLink AS l ON l.BusinessId = b.BusinessId
  INNER JOIN tblTags            AS t ON t.TagId      = l.TagId
WHERE
  t.TagName IN ('Technology', 'Office Supplies')
GROUP BY
  b.BusinessId,
  b.BusinessName 

This selects all businesses that are in either one of the categories. To select only those in both categories, you could append a

HAVING COUNT(*) = 2

The method you are using (three tables to represent a m:n relationship) is the standard way to solve this task, you can keep that.

Personally, I would not use "hungarian notation" for table names (i.e. no "tbl") and I would not use plural table names (i.e. not "Tags"), especially when the other tables are not plural either.

Answering the first comment below:

For larger data sets, the performance of this query relies on indexes. All the primary keys need an index, naturally. In tblBusinessTagLink you should have a composite index covering both fields and one additional index for the field that does not come first in the composite index.

The WHERE keywords LIKE '%technology%' idea is a bad one, mostly because for any LIKE conditions other than start-of-field searches an index cannot be used (i.e. performance will degrade rapidly as your data set grows) and partly because it should be WHERE ','+keywords+',' LIKE '%,technology,%' to begin with or you will get partial matches/false positives.

Also, it might be a bit more efficient to query by TagId. This way you can remove one table from the JOIN entirely:

FROM 
  tblBusiness AS b 
  INNER JOIN tblBusinessTagLink AS l ON l.BusinessId = b.BusinessId 
WHERE 
  l.TagId IN (1, 2)

If you intend to query by TagName however, an index on this field will be absolutely necessary as well.

You can use simple JOIN to get record

SELECT t.Tag, b.BusinessName 
FROM tblBusiness b, tblTags t, tblBusinessTagLink l
WHERE t.TagID = l.TagID 
AND l.BusinessID = b.BusinessID 
AND t.Tag = 'Office Supplies'

You can use the INTERSECT set operation to merge the 2 queries (one for 'Office Supplies' and one for 'Technology').

However if you are using MySQL (which does not support INTERSECT), you can use a UNION ALL with a 'HAVING COUNT(*) = 2' like this.

EDIT:

You can also use the second option without the UNION ALL like so:

select Name from tblBusiness
left join tblBusinessTagLink on tblBusinessTagLink.BusinessID = tblBusiness.ID
left join tblTags on tblTags.TagID = tblBusinessTagLink.TagID
where Tag = 'Office Supplies' or Tag = 'Technology'
group by name
having count(Name) = 2;