CODEWITHSUNDEEP
sqlsql-server
Colt
Colt

Reputation: 3

Return only 1 value from a table that is related to a child table with multiple related records

Table A contains an ID that is related to table B. Table B also contains an ID that is related to table C. I need to figure out how to bring back only one value, based on a hierarchy that is defined outside of the tables. For Example: Table A (Devices)

-------------------------
| DeviceID | Device Name |
-------------------------
|___001____| Server1_____|
--------------------------
|___002____| server2_____|
--------------------------

Table B (Translation Table)

-------------------------
| DeviceID | Value ID    |
-------------------------
|___001____|____456______|
--------------------------
|___002____|____456______|
--------------------------
|___001____|____789______|
--------------------------
|___002____|____123______|

Table C (Value Table)

-------------------------
|_ValueID__|___Value_____|
-------------------------
|___123____|____LOW______|
--------------------------
|___456____|____MED______|
--------------------------
|___789____|____HIGH_____|
--------------------------

What I need is to evaluate each ID from table a and if it has a related value for HIGH (789) I need to bring back HIGH, if it DOESN'T if a related HIGH value then I need to check and see if it is related to a MED value. If the device is not related to a HIGH value but it is related to a MED value, then bring back MED. Lastly doing the same thing for LOW. Devices that don't have a value do not need to be returned. Desired Output

------------------------------
|___Device Name___|___COST___|
------------------------------
|___Server1_______|___HIGH___|
------------------------------
|___Server2_______|___MED____|
------------------------------

How would i right a query for this information, especially if it is possible for the value ID's to change.

Upvotes: 0

Views: 54

Answers (2)

Gerard H. Pille
Gerard H. Pille

Reputation: 2578

select a.*,
       coalesce(
         (select C.value from C, B
           where c.Value = 'HIGH'
             and b.ValueID = c.ValueID
             and b.DeviceID = a.DeviceID),
         (select C.value from C, B
           where c.Value = 'MED'
             and b.ValueID = c.ValueID
             and b.DeviceID = a.DeviceID),
         (select C.value from C, B
           where c.Value = 'LOW'
             and b.ValueID = c.ValueID
             and b.DeviceID = a.DeviceID)
       ) Value
  from a
  where exists (select null from C,B
                  where b.ValueID = c.ValueID
                    and b.DeviceID = a.DeviceID)

The coalesce will return the first not null value, which probably satisfies your desires.

Upvotes: 1

Murali Dhar Darshan
Murali Dhar Darshan

Reputation: 295

Based on your inputs and desired output, following query may give desired result:

  select a.DeviceName, c.Value 
  from TableA a inner join TableB b 
  on a.DeviceID=b.DeviceID
  inner Join TableC c
  on b.ValueID=c.ValueID

Upvotes: 0

Related Questions