I have previously tried the below approach

LOCK ROW FOR ACCESS

SELECT c.numb, SUM(c.results),

CASE

WHEN SUM(c.results) MOD 10 = '0' THEN

'Found in A but could be duplicate'

WHEN SUM(c.results) MOD 13 = '0' THEN

'Found in E but could be duplicate'

WHEN SUM(c.results) = '0' THEN

'Not found'

END FROM (

SELECT a.numb,

CASE

WHEN TRIM(b.event_cd) IS NOT NULL

OR TRIM(b.event_cd)<>'' THEN '10'

ELSE '0'

END AS results

FROM T

LEFT JOIN A

ON T.numb = A.numb

AND A.event_cd = 'OK'

UNION ALL

SELECT a.numb,

CASE

WHEN TRIM(b.event_cd) IS NOT NULL

OR TRIM(b.event_cd)<>'' THEN '13'

ELSE '0'

END AS results

FROM T

LEFT JOIN E

ON T.numb = E.numb

AND E.event_cd = 'OK' ) c

GROUP BY 1;

but this way I was not able to identify the occurences in each of the two tables A and E.

SEL

E1.COL1,

SUM(ACOUNT) AS ACOUNT,

SUM(BCOUNT) AS BCOUNT

FROM E1

LEFT OUTER JOIN

(SEL COL1,COUNT(*) AS ACOUNT FROM A1 GROUP BY 1) A1

ON A1.COL1=E1.COL1

LEFT OUTER JOIN

(SEL COL1,COUNT(*) AS BCOUNT FROM B1 GROUP BY 1) B1

ON B1.COL1=E1.COL1

GROUP BY 1

Can you show some sample data and expected result?

Dieter

Hello,

I have a difficult task (or at least this is how I see it) to implement using SQL. Here is the case:

- there are two tables: A and E each one having a numeric column. Each of the two tables contain checkpoints(numbers) for a specific region.

- one table T contains a numeric column with checkpoint(numeric). I want to verify if each number from T is either in A or in E. Is it possible that one checkpoint from table T can be found both in A (possible several times-duplicates) or in E (possible several times-duplicates). By removing duplicates from each table finding a solution would be more simple, but I do also need to know the number of occurences from the two tables.

The result I want to achieve is to display using SQL if each number from table T is in either table A or table E.

For example:

If number 1234 was found twice in table A and once in table E, I need to have the result 'AAE'

If number 1234 was found three times in table A and was not found in table E, I need to have the result 'AAA'

If number 1234 was not found neither in table A nore in table E, the result should be 'N'

Can you share any ideas how to solve it?

Thank you.