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