SQL Query issue

hey All,

I have a sql database I wrote that has a few tables in it.

First table contains the assigned companies

It has two columns 1 is the username and 1 is the company both are nvarchar(100)

The second table is called assignloans and it contains the usernames with the loan numbers assigned to them.

The third table contains all the loan information I need.

So here is my issue. I am trying to create it so that if I have two usernames both assigned to the same company that when my query runs it puts in the loan numbers and the usernames into the assignloans table.

It is working half way where it is only inserting 1 of the usernames. I know its going ot be something simple but am just beating my head on the wall now.

if not exists(Select  d.xrefid,d.company,e.userid from outlookreport.dbo.loanselect d,outlookreport.dbo.assignloans e ,outlookreport.dbo.userassign f where e.xrefid=d.xrefid and f.username = e.userid)
 insert  into outlookreport.dbo.assignloans(xrefid,userid)
select a.xrefid, b.username
from outlookreport.dbo.loanselect a, outlookreport.dbo.userassign b, outlookreport.dbo.assignloans c
where  a.company=b.company and b.username <> c.userid

Open in new window

desiredforsomeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

desiredforsomeAuthor Commented:
This is an update on the code that is only inserting on one username and not both.

if not exists(Select  d.xrefid,d.company from outlookreport.dbo.loanselect d,outlookreport.dbo.assignloans e  where e.xrefid=d.xrefid )
 insert  into outlookreport.dbo.assignloans(xrefid,userid)
select a.xrefid, b.username
from outlookreport.dbo.loanselect a, outlookreport.dbo.userassign b
where  a.company = b.company 

Open in new window

0
lcohanDatabase AnalystCommented:
Is most likely because the SELECT from the INSERT has different WHERE clause that the NOT EXISTS...I mean the "missing" username is filtered by the:

[...]
where  a.company=b.company and b.username <> c.userid
0
desiredforsomeAuthor Commented:
I posted up my updated code after figuring that part out however it is still not owrking properly.

I think what I need it to do is to loop through each record comparing. I am not sure how this syntax and such would go.
0
desiredforsomeAuthor Commented:
This seems to work below

 while not exists(Select  d.xrefid from outlookreport.dbo.loanselect d,outlookreport.dbo.assignloans e  where e.xrefid=d.xrefid )
 begin
 insert  into outlookreport.dbo.assignloans(xrefid,userid)
select a.xrefid, b.username
from outlookreport.dbo.loanselect a, outlookreport.dbo.userassign b
where  a.company = b.company 
end

Open in new window

However this is just inserting. Is ther ea simple way to have this update if something changes? Such as the username is removed from the company. Or woudl that need to be an entire different query?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
desiredforsomeAuthor Commented:
This solution worked perfectly. Auto updates as well.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.