Skip to content
Advertisement

SQL query for checking whether a column with a specific value has its correspondent row with another specific value in the same column

I am not completely sure how to solve this problem.

My data looks like this:

ENT   ACC L_TYPE K_TYPE
0100  123 660    S
0100  123 673    S

And I would like to see whether L_TYPE 673 has no correct correspondent with L_TYPE = 660.

I want to find out whether the column L_TYPE with the value 673 has also its correspondent 660. All the other values do not change.

However, I would need the SQL as general as possible.

The only thing that I was capable of writing was this. So, I was able to find out that at least many rows with different values are in L_TYPE.

SELECT ENT, ACC, COUNT(*) FROM TABLE
WHERE K_TYPE = 'S'
GROUP BY ENT, ACC
HAVING COUNT(*)>1;

I thought about using a cursor but I am not quite sure whether this solves my problem.

Maybe somebody knows how to solve this problem? I use DB2 as a database.

Advertisement

Answer

To get the rows for which every ENT, ACC combination there are L_TYPE of both 600 and 673.

SELECT ENT, ACC, COUNT(*) FROM TABLE
WHERE K_TYPE = 'S' and l_type in (660,673)
GROUP BY ENT, ACC
HAVING COUNT(*)>1;
User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement