November 10, 2016 at 8:27 am
I am trying to show where I am having the issue, I need to do a join on the "temptable" table to get the value of "book_number", if it matches in the last SELECT it will be excluded from the data results. I just cant think of any other way to do this.
Here is the SQL I am tying to use:
with temptable ( account_number, year, user_id, date, user_name, zip, book_number, error_code)
as
(
select accnumb01
,year01
,userid01
,date01
,username01
,zip01
,booknumber05
,errorcode01
from table_accounts
join table_users
on accnumb01 = accnumb03
and zip01 = zip05
and year01 = year03
where userid01 = '12345' and (errorcode01 = '1')
AND ((('2016-01-01' <= date01)
AND (date01 < '2016-02-01')) OR (('2017-01-01' <= date01)
AND (date01 < '2017-02-01')))
and ref05 = 1
)
-- at this point I have all the data I need,
-- now I have to check if there is any account number with the same book number, if here is a match, exclude it from the results.
select account_number, year, user_id, date, user_name, zip, book_number, error_code
from temptable
where
-- In here I am trying to join with "temptable" to get the value of temptable.book_number that I need from the temptable table, try to find any account number that matches, using he SELECT below,
-- thats where I am having the problem. I don't know why I can not join the "table_users" with" temptable" to do the match on
-- "table_users.booknumber05 = temptable.book_number".
account_number NOT IN ( SELECT table_users.accnumb03
FROM table_users, temptable
WHERE table_users.booknumber05 = temptable.book_number AND table_users.zip05 = '1'
AND table_users.year03 > '2015' AND table_users.userid05 = '12345')
Any help is very appreciated, thanks for looking!
November 10, 2016 at 8:41 am
a_car11 (11/10/2016)
I am trying to show where I am having the issue, I need to do a join on the "temptable" table to get the value of "book_number", if it matches in the last SELECT it will be excluded from the data results. I just cant think of any other way to do this.Here is the SQL I am tying to use:
with temptable ( account_number, year, user_id, date, user_name, zip, book_number, error_code)
as
(
select accnumb01
,year01
,userid01
,date01
,username01
,zip01
,booknumber05
,errorcode01
from table_accounts
join table_users
on accnumb01 = accnumb03
and zip01 = zip05
and year01 = year03
where userid01 = '12345' and (errorcode01 = '1')
AND ((('2016-01-01' <= date01)
AND (date01 < '2016-02-01')) OR (('2017-01-01' <= date01)
AND (date01 < '2017-02-01')))
and ref05 = 1
)
-- at this point I have all the data I need,
-- now I have to check if there is any account number with the same book number, if here is a match, exclude it from the results.
select account_number, year, user_id, date, user_name, zip, book_number, error_code
from temptable
where
-- In here I am trying to join with "temptable" to get the value of temptable.book_number that I need from the temptable table, try to find any account number that matches, using he SELECT below,
-- thats where I am having the problem. I don't know why I can not join the "table_users" with" temptable" to do the match on
-- "table_users.booknumber05 = temptable.book_number".
account_number NOT IN ( SELECT table_users.accnumb03
FROM table_users, temptable
WHERE table_users.booknumber05 = temptable.book_number AND table_users.zip05 = '1'
AND table_users.year03 > '2015' AND table_users.userid05 = '12345')
Any help is very appreciated, thanks for looking!
I'm not sure what do you mean by not being able to do the join, but you might not need it. You might only need a correlated subquery.
with temptable ( account_number, year, user_id, date, user_name, zip, book_number, error_code)
as
(
SELECT accnumb01
,year01
,userid01
,date01
,username01
,zip01
,booknumber05
,errorcode01
FROM table_accounts
JOIN table_users ON accnumb01 = accnumb03
AND zip01 = zip05
AND year01 = year03
WHERE userid01 = '12345'
AND errorcode01 = '1'
AND ( ('2016-01-01' <= date01 AND date01 < '2016-02-01')
OR ('2017-01-01' <= date01 AND date01 < '2017-02-01'))
and ref05 = 1
)
SELECT account_number
,year
,user_id
,date
,user_name
,zip
,book_number
,error_code
FROM temptable tt
WHERE account_number NOT IN ( SELECT tu.accnumb03
FROM table_users tu
WHERE tu.booknumber05 = tt.book_number
AND tu.zip05 = '1'
AND tu.year03 > '2015'
AND tu.userid05 = '12345');
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply