title: Oracle SQL selecting with condition a if b condition does not exist
body: <p>I'm stuck with the exists and not exists keywords.</p>
  
  <p>I have a table (Oracle Portal table) which has, summarized, following structure:</p>
  
  <pre><code>ID    LANGUAGE    CAPTION
  1     NL          DUTCH CAPTION
  1     F           FRENCH CAPTION
  2     NL          DUTCH CAPTION
  3     F           FRENCH CAPTION
  </code></pre>
  
  <p>I need a select which always selects two rows per ID, even if a F row or NL row does not exists. Then the row should be selected with the non-existing language in column language but with the caption of the existing row (with language F) </p>
  
  <p>So the output of the query should be :</p>
  
  <pre><code>ID    LANGUAGE    CAPTION
  1     NL          DUTCH CAPTION
  1     F           FRENCH CAPTION
  2     NL          DUTCH CAPTION
  2     F           DUTCH CAPTION
  3     F           FRENCH CAPTION
  3     NL          FRENCH CAPTION
  </code></pre>
  
  <p>Is this possible? </p>
  
  <p>Very curious, 
  and already a big big thanks!!</p>
  
  <p>ps: I have something like this but it's totally not what I want.</p>
  
  <pre><code>SELECT id, language, display_name
  FROM portal.wwsbr_all_folders p 
  WHERE caid = 55 
  AND DISPLAY_IN_PARENT_FOLDER = 1 
  AND ((upper(language) = 'NL' AND not exists (SELECT * FROM portal.wwsbr_all_folders p2 WHERE p.id = p2.id and language IN ('F')))
        OR
        (upper(language) = 'F' AND not exists (SELECT * FROM portal.wwsbr_all_folders p2 WHERE p.id = p2.id and language IN ('NL')))
      )
  AND parent_id = 4030963 
  ORDER BY sub_folder_sequence
  </code></pre>
  
  <p>EDIT:
  I'm getting the right results with the following query now </p>
  
  <pre><code>(SELECT id, CASE language WHEN 'nl' THEN 'f' ELSE 'f' END lang, display_name
  FROM portal.wwsbr_all_folders p 
  WHERE caid = 55 
  AND DISPLAY_IN_PARENT_FOLDER = 1 
  AND ((language = 'f' and exists(SELECT * FROM portal.wwsbr_all_folders p2 WHERE p2.id = p.id AND language = 'f')) 
      OR (language = 'nl' and not exists(SELECT * FROM portal.wwsbr_all_folders p2 WHERE p2.id = p.id AND language = 'f')))
  AND parent_id = 4656102)
  UNION ALL
  (
  SELECT id, CASE language WHEN 'f' THEN 'nl' ELSE 'nl' END lang, display_name
  FROM portal.wwsbr_all_folders p 
  WHERE caid = 55 
  AND DISPLAY_IN_PARENT_FOLDER = 1 
  AND ((language = 'nl' and exists(SELECT * FROM portal.wwsbr_all_folders p2 WHERE p2.id = p.id AND language = 'nl')) 
      OR (language = 'f' and not exists(SELECT * FROM portal.wwsbr_all_folders p2 WHERE p2.id = p.id AND language = 'nl')))
  AND parent_id = 4656102)
  </code></pre>