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 .
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;