sqloracle-database

How to write a SQL query in Oracle to fetch rows from same table while keeping pair?


I have a simple table where I want to get names of elements by filtering on two arrays, however. I need to fetch in first row the first element of array 1 with the first element of array 2.

Example of my wrong query

SELECT
    p1.parameter_name,
    p2.parameter_name
FROM
    parameters p1,
    parameters p2
WHERE
    p1.parameter_name IN ( 'NOM', 'NOM', 'SALARY', 'RRRR', 'PSC3',
                           'PSC9H50' )
    AND p2.parameter_name IN ( 'AGE', 'ZZZ4', 'SDFGH', 'CDA', 'SD',
                               'KIY' );

The result is the cartesian query of the two tables but what I need is the following result

ParamA ParamB
NOM. AGE
NOM. ZZZ4
SALARY. SDFGH.

and so on to last element .


Solution

  • You can use IN with paired values:

    SELECT p1.parameter_name,
           p2.parameter_name
    FROM   parameters p1
           INNER JOIN parameters p2
           ON (p1.parameter_name, p2.parameter_name) IN (
                ('NOM', 'AGE'),
                ('NOM', 'ZZZ4'),
                ('SALARY', 'SDFGH'),
                ('RRRR', 'CDA'),
                ('PSC3', 'SD'),
                ('PSC9H50', 'KIY')
              );
    

    Or you can use a sub-query factoring clause (WITH clause):

    WITH parameter_pairs (name1, name2) AS (
      SELECT 'NOM',     'AGE'   FROM DUAL UNION ALL
      SELECT 'NOM',     'ZZZ4'  FROM DUAL UNION ALL
      SELECT 'SALARY',  'SDFGH' FROM DUAL UNION ALL
      SELECT 'RRRR',    'CDA'   FROM DUAL UNION ALL
      SELECT 'PSC3',    'SD'    FROM DUAL UNION ALL
      SELECT 'PSC9H50', 'KIY'   FROM DUAL
    )
    SELECT p1.parameter_name,
           p2.parameter_name
    FROM   parameters p1
           CROSS JOIN parameters p2
           INNER JOIN parameter_pairs pp
           ON     pp.name1 = p1.parameter_name
              AND pp.name2 = p2.parameter_name;