CODEWITHSUNDEEP
sqlsql-server
Brian Larsen
Brian Larsen

Reputation: 25

SQL QUERY Inner Join missing data

I need some help getting this SQL Query to work

All tags are listed in the table Tag, but my joins is causing this trouble. Please let me know how to list both type 1 (Discrete - no units) and type 2 (Analog with units) in the same query.

This is the current result:

wwTagKey TagName                        Description               TagType   Unit
---------------------------------------------------------------------------------
527      Æ0_0_02_VEN_MR6_02_Q.IO.HIL    High Limit Alarm Level    1         % rH
532      Æ0_0_02_VEN_MR6_02_Q.IO.LOL    Low Limit Alarm Level     1         % rH
547      Æ0_0_02_VEN_MR6_02_Q.IO.PV     Relative humidity         1         % rH
541      Æ0_0_02_VEN_MR6_03_Q.IO.HIL    High Limit Alarm Level    1         % rH

I need all tags with TagType no. 1 to included in this result, but this type (Discrete) does not include a Unit.

Regarding this EUKey (Unit), then the database does not contain a table where Unit and wwTagKey are related, which is why I have joined the Tagname.

SELECT
    Tag.wwTagKey
    ,Tag.TagName
    ,Tag.Description
    ,Tag.TagType
    ,EngineeringUnit.Unit
FROM
    [Runtime].[dbo].[Tag]
INNER JOIN 
    [Runtime].[dbo].[AnalogTag] ON .AnalogTag.TagName = Tag.TagName
INNER JOIN 
    [Runtime].[dbo].[EngineeringUnit] ON AnalogTag.EUKey = EngineeringUnit.EUKey            
WHERE 
    TagType < 3;

Upvotes: 0

Views: 1579

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270773

Doesn't LEFT JOIN do what you want?

SELECT t.wwTagKey, t.TagName, t.Description, t.TagType,
        eu.Unit
FROM [Runtime].[dbo].[Tag] t LEFT JOIN
     [Runtime].[dbo].[AnalogTag] ant
     ON ant.TagName = t.TagName LEFT JOIN 
     [Runtime].[dbo].[EngineeringUnit] eu
     ON ant.EUKey = au.EUKey            
WHERE t.TagType IN (1, 2);

Upvotes: 1

Joakim Danielson
Joakim Danielson

Reputation: 52043

You can separate your query into two and use union

SELECT wwTagKey, TagName, Description, TagType, e.Unit
FROM [Runtime].[dbo].[Tag]
INNER JOIN [Runtime].[dbo].[AnalogTag] a ON a.TagName = Tag.TagName
INNER JOIN [Runtime].[dbo].[EngineeringUnit] e ON a.EUKey = e.EUKey           
WHERE TagType = 2
UNION ALL
SELECT wwTagKey, TagName, Description, TagType, ‘’
FROM [Runtime].[dbo].[Tag]
WHERE TagType = 1;

Alternatively you can you use left join but without knowing your data I can’t tell if you need to replace the first or the second or both inner joins with left joins.

Upvotes: 0

Related Questions