Re: Querying for name/value pairs in reverse

This is definitely doable.

one "Set" way that I could think of doing this would be to first compile a
temp table with all of the Value/Pairs that your looking to search for and
then just JOIN the ID's (in this case it would be the Value and Pair) to the
ErrorValues table. This should give you all of the ErrorID's in the
ErrorValues table which would then allow you to JOIN up against the Errors
table to get information like CreateDate.

Another way, would be to use XML instead of Value/Pair to turn it into a
Node and Value type of thing. You could then use XQuery to search inside of
the XML attribute for what you were looking for. SET theory would be a
better alternative but this is just a different idea.

One Naming convention tip, I like to name my PrimaryKey's something more
descriptive than just ID. You'll notice that your ErrorValues table had to
include the foreign key called ErrorID that actually relates to attribute ID
in the Errors table. When your looking at miles and miles of code or
reviewing JOIN syntax "a.ID = b.ErrorID" sometimes it's easier to validate
if it looks like this. "a.ErroID = b.ErrorID". Just my 2 cents...

Hope that helps.

-Paul

On 7/15/06, Stefan Arentz <stefan(dot)arentz(at)gmail(dot)com> wrote:
>
> I'm no SQL expert by any means so I'm wondering if something like this
> is possible.
>
> I have two tables like this:
>
> create table Errors (
> Id serial not null,
> CreateDate timestamp not null,
> primary key (Id)
> );
>
> create table ErrorValues (
> Id serial not null,
> ErrorId int not null,
> Name varchar(255) not null,
> Value text not null,
> primary key (Id),
> foreign key (ErrorId) references Errors (Id)
> );
>
> Now, selecting specific ErrorValues with a bunch of names that are
> related to an Error is of course pretty simple. But I want to go the
> other way. I want to query for:
>
> 'give me all Errors that have the Code=1234 AND Address=1.2.3.4 AND
> Type=OSX Name/Value pairs'
>
> What is a good recipe to deal with this? Is something like this
> possible with standard sql? Is there a nice PG way to do this?
>
> Are there good books that cover real world stuff like this?
>
> So many questions from a SQL noob.
>
> S.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>