X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id E13F69FA3B4 for ; Sat, 15 Jul 2006 19:21:07 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 82506-01 for ; Sat, 15 Jul 2006 22:21:04 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.185]) by postgresql.org (Postfix) with ESMTP id 1FAE19F95E3 for ; Sat, 15 Jul 2006 19:21:03 -0300 (ADT) Received: by nf-out-0910.google.com with SMTP id p77so167945nfc for ; Sat, 15 Jul 2006 15:21:02 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=gPdFqWu5P71kzH8lGeRrIMaKyBQjrWOvq1Ne1n8Meq1bvtpVqVpX4OyfgQJq+sRJe/xn0oXpTSs7jJW9gq4CuTVuDTXeIgEddK2bB9P8ZjRg7qZ7lfFgc9WKHzS6F+5K0ihzFItMdM6fc0OdbncOjsVXmwqPvEjvhNS/Lkfe184= Received: by 10.78.170.17 with SMTP id s17mr281889hue; Sat, 15 Jul 2006 15:21:02 -0700 (PDT) Received: by 10.78.29.15 with HTTP; Sat, 15 Jul 2006 15:21:02 -0700 (PDT) Message-ID: Date: Sat, 15 Jul 2006 17:21:02 -0500 From: "Aaron Bono" To: "Stefan Arentz" Subject: Re: Querying for name/value pairs in reverse Cc: pgsql-sql@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_42790_3331991.1153002062292" References: X-Google-Sender-Auth: f56d3274f33420e3 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200607/218 X-Sequence-Number: 25792 ------=_Part_42790_3331991.1153002062292 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 7/15/06, Stefan Arentz 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? Try this SELECT Errors.ID, Errors.CreateDate FROM Errors WHERE Errors.ID IN ( SELECT ErrorValues.id, FROM ErrorValues WHERE (ErrorValues.name = 'Code' AND ErrorValues.value = '1234') INTERSECT SELECT ErrorValues.id, FROM ErrorValues WHERE (ErrorValues.name = 'Address' AND ErrorValues.value = '1.2.3.4') INTERSECT SELECT ErrorValues.id, FROM ErrorValues WHERE (ErrorValues.name = 'Type' AND ErrorValues.value = 'OSX Name/Value pairs') ); ================================================================== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com ================================================================== ------=_Part_42790_3331991.1153002062292 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline

On 7/15/06, Stefan Arentz <stefan.arentz@gmail.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?

Try this

SELECT
   Errors.ID,
   Errors.CreateDate
FROM Errors
WHERE Errors.ID IN (
   SELECT
ErrorValues.id,
   FROM ErrorValues
   WHERE (ErrorValues.name = 'Code' AND ErrorValues.value = '1234')
   INTERSECT
   SELECT
   ErrorValues.id,
FROM ErrorValues
WHERE (ErrorValues.name = 'Address' AND ErrorValues.value = '1.2.3.4')
   INTERSECT
   SELECT
   ErrorValues.id,
FROM ErrorValues
WHERE (ErrorValues.name = 'Type' AND ErrorValues.value = 'OSX Name/Value pairs')

);

==================================================================
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
================================================================== ------=_Part_42790_3331991.1153002062292--