October 10, 2013 at 3:42 am
Hi
I have this data first, before I explain my query and my issue:
create table staging(customer_id int not null unique, customer_name varchar(20), customer_lname varchar(20), [status] int)
insert into staging(customer_id, customer_name, customer_lname, [status])
values(1, 'James', 'Brown', 1), (2, 'Thabo', 'Kgosi', 1), (3, 'Horse', 'King', 0), (4, 'Tom', 'Smith', 1)
create table final(customer_id int not null unique, customer_name varchar(20), customer_lname varchar(20))
insert into final(customer_id, customer_name, customer_lname)
values(1, 'James', 'Brown'), (2, 'Thabo', 'Kgosi'), (3, 'Horse', 'King'), (4, 'Tom', 'Smith')
create table error(customer_id int not null unique,customer_lname varchar(20))
Let me explain my data first, I have Staging table, all the records gets validated and get signed a [status]. if a record fail verification it gets [status] = 1, otherwise it get passed staright to Final table.
Records in Staging will be validated again if they pass they go through Final table.
In my query below, I want to check data in Staging with status = 1, then check if that record is also there in Final, which it would mean it once failed verification. If I find that record I write it to Error table. I want to end up with all the records that once failed Validation.
insert into error
select fn.customer_id, fn.customer_name
from final fn
left join staging st
on fn.customer_id = st.customer_id
where in (select * from staging stg where stg.[status] = 1 and fn.customer_id = stg.customer_id)
I'm struggling with a concerpt but I think it should be along the code I wrote, please help.
October 10, 2013 at 3:49 am
I think I found the answer, it should be
insert into error
select fn.customer_id, fn.customer_name
from final fn
left join staging st
on fn.customer_id = st.customer_id
where exists (select * from staging stg where stg.[status] = 1 and fn.customer_id = stg.customer_id)
Correct me where you think could do better
October 10, 2013 at 8:10 am
I might be missing something but it seems like you're over complicating things.
Your query with an OUTER JOIN and an EXISTS returns the same results set as an INNER JOIN would:
SELECT fn.customer_id
, fn.customer_name
FROM final fn
LEFT JOIN staging st ON fn.customer_id = st.customer_id
WHERE EXISTS ( SELECT *
FROM staging stg
WHERE stg.[status] = 1
AND fn.customer_id = stg.customer_id )
SELECT fn.customer_id
, fn.customer_name
FROM final fn
INNER JOIN staging st ON fn.customer_id = st.customer_id
WHERE st.[status] = 1
_____________________________________________________________________
- Nate
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply