Thread: Querying for name/value pairs in reverse

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

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?

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...