pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedQuerying for name/value pairs in reverse
4+ messages / 3 participants
[nested] [flat]
* Querying for name/value pairs in reverse
@ 2006-07-15 09:24 Stefan Arentz <stefan.arentz@gmail.com>
2006-07-15 12:00 ` Re: Querying for name/value pairs in reverse Paul S <plabrh1@gmail.com>
2006-07-15 22:21 ` Re: Querying for name/value pairs in reverse Aaron Bono <postgresql@aranya.com>
0 siblings, 2 replies; 4+ messages in thread
From: Stefan Arentz @ 2006-07-15 09:24 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Querying for name/value pairs in reverse
2006-07-15 09:24 Querying for name/value pairs in reverse Stefan Arentz <stefan.arentz@gmail.com>
@ 2006-07-15 12:00 ` Paul S <plabrh1@gmail.com>
2006-07-15 22:26 ` Re: Querying for name/value pairs in reverse Aaron Bono <postgresql@aranya.com>
1 sibling, 1 reply; 4+ messages in thread
From: Paul S @ 2006-07-15 12:00 UTC (permalink / raw)
To: pgsql-sql; stefan.arentz@gmail.com
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.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?
>
> 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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Querying for name/value pairs in reverse
2006-07-15 09:24 Querying for name/value pairs in reverse Stefan Arentz <stefan.arentz@gmail.com>
2006-07-15 12:00 ` Re: Querying for name/value pairs in reverse Paul S <plabrh1@gmail.com>
@ 2006-07-15 22:26 ` Aaron Bono <postgresql@aranya.com>
0 siblings, 0 replies; 4+ messages in thread
From: Aaron Bono @ 2006-07-15 22:26 UTC (permalink / raw)
To: Paul S <plabrh1@gmail.com>; +Cc: pgsql-sql; stefan.arentz@gmail.com
On 7/15/06, Paul S <plabrh1@gmail.com> wrote:
>
> 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.
>
This works - the subselect I sent earlier kind of does this (the subselect
can act as a temp table in memory so you don't have to create a physical
one).
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.
>
I wonder if the use of XML in a database is a very good idea. (am I
treading on religious territory here?) I can think of some examples where
XML can be useful but the problem I see with it is that your data structure
is embedded in a single field and your database schema does not describe
your data structure very well anymore. I always like to use the database
schema as a way to document the data structure so if you have the DB
diagrams, you can understand everything there.
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...
>
I wholeheartedly agree. If you don't adopt a good naming convention like
this, you will come to regret it as your application grows in size and
complexity.
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
==================================================================
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Querying for name/value pairs in reverse
2006-07-15 09:24 Querying for name/value pairs in reverse Stefan Arentz <stefan.arentz@gmail.com>
@ 2006-07-15 22:21 ` Aaron Bono <postgresql@aranya.com>
1 sibling, 0 replies; 4+ messages in thread
From: Aaron Bono @ 2006-07-15 22:21 UTC (permalink / raw)
To: Stefan Arentz <stefan.arentz@gmail.com>; +Cc: pgsql-sql
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
==================================================================
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2006-07-15 22:26 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-07-15 09:24 Querying for name/value pairs in reverse Stefan Arentz <stefan.arentz@gmail.com>
2006-07-15 12:00 ` Paul S <plabrh1@gmail.com>
2006-07-15 22:26 ` Aaron Bono <postgresql@aranya.com>
2006-07-15 22:21 ` Aaron Bono <postgresql@aranya.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox