Keith Devens .com
Keith Devens .com |
Monday, October 13, 2008 | |
| REMEMBERS- HEEL BARES DURID! BARE DURIDS IS STORNG FREND! – Alamo | ||
|
| Objects |
| Object_Id |
| more fields... |
and
| Properties |
| Object_Id |
| Property_Name |
So, every "object" can have multiple "properties". Now, it's really easy to say "give me all the objects which have ANY of these properties" with something like "SELECT Objects.* FROM Objects INNER JOIN Properties USING(Object_Id) WHERE Property_Name IN([list of properties])"
But how can you easily get the set of objects which have ALL of the properties in the [list of properties]? You could say something like:
"SELECT * FROM Objects WHERE Object_Id IN(
INTERSECT(
(SELECT Object_Id FROM Properties WHERE Property_Name = list[0]),
(SELECT Object_Id FROM Properties WHERE Property_Name = list[1]),
(SELECT Object_Id FROM Properties WHERE Property_Name = list[2]),
etc.
)"
But that's a mess, and doesn't happen to be available to me anyway since I'm using MySQL.
Anyone know the right way to do this?
| ← Concealed carry | Tony Blair is the man → |
J$ (http://alpha-geek.com) wrote:
J$ (http://alpha-geek.com) wrote:
Oh wait, that won't work in any way shape or form. What was I thinking?
That will teach me to write knee-jerk SQL...
Goerge Schlossnagle wrote:
You can join against properties multiple times in you single query
SELECT Objects.* FROM Objects o, Properties a, Properties b WHERE o.oid = a.oid AND o.oid = b.oid AND a.pname = 'this' AND b.pname = 'that'
Keith (http://keithdevens.com/) wrote:
Eew. OK. I was hoping there'd be another way to do it besides doing many joins. Thanks though, that's at least better than what I was using. I wonder if there's still another way.
Ned Batchelder (http://nedbatchelder.com) wrote:
You could avoid the INTERSECT but do the same sort of query with:
Friday, March 5, 2004
Permalink
SELECT * FROM Objects WHERE
Object_Id IN (SELECT Object_Id FROM Properties WHERE Property_Name = list[0]) AND
Object_Id IN (SELECT Object_Id FROM Properties WHERE Property_Name = list[1]) AND
Object_ID IN (SELECT Object_Id FROM Properties WHERE Property_Name = list[2])
)
Keith (http://keithdevens.com/) wrote:
Here's basically what I wound up using. I normalized some more, so I had to do some more joins in there...
SELECT a.Bookmark_Id
FROM (
Bookmark_Keywords a
INNER JOIN Bookmark_Keyword_Info bki0
USING ( Bookmark_Keyword_Id )
) INNER JOIN (
Bookmark_Keywords b
INNER JOIN Bookmark_Keyword_Info bki1
USING ( Bookmark_Keyword_Id )
) ON (a.Bookmark_Id = b.Bookmark_Id)
INNER JOIN (
Bookmark_Keywords c
INNER JOIN Bookmark_Keyword_Info bki2
USING ( Bookmark_Keyword_Id )
) ON (a.Bookmark_Id = c.Bookmark_Id)
WHERE bki0.Bookmark_Keyword_Name = 'java' AND
bki1.Bookmark_Keyword_Name = 'scripting' AND
bki2.Bookmark_Keyword_Name = 'python'
And that's all dynamically generated based on what keywords (properties) are asked for. The base case looks like:
SELECT a.Bookmark_Id
FROM (
Bookmark_Keywords a
INNER JOIN Bookmark_Keyword_Info bki0
USING ( Bookmark_Keyword_Id )
)WHERE bki0.Bookmark_Keyword_Name = 'java'
Feel free to post a comment below. Please see my comment policy.
Formatting Rules (No HTML):
Generated in about 0.204s.
(Used 8 db queries)
Off of the top of my head, this may do what you want.
SELECT *
FROM Objects o
JOIN Properties p ON p. Object_Id = p. Object_Id
WHERE p.Property_Name = 'this'
AND p.Property_Name = 'that'
AND p.Property_Name = 'foo'
AND p.Property_Name = 'bar'
AND p.Property_Name = 'baz'
AND p.Property_Name = 'quux'
.
.
.