0

I have 3 (hypothetical) tables.

[1]

  1. Photos (a list of photos)
  2. Attributes (things describing the photos)
  3. PhotosToAttributes (a table to link the first 2)

I want to retrieve the Names of all the Photos that have a list of attributes. For example, all photos that have both dark lighting and are portraits (AttributeID 1 and 2). Or, for example, all photos that have dark lighting, are portraits and were taken at a wedding (AttributeID 1 and 2 and 5). Or any arbitrary number of attributes.

The scale of the database will be maybe 10,000 rows in Photos, 100 Rows in Attributes and 100,000 rows in PhotosToAttributes.

This question: SQL: Many-To-Many table AND query is very close. (I think.) I also read the linked answers about performance. That leads to something like the following. But, how do I get Name instead of PhotoID? And presumably my code (C#) will build this query and adjust the attribute list and count as necessary?

SELECT PhotoID
FROM PhotosToAttributes
WHERE AttributeID IN (1, 2, 5)
GROUP by PhotoID
HAVING COUNT(1) = 3

I'm a bit database illiterate (it's been 20 years since I took a database class); I'm not even sure this is a good way to structure the tables. I wanted to be able to add new attributes and photos at will without changing the data access code.

Community
  • 1
  • 1
DouglasJ
  • 23
  • 3

2 Answers2

0

It is probably a reasonable way to structure the database. An alternate would be to keep all the attributes as a delimited list in a varchar field, but that would lead to performance issues as you search the field.

Your code is close, to take it to the final step you should just join the other two tables like this:

Select p.Name, p.PhotoID
  From Photos As p
  Join PhotosToAttributes As pta On p.PhotoID = pta.PhotoID
  Join Attributes As a On pta.AttributeID = a.AttributeID
  Where a.Name In ('Dark Light', 'Portrait', 'Wedding')
  Group By p.Name, p.PhotoID
  Having Count(*) = 3;

By joining the Attributes table like that it means you can search for attributes by their name, instead of their ID.

mendosi
  • 2,001
  • 1
  • 12
  • 18
  • The alternative you mention is non-relational and should be discouraged in the strongest possible terms. In short, never, ever do this. – RBarryYoung Nov 19 '16 at 01:48
0

For first create view from your joins:

create view vw_PhotosWithAttributes
as
select 
    p.PhotoId, 
    a.AttributeID, 
    p.Name PhotoName, 
    a.Name AttributeName
from Photos p
inner join PhotosToAttributes pa on p.PhotoId = pa.PhotoId
inner join Attributes a on a.AttributeID = pa.AttributeID

You can easy ask for attribute, name, id but don't forget to properly index field.

Deadsheep39
  • 561
  • 3
  • 16