title: SQL query to show used records in the result set
body: <p>I have a Table like below</p>
  
  <p><strong>Commodity</strong></p>
  
  <p><a href="https://i.stack.imgur.com/wlgWo.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/wlgWo.png" alt="enter image description here"></a></p>
  
  <p>Now I have another table named</p>
  
  <p><strong>Worklist</strong> </p>
  
  <p><a href="https://i.stack.imgur.com/ymj59.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/ymj59.png" alt="enter image description here"></a></p>
  
  <p>And there is a many to many relationship b.w worklist and commodities</p>
  
  <p><strong>Worklist_commodities</strong></p>
  
  <p><a href="https://i.stack.imgur.com/nCacY.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/nCacY.png" alt="enter image description here"></a></p>
  
  <p>Now I want to get all the commodities which are not excluded but if commodities are used in work list and it’s also marked excluded then I also want to load that result in the result set.</p>
  
  <p>So what will be the final query to load the result. The problem can be easily solved by the unions but I don’t use unions because I used some kind of ORM which don’t have a union functionality.</p>
  
  <p>SQL UNION QUERY I used to get the result set i want to get the same result without UNION</p>
  
  <pre><code>`SELECT this_.ID as ID,
         this_.Name AS Name,
         this_.ParentID AS ParentID,
         this_.IsExcluded AS IsExcluded
         FROM   INFO_Commodities this_ WHERE this_.IsExcluded = 0  
    UNION
    SELECT this_.ID as ID,
         this_.Name AS Name,
         this_.ParentID AS ParentID,
         this_.IsExcluded AS IsExcluded
         FROM   INFO_Commodities this_ WHERE this_.IsExcluded = 1 AND this_.ID IN 
    (SELECT wc.[CommodityID]
      FROM SWF_WorkLists as w INNER JOIN SWF_WorkListCommodities AS wc on w.ID 
      = wc.WorklistID WHERE wc.[CommodityID] = this_. ID)
  </code></pre>
  
  <p>`</p>