in reply to Querying Meta Data

I use this SQL query: 

select n.id
from nodes n, metadata m
where n.id=m.id
  and ((m.name='color' and m.value='red') or
       (m.name='name' and m.value='george'))
group by n.id
having count(n.id)=2;

[download]
Of course, the query can be built dinamically and should be adapted to your needs. I'm not an SQL expert, so I welcome any opinion on this approach.

Ciao, Valerio

Replies are listed 'Best First'.
Re: Re: Querying Meta Data
by eric256 (Parson) on Jul 24, 2003 at 23:18 UTC
    Thats a nice twist on it. Any ideas on weather that is better or worse speed wise?
    ___________
    Eric Hodges
[reply]
      The relative speed of the self-join vs HAVING option is really very difficult to assess - it really depends on the data, the way the indexes are set up, etc.

      In addition I think that HAVING is a Sybase/MS-SQL extension, so it may not be available with MySQL.

      Michael

[reply]

        Sorry for being late... The query above works perfectly with MySql (v3.23.* and v4.*). Besides that, I think HAVING clause is standard SQL. ++ for your answer!

        Ciao, Valerio

[reply]

In Section Seekers of Perl Wisdom