Dynamic query

[gelen4o]

Hi All,

SQL is something very new to me and I'm struggling with the following case:

There are two tables:

- "Account" (containing information for account numbers and their respective owners - "Entity")
- "Agreements" (containing information for agreements an account or entity may have) -- account or entity name also present in this table.
- One account or entity may have several agreements and one entity may own several accounts.

I have no problem running separate queries using either "Account Number" or "Entity" as KEY to extract all relevant agreements, however I would like to combine the two into just one query.

My goal is to tell SQL:

if "Account Number" not in "Agreements" then

substitute "Account Number" with "Entity" (owner)

Else:

populate agreements for "Account Number"


Thanks in advance !

 

[xenou]

This basically looks like two queries to me:

First, all records in Account where there is a match record in Agreements:

select t1.* 
from 
	Account t1
	inner join Agreements t2
	on t1.[Account Number] = t2.[Account Number]

Second, all records in Accounts where there is not a matching record (account number) in Agreements:

select t1.* 
from 
	Account t1
where
	t1.[Account Number] not in (select t3.[AccountNumber] from Agreements t3)

You can combine them by using UNION and thus make one query out of two:

select t1.* 
from 
	Account t1
	inner join Agreements t2
	on t1.[Account Number] = t2.[Account Number]

union all

select t1.* 
from 
	Account t1
where
	t1.[Account Number] not in (select t3.[AccountNumber] from Agreements t3)

This is untested because there is not much to test on. I would suggest some sample data if you want more explicit instructions.