Hi

I actually have a simple problem, which I'm trying to explain, before posting my SQL.

I have table A and table B. Table A contains rows from table B, but they have been through a process and have changed. I want to list out rows from both tables, typically a union.

Here comes the problem:
Every rows from table A should list out. From table B I only want rows where resource_id are alike in both tables, AND where voucher_no from table B isn't already listed.

I haven't found a good way to implement this effectively so I ended up with a simple select to list all rows from table A, and an ugly resource_id IN (Select..) and voucher_no NOT IN (select...). Totally I get 4 table scans.

The SQL is like this now, and I hope someone has a smart way to do the same thing, but more effectively.

SELECT resource_id,
amount,
voucher_no,
status,
'A' AS fromtable
FROM tableA
WHERE status IN ('N','T')

UNION

SELECT resource_id,
amount,
voucher_no,
status,
'B' AS fromtable
FROM tableB
WHERE resource_id IN (SELECT resource_id
FROM tableA
WHERE status IN ('N','T'))
AND status = 'N'
AND voucher_no NOT IN (SELECT voucher_no
FROM tableA
WHERE status IN ('N','T'))