Help with Query

I have a table which holds system variables for a program I am writing.

It looks like this...

(AccountName VarChar(128), SettingName VarChar(20), Sequence Int, Setting VarChar(255))

There is a 'ALL USERS' AccountName and then individual users account names... such as DOMAIN\USERNAME. I Would like to Select all of the records containing 'ALL USERS' but overide the 'All USERS' entries with the individual user name settings where the SettingNames are equal.

AccountName SettingName Sequence Setting
ALL USERS DRIVESEARCH 1 D
ALL USERS DRIVESEARCH 2 G
ALL USERS DRIVESEARCH 3 H
ALL USERS DRIVESEARCH 4 I
ALL USERS DRIVESEARCH 5 J
ALL USERS JOBAUTHVIEWER 1 \\Path
ALL USERS OBJMDLWORD 1 wpfiles
ALL USERS RECENTFILESTODISPLAY 1 16
DOMAIN\USERRECENTFILESTODISPLAY 1 100

I would like...

ALL USERS DRIVESEARCH 1 D
ALL USERS DRIVESEARCH 2 G
ALL USERS DRIVESEARCH 3 H
ALL USERS DRIVESEARCH 4 I
ALL USERS DRIVESEARCH 5 J
ALL USERS JOBAUTHVIEWER 1 \\Path
ALL USERS OBJMDLWORD 1 wpfiles
DOMAIN\USERRECENTFILESTODISPLAY 1 100

Notice Recentfilestodisplay is down to one instead of two.
Is there a way to query this?something like this should do it shouldn't it?

select AccountName, SettingName, Sequence Int, Setting
from tblSystemVariables where AccountName = @.SpecificName
Union
select AccountName, SettingName, Sequence Int, Setting
from tblSystemVariables where
AccountName = 'ALL USERS'
and SettingName not in
(select SettingName from tblSystemVariables where AccountName = @.SpecificName)

where tblSystemVariables is your table and @.SpecificName is your specific domain/user combo.

Does that work?

Not really that elegant I know...|||Thank You! Works Great

Ended up with:

SELECT AccountName, SettingName, Sequence, Setting
FROM LoadSystemSettings
WHERE AccountName = 'DOMAIN\USER'
UNION
SELECT AccountName AS AccountName1, SettingName AS SettingName1, Sequence AS Sequence1, Setting AS Setting1
FROM LoadSystemSettings
WHERE AccountName = 'ALL USERS' AND SettingName NOT IN
(SELECT SettingName
FROM dbo.LoadSystemSettings
WHERE AccountName = 'DOMAIN\USER')

Was Missing the "NOT IN" Clause.

Kent|||Glad to be of service. Have a good one.

Help with phoneBook db design

Hi All!!
I need to build small phone book db.
There are phone numbers and name (to whom phone belong)
There are distribution lists that holds phone numbers and also can hold
another lists.
There are Users to whom distribution lists and or phones belong.
Any ideas, implementations ??
TNX
Imagination is more important then knowledge. (A.Einshtein)
Message posted via http://www.droptable.com
E B via droptable.com wrote:
> Hi All!!
> I need to build small phone book db.
> There are phone numbers and name (to whom phone belong)
> There are distribution lists that holds phone numbers and also can hold
> another lists.
> There are Users to whom distribution lists and or phones belong.
> Any ideas, implementations ??
> TNX
> --
> Imagination is more important then knowledge. (A.Einshtein)
> Message posted via http://www.droptable.com
Please don't multi-post!
Answered in comp.databases.ms-sqlserver
David Portas
SQL Server MVP

Help with phoneBook db design

Hi All!!
I need to build small phone book db.
There are phone numbers and name (to whom phone belong)
There are distribution lists that holds phone numbers and also can hold
another lists.
There are Users to whom distribution lists and or phones belong.
Any ideas, implementations '?
TNX
Imagination is more important then knowledge. (A.Einshtein)
Message posted via http://www.droptable.comE B via droptable.com wrote:
> Hi All!!
> I need to build small phone book db.
> There are phone numbers and name (to whom phone belong)
> There are distribution lists that holds phone numbers and also can hold
> another lists.
> There are Users to whom distribution lists and or phones belong.
> Any ideas, implementations '?
> TNX
> --
> Imagination is more important then knowledge. (A.Einshtein)
> Message posted via http://www.droptable.com
Please don't multi-post!
Answered in comp.databases.ms-sqlserver
David Portas
SQL Server MVP
--

Help with phoneBook db design

Hi All!!
I need to build small phone book db.
There are phone numbers and name (to whom phone belong)
There are distribution lists that holds phone numbers and also can hold
another lists.
There are Users to whom distribution lists and or phones belong.
Any ideas, implementations '?
TNX
--
Imagination is more important then knowledge. (A.Einshtein)
Message posted via http://www.sqlmonster.comE B via SQLMonster.com wrote:
> Hi All!!
> I need to build small phone book db.
> There are phone numbers and name (to whom phone belong)
> There are distribution lists that holds phone numbers and also can hold
> another lists.
> There are Users to whom distribution lists and or phones belong.
> Any ideas, implementations '?
> TNX
> --
> Imagination is more important then knowledge. (A.Einshtein)
> Message posted via http://www.sqlmonster.com
Please don't multi-post!
Answered in comp.databases.ms-sqlserver
--
David Portas
SQL Server MVP
--