* Seamus R Abshere
> i am developing a photo gallery with php4/mysql4.0 that uses
> faceted classification.
>
> -my tables:
> photos(photoid)
> metadata(photoid,facetid)
>
> -to select all of the photoid's that are associated with either
> facetid 1 or 2:
> SELECT DISTINCT photos.*
> FROM photos,metadata
> WHERE photos.photoid = metadata.photoid AND (metadata.facetid = 1
> OR metadata.facetid = 2)
>
> but what if i want to select all photoids that are associated
> with BOTH facetids? is there a join? (just sticking "AND" in
> there won't work, because any row in metadata can only have one facetid.)
You can join the metadata table twice:
SELECT photos.*
FROM photos,metadata m1,metadata m2
WHERE
photos.photoid = m1.photoid AND m1.facetid = 1 AND
photos.photoid = m2.photoid AND m2.facetid = 2
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
