-
Help with a heavy SQL with 4 table scans
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'))
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|