How can I make all values of one identifier the same based a on value in a row?

SELECT a.IDENTIFIER,
    a.NAME,
    a.CATEGORY,
    b.IDENTIFIER_TYPE,
    b.NAME,
    CASE
        WHEN b.IDENTIFIER_TYPE = 111 THEN 'PRESENT'
        ELSE 'NOT PRESENT'
    END AS HAS111
FROM TABLE_A a
LEFT JOIN IDENTIFIER_TYPE_TABLE b ON a.IDENTIFIER = b.IDENTIFIER
WHERE a.IDENTIFIER IN (1, 2, 3, 4)
;

Here is the code I’m using, and the data I’m getting is:

But I want the data to look like this:

Basically, I need this:

td {
  text-align: center;
}

<table>
  <tbody>
    <tr>
      <td>IDENTIFIER</td>
      <td>NAME</td>
      <td>CATEGORY</td>
      <td>IDENTIFIER_TYPE</td>
      <td>HAS111</td>
    </tr>
    <tr>
      <td>123</td>
      <td>item123</td>
      <td>1</td>
      <td>778</td>
      <td>not present</td>
    </tr>
    <tr>
      <td>123</td>
      <td>item123</td>
      <td>1</td>
      <td>127</td>
      <td>not present</td>
    </tr>
    <tr>
      <td>123</td>
      <td>item123</td>
      <td>1</td>
      <td>137</td>
      <td>not present</td>
    </tr>
    <tr>
      <td>456</td>
      <td>item456</td>
      <td>1</td>
      <td>122</td>
      <td>not present</td>
    </tr>
    <tr>
      <td>456</td>
      <td>item456</td>
      <td>2</td>
      <td>87</td>
      <td>not present</td>
    </tr>
    <tr>
      <td>456</td>
      <td>item456</td>
      <td>2</td>
      <td>444</td>
      <td>not present</td>
    </tr>
    <tr>
      <td>789</td>
      <td>item789</td>
      <td>2</td>
      <td>289</td>
      <td>present</td>
    </tr>
    <tr>
      <td>789</td>
      <td>item789</td>
      <td>2</td>
      <td>111</td>
      <td>present</td>
    </tr>
    <tr>
      <td>789</td>
      <td>item789</td>
      <td>2</td>
      <td>75</td>
      <td>present</td>
    </tr>
  </tbody>
</table>

You see item789 has 111 in one of the rows, therefore I need all the other rows to say “present” for item789. The others don’t have 111, so they have no 111 present. Does this make sense?

Answer

You can use EXISTS :

SELECT a.IDENTIFIER, a.NAME, a.CATEGORY, b.IDENTIFIER_TYPE, b.NAME,
       (CASE WHEN EXISTS (SELECT 1 
                          FROM IDENTIFIER_TYPE_TABLE b1 
                          WHERE a.IDENTIFIER = b1.IDENTIFIER AND 
                                b1.IDENTIFIER_TYPE = 111
                         ) 
             THEN 'PRESENT' ELSE 'NOT PRESENT' 
        END) AS HAS111
FROM TABLE_A a LEFT JOIN 
     IDENTIFIER_TYPE_TABLE b 
     ON a.IDENTIFIER = b.IDENTIFIER
WHERE a.IDENTIFIER IN (1, 2, 3, 4);

Advertisement

Answer