asked on

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

desiredforsome

ASKER

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

lcohan

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

desiredforsome

ASKER

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.

ASKER CERTIFIED SOLUTION

desiredforsome

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

desiredforsome

ASKER

This solution worked perfectly. Auto updates as well.