From: | "Aaron Bono" <postgresql(at)aranya(dot)com> |
---|---|
To: | "Stefan Arentz" <stefan(dot)arentz(at)gmail(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Querying for name/value pairs in reverse |
Date: | 2006-07-15 22:21:02 |
Message-ID: | bf05e51c0607151521v3385a6fey4eb5aa233e0a50e4@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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?
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
==================================================================
From | Date | Subject | |
---|---|---|---|
Next Message | Aaron Bono | 2006-07-15 22:26:28 | Re: Querying for name/value pairs in reverse |
Previous Message | Aaron Bono | 2006-07-15 22:10:14 | Re: Doubt about User-defined function. |