Results 1 to 4 of 4

Thread: Help with Query

  1. #1
    Join Date
    Dec 2003
    Location
    Boise, Idaho
    Posts
    9

    Unanswered: 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?

  2. #2
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    something like this should do it shouldn't it?

    Code:
    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....

  3. #3
    Join Date
    Dec 2003
    Location
    Boise, Idaho
    Posts
    9
    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

  4. #4
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    Glad to be of service. Have a good one.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •