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?
 
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
 
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 solution worked perfectly. Auto updates as well.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.