Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2003
    Posts
    21

    Red face Unanswered: Overriding Values

    Hi gurus, I'm basically trying to have global settings that can be overriden per object (let's say a forum as in the DDL below) ... let me explain, let's say I want to have 5 settings for all my forums, e.g. threads per page .. etc, I want those to work as global or default settings but at the same time I still want to be able to override some or all of the settings per forum.

    The global (or default) settings are in the ForumSettingDefaults table, the overriden settings are in the ForumSettings table, how can we select the settings for all forums so that the resultset contains:
    ForumID, SettingName, SettingValue (if the setting is overriden, ie. exists in the ForumSettings table, the value is taken from there otherwise we get the value from the ForumSettingDefaults table).

    Here's the DDL for the tables and an example for the data follows:

    create table Forums
    (
    ForumID int,
    ForumName varchar(50),
    -- other fields
    );

    create table ForumSettings
    (
    ForumID int,
    SettingName varchar(50),
    SettingValue varchar(50)
    );

    create table ForumSettingDefaults
    (
    SettingName varchar(50),
    SettingValue varchar(50)
    );

    For example if I have this data in my tables:

    -- Table Forums
    ForumID | ForumName
    1 | Forum1
    2 | Forum2

    -- Table ForumSettings
    ForumID | SettingName | SettingValue
    2 | Setting2 | 7

    -- Table ForumSettingDefaults
    SettingName | SettingValue
    Setting1 | 1
    Setting2 | 2
    Setting3 | 3
    Setting4 | 4
    Setting5 | 5

    I want to get this resultset:

    ForumID | SettingName | SettingValue
    1 | Setting1 | 1
    1 | Setting2 | 2
    1 | Setting3 | 3
    1 | Setting4 | 4
    1 | Setting5 | 5
    2 | Setting1 | 1
    2 | Setting2 | 7
    2 | Setting3 | 3
    2 | Setting4 | 4
    2 | Setting5 | 5

    * Notice the overriden value for Setting2 in fourm 2

    Any suggestions are highly appreciated...

  2. #2
    Join Date
    Apr 2003
    Posts
    21
    Never mind guys, I already solved the problem, here's the select statement:

    SELECT
    ForumID,
    SettingName,
    SettingValue
    FROM
    ForumSettings
    UNION ALL
    SELECT
    Forums.ForumID,
    ForumSettingDefaults.SettingName,
    ForumSettingDefaults.SettingValue
    FROM
    Forums,
    ForumSettingDefaults
    WHERE
    CONCAT(Forums.ForumID, ForumSettingDefaults.SettingName) NOT IN (SELECT CONCAT(ForumID, SettingName) FROM ForumSettings)

    If you have any better solution, it would be very nice to post it here.

    Thanks

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    better solution might be a LEFT OUTER JOIN
    Code:
    SELECT f.ForumID
         , d.SettingName
         , COALESCE(s.SettingValue,d.SettingValue) AS SettingValue
      FROM Forums AS f
    CROSS
      JOIN ForumSettingDefaults AS d
    LEFT OUTER
      JOIN ForumSettings AS s
        ON s.ForumID = f.ForumID
       AND s.SettingName = d.SettingName
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Apr 2003
    Posts
    21
    Thanks a lot, it works greatly!

Posting Permissions

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