select distinct help
select distinct ISNULL (a.account,'') as "Account", ISNULL (c.address1,'') as "Address",
ISNULL (c.city,'') as "City", ISNULL (c.state,'') as "State",
ISNULL (c.postalcode,'') as "Zip Code", ISNULL (a.mainphone,'') as "Phone",
a.userfield1 as "GID", s.division
from sysdba.account as a
join sysdba.address as c on a.addressid = c.addressid
join sysdba.staff as s on a.accountid = s.accountid
where a.type like '%client%' and a.userfield1 is not null and (s.division like '%HR%' or s.division like '%db%') and s.type = 'client'
So what happens now is that if an account is listed in two division I get two distinct rows returned, but each with the same GID column. When I try to push this to a new database that has GID as the primary key I get duplicate on that column and it errors out.
I need to be able to get only a single row if the division is both HR and db. how to tackle this problem.
Thanks!
You need to handle the Division field differently, as that is the cause of the problem. So the first question is "What do you want the division to be shown as if the account appears in two divisions?".
If you can hardcode the divison in all cases, then replace the division with this hard coded value and the Distinct will take care of the rest.
A more complete answer is to separate the two cases and combine them with an Outer Join, then decode the Division based on the information of which divisions it exists for.
Select Coalesce(t1.account, t2.account, '') as "Account", ISNULL (c.address1,'') as "Address",
ISNULL (c.city,'') as "City", ISNULL (c.state,'') as "State",
ISNULL (c.postalcode,'') as "Zip Code", ISNULL (a.mainphone,'') as "Phone",
a.userfield1 as "GID",
Case when t1.division is null then t2.division
when t2.division is null then t1.division
else 'TwoDivisions'
end as division
From (
Select a1.accountid,
a1.account,
a1.userfield1,
s1.division
From account a1
Join staff s1
On a.accountid = s.accountid
Where a1.type like '%client%'
And s1.division like '%HR%'
And s1.type = 'client' ) t1
Full Outer Join
Select a2.accountid,
a2.account,
a2.userfield1,
s2.division
From account a2
Join staff s2
On a2.accountid = s2.accountid
Where a2.type like '%client%'
And s2.division like '%db%'
And s2.type = 'client' ) t2
on t1.accountid = t2.accountid
Join address c On c.accountid = Coalesce( t1.accountid, t2.accountid )
Join
