Problem ANDing many to one relationship

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Problem ANDing many to one relationship

Author

Topic

jalessio
Starting Member

1 Post

Posted - 2002-08-06 : 15:38:38

DB is MS SQL Server 2000

I have one table that represents files on a shared filesystem and another table that represents labels that are applied to the file. Here's a text representation of the simplified table layout:

|----------|
| files |
|----------|
| file_id |
| filename |
|----------|

|----------|
| labels |
|----------|
| label_id |
| file_id |
| label |
|----------|

Any number of labels can be applied to a file and I need to be able to find out which files have certain labels. The problem I am having is finding files that have more than one label. For instance, "show me all the files that have 'labelone' and 'labeltwo'". I first tried representing the query like this:

SELECT files.* FROM files
INNER JOIN files ON files.file_id = labels.file_id
WHERE labels.label = 'labelone' AND labels.label = 'labeltwo'

I soon realized that this never returns any rows since a single record expressed by the join can never actually have two labels atttached to it. The only way I have been able to get the desired results is via a self join like this:

SELECT files.*
FROM file INNER JOIN
labels L1 ON files.file_id = L1.file_id INNER JOIN
labels L2 ON L1.file_id = L2.file_id
WHERE (L1.label = 'labelone') AND (L2.label = 'labeltwo')

This appears to work fine, but I'm wondering if there is a simpler way to express this query since this can quickly get out of hand when dealing many labels. For example, if I want to check for the existance of five specific labels I have to self join the labels table as many times. Any ideas/suggestions on how to simplify this type of query? I am open to the idea of redesigning the data structure if necessary. Thanks.

- Jamie

izaltsman
A custom title

1139 Posts

Posted - 2002-08-06 : 16:04:35

Try using OR instead of AND (think about it -- no label can equal 'labelone' and 'labletwo' at the same time, but you want a row returned as long as you find either 'labelone' or 'labletwo'...)

SELECT files.* FROM files
INNER JOIN files ON files.file_id = labels.file_id
WHERE labels.label = 'labelone' OR labels.label = 'labeltwo'

To make things even easier, you can use IN operator as a shorthand for multiple ORs:

SELECT files.* FROM files
INNER JOIN files ON files.file_id = labels.file_id
WHERE labels.label IN ('labelone', 'labeltwo', 'labelthree')

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-08-06 : 16:05:05

SELECT files.* FROM files
INNER JOIN files ON files.file_id = labels.file_id
WHERE labels.label = 'labelone' ~~AND~~OR labels.label = 'labeltwo'

Edit: Ilya, you got me...GSW...somebody call a medic.

Jay White
{0}

Edited by - Page47 on 08/06/2002 16:08:01

izaltsman
A custom title

1139 Posts

Posted - 2002-08-06 : 16:09:48

Ooops... Misread the question... If you want to list all files that have all three labels, something like this should work:

SELECT f.*
FROM
files f inner join
(
SELECT files.file_id FROM files
INNER JOIN labels ON files.file_id = labels.file_id
WHERE labels.label IN ('labelone', 'labeltwo', 'lablethree')
group by files.file_id
having count(*) = 3
) as qual
on f.file_id = qual.file_id

Edited by - izaltsman on 08/06/2002 16:14:57

joshb
Yak Posting Veteran

52 Posts

Posted - 2002-08-06 : 16:24:36

Something like this would work:

SELECT f.file_id
FROM files f
WHERE
(SELECT COUNT(file_id)
FROM labels
WHERE file_id=f.file_id AND label_id IN (1,3)
GROUP BY file_id) > 1

This assumes that all combinations of label_id, file_id in the labels table are unique, which I would think they would be.

Josh

r937
Posting Yak Master

112 Posts

Posted - 2002-08-06 : 17:12:02

josh, i don't think >1 works except for two labels

izaltsman, yours seems to have an extra inner join in it

SELECT filename 
  FROM files 
INNER 
  JOIN labels
    ON files.file_id = labels.file_id 
   AND labels.label IN 
       ( 'labelone', 'labeltwo', 'labelthree'
       , 'labelfour', 'labelfive' ) 
GROUP
    BY filename
HAVING count(*) = 5

perhaps wishful thinking on my part, but i expect that putting the label check into the ON condition is potentially faster than in the WHERE clause

rudy
http://rudy.ca/

izaltsman
A custom title

1139 Posts

Posted - 2002-08-07 : 17:54:53

quote:

izaltsman, yours seems to have an extra inner join in it

The join was there to allow jalessio to pull more than just a filename from the files table. But if the filename is all he needs -- your solution is the way to go.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources