SQL code to check if a record exist in a table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL code to check if a record exist in a table

Author

Topic

Billmiles77
Starting Member

7 Posts

Posted - 2012-10-19 : 16:05:05

Hello and thanks in advance. I have submitted this questions earlier however I think I should define the objective a little more.
I am running mining SQL queries using Toad with great limitations. My rights to the source database are query only, no temporary table rights or update rights. I have a list of accounts, roughly 6000, that have been provided for my query into a particuliar table. What I typically do is create an expression list containing the accounts using 'WHERE IN' list command to return additional data regarding the accounts. Sometimes during the query an account is not found in the table which then leaves me with the physical reconcilliation of the records founds against the expression list. Is there a way to do identify records not found programmatically during the query execution? So I would like a return set containing all accounts in the expression but if not found flag that account. Apologizes if this is confusing..I am a newbie to SQL coming from a COBOL mainframe background.

SELECT t.account_no
FROM master_table t
WHERE (t.account_no IN ( 01, 02, 03, 04, 05, 06));

chadmat
The Chadinator

1974 Posts

Posted - 2012-10-19 : 16:53:29

Do an outer join between your table and the master_table.

-Chad

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-10-19 : 16:57:56

SELECT a.account_no
FROM (
select 01 account_no union all
select 02 union all
select 03 union all
select 04 union all
select 05 union all
select 06
) a
left outer join
master_table t
on a.account_no = t.account
WHERE (t.account_no IN ( 01, 02, 03, 04, 05, 06))
GROUP BY a.account_no
HAVING count(*) = 0;

=================================================
We are far more concerned about the desecration of the flag than we are about the desecration of our land. -Wendell Berry

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources