Want a table to hold system properties that user can override.

[Springy]

I'd like to develop a query that allows me to store name:value properties for each user. This is easily done with a table like this called user_properties:

"user_id" VARCHAR(12) NOT NULL,
"pname" VARCHAR(20) NOT NULL,
"pvalue" VARCHAR(20) NOT NULL,

This is the query i use to get properties for user 'tim':
SELECT
defaultproperties.pname,
defaultproperties.pvalue
FROM
public.user_properties defaultproperties
WHERE
(defaultproperties.user_id = 'tim')

pname pvalue
propertyOne 10
propertyTwo 20

I'd like to take this to the next stage and have default properties held under the user_id 'default' and each user only has rows where they have overriden a default value.
So i'd get something like below where the values for propertyOne and propertyTwo are 'tim's values and the others are the 'default' users values.

pname pvalue
propertyOne 10
propertyTwo 20
propertyThree 3
propertyFour 4
propertyFive 5

I've tried doing various left joins on the 'user_properties' table but have yet to get the desiered result.

Can anybody help?

Tim..

 

[eheidrich]

I am not really sure what you want, but if you can get your specific user properties on one selection and the default ones on the other, you might try to use a UNION clause, first the user select UNION the seconde select. Beware to use the same field names.

EX:

SELECT
defaultproperties.pname,
defaultproperties.pvalue
FROM
public.user_properties defaultproperties
WHERE
(defaultproperties.user_id = 'tim')

UNION

SELECT
default_properties.pname,
default_properties.pvalue
FROM
public.default_properties

I hope I have helped!

 

[stoolpigeon]

You can do it with a sub-query. Try something like this and see if it is what you are trying to do.

SELECT * FROM user_properties WHERE user_id = 'tim' OR
(user_id = 'default' AND pname NOT IN 
(SELECT pname FROM user_properties WHERE user_id = 'tim'));

This will list all the possible pname values- one for each tim has, one for the rest held be default but not tim.

 

[Springy]

Thats great

Thanks for that. Works just as I had wanted.


Tim..