Databases
StackOverflow2013
Postsdbo
POOracle SQL selecting with condition a if b condition does not exist
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POOracle SQL selecting with condition a if b condition does not exist
    primarykey
    data
    text
    <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>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USKara
    1. USVerthosa
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POOracle SQL selecting with condition a if b condition does not exist
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload