T-SQL - How to combine results of two tables with two different WHERE clauses

Question

I have 3 tables, VALUES, SETTINGS and CONTROLS. Every SETTING has one CONTROL both not necessarily has a VALUE.

VALUES          SETTINGS          CONTROLS
------          --------          --------
ID              ID                ID
SettingID       SettingName       SettingID
Value           ProductFamilyID   ControlName
OrderID

I have tried joins and unions but just can't get it working. What I would like is a query where the result would look like this:

SettingName     ControlName     Value
-----------     -----------     -----
Setting1        Control1        Value1
Setting2        Control58       Value22
Setting3        Control22       null

I forgot to mention, I would like to filter the result where VALUE.OrderID = '1' and also where SETTINGS.ProductFamilyID='2'

The problem I always face is, there are 52 SETTINGS but only 45 of them have VALUES. So with all the left joins, 7 SETTING is missing and I can only see the 45 which has VALUE.

With UNION I have something close but I have the results in separate rows (1 row for a CONTROL, another for its VALUE or no value)

SELECT 
    SETTINGS.ID, CONTROLS.ControlName 
FROM 
    CONTROLS
INNER JOIN 
    Settings ON CONTROLS.SettingID = SETTINGS.ID
WHERE 
    SETTINGS.ProductFamilyID = '2'

UNION 

SELECT 
    SETTINGS.ID, VALUES.Value 
FROM 
    VALUES
INNER JOIN 
    SETTINGS ON VALUES.SettingID = SETTINGS.ID
WHERE 
    VALUES.OrderID = '1'

The result of this is:

SettingID     ControlName
---------     -----------
1             Control1
1             Value1
2             Control2
2             Value2
3             Control3 <--- Control3 has no value
4             Control4
4             Value4

What I would like is to have the values in a separate column by the ControlName column.

What have you tried, and what did that give you? We can compare it to what you want to help us understand your question better. — BeanFrog, Oct 25 '16 at 14:40
Thanks for all the help, I have gotten to this far with the left join but I forgot to mention I would like to filter the results. I have added some more explanation, hope it makes sense. — Steven, Oct 25 '16 at 14:57
Added my try with UNION and a bit more explanation. Thanks again for the help. — Steven, Oct 25 '16 at 15:11

score 2 · Answer 1 · edited May 23 '17 at 11:46

2

You just need a left join on Values, you can use an inner join for the other one as you say:

Every SETTING has one CONTROL:

SELECT s.SettingName, s.ControlName, v,Value
FROM Settings s
INNER JOIN Controls c ON c.SettingID = s.ID
LEFT JOIN VALUES v ON v.SettingsID = s.ID

For reference, this is a great JOINS diagram, taken from here:

edited May 23 '17 at 11:46

Community

1
1

answered Oct 25 '16 at 14:44

Tanner

22,205
9
65
83

score 0 · Answer 2 · answered Oct 25 '16 at 14:43

Read up on the different types of SQL Join and how they work. Your requirement is very simple when you understand how left joins work:

select s.SettingName
      ,c.ControlName
      ,v.Value
from Settings s
    left join Controls c
        on(s.ID = c.SettingID)
    left join Values v
        on(s.ID = v.SettingID)

score 0 · Answer 3 · answered Oct 25 '16 at 14:43

You should have a look at left joins. Here is a query that might work for you case:

SELECT
    SETTINGS.SettingName,
    CONTROLS.ControlName,
    VALUES.Value
FROM
    SETTINGS
    LEFT JOIN CONTROLS
        ON SETTINGS.ID=CONTROLS.SettingID
    LEFT JOIN VALUES
        ON VALUES.SettingID=CONTROLS.SettingID

score 0 · Answer 4 · answered Oct 25 '16 at 14:44

0

SELECT
    s.SettingName
    , c.ControlName
    , v.Value
FROM
    SETTINGS s
    INNER JOIN CONTROLS c ON s.ID = c.SettingID
    LEFT OUTER JOIN VALUES v ON s.ID = v.SettingID;

answered Oct 25 '16 at 14:44

DVT

3,014
1
13
19

score 0 · Answer 5 · answered Oct 25 '16 at 14:44

The following query will give you - for each setting, the control name, and any value if there is one.

select SettingName, ControlName, Value
from SETTINGS S 
inner join CONTROLS C on S.ID = C.SettingID
left join VALUES V on S.ID = V.SettingID

The inner join takes rows which appear in both settings and controls.

The left join gives all rows that appear in settings, and any corresponding rows from values, or null if none.

score 0 · Accepted Answer · answered Oct 26 '16 at 07:04

Figured it out at last!

SELECT 
    FIRSTSET.ID, FIRSTSET.SETTINGNAME, FIRSTSET.CONTROLNAME, SECONDSET.VALUE 
FROM
    (SELECT 
        SETTINGS.ID AS ID, SETTINGS.SettingName 
    AS 
        SETTINGNAME,  CONTROLS.ControlName AS CONTROLNAME 
    FROM 
        CONTROLS
    INNER JOIN 
        SETTINGS ON CONTROLS.SettingID = Settings.ID
    WHERE 
        SETTINGS.ProductFamilyID = '2') 
    AS FIRSTSET
LEFT JOIN
    (SELECT 
        SETTINGS.ID 
    AS ID2, VALUES.Value AS VALUE FROM VALUES
    INNER JOIN 
        SETTINGS ON VALUES.SettingID = SETTINGS.ID
    WHERE 
        VALUES.SalesOrderLineID = '1') 
    AS SECONDSET
ON FIRSTSET.ID = SECONDSET.ID2

T-SQL - How to combine results of two tables with two different WHERE clauses

6 Answers6