First, I'm not sure if the title represent the best of the issue. Any better suggestion is welcomed. My problem is I have the following table:
+----+----------+-------+-----------------+ | ID | SUPPLIER | BUYER | VALIDATION_CODE | +----+----------+-------+-----------------+ | 1 | A | Z | 937886521 | | 2 | A | X | 937886521 | | 3 | B | Z | 145410916 | | 4 | C | V | 775709785 | +----+----------+-------+-----------------+
I need to show SUPPLIERS A and B which have BUYER Z, X. However, I want this condition to be one-to-one relationship rather than one-to-many. That is, for the supplier A, I want to show the column with ID: 1, 2. For the supplier B, I want to show the column 3 only. The following script will show the supplier A with all possible buyers (which I do not want):
SELECT *
FROM validation
WHERE supplier IN ( 'A', 'B' )
AND buyer IN ( 'X', 'Z');
This will show the following pairs: (A,Z), (A,X), (B, Z). I need to show only the following: (A,X)(B,Z) in one statement.
The desired result should be like this:
+----+----------+-------+-----------------+ | ID | SUPPLIER | BUYER | VALIDATION_CODE | +----+----------+-------+-----------------+ | 2 | A | X | 937886521 | | 3 | B | Z | 145410916 | +----+----------+-------+-----------------+
You can update the WHERE clause to filter on the desired pairs:
select *
from sample
where (upper(supplier),upper(buyer))
in (('A','X'),('A','Y'),('A','Z'),('B','X'),('B','Y'),('B','Z'));
I used the UPPER function based on your mixed case examples.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments