• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 166
  • Last Modified:

Help with outer join /need to only return "primary" value

I'm a SQL newbie and am trying to change a select statement that was created by another person.

The following code works but returns duplicate activity records if there are multiple matches on cstd_contact_credit_union.company_id
I only want to return a record where the cstd_contact_credit_union.primary_flag ='Y' or there is no matching cstd_contact_credit_union.company_id

Thank you.


select oncd_activity.activity_id,oncd_company.company_id  hftohbmhma,
 oncd_company.company_name_1 tvtrqpheus,  
oncd_activity.activity_id zqdnixavvh, 
 oncd_activity.due_date nztjanesyf,     
oncd_activity.description xyxehnffxg,   
oncd_activity.activity_id, 
oncd_contact.contact_id vcpewprrmf, 
cstd_contact_credit_union.contact_id wdoasjxpfp, 
oncd_company_territory.territory_code bkfutmzpfp,
rtrim(oncd_contact.first_name) <$DB_PLUS> '  ' <$DB_PLUS> rtrim(oncd_contact.last_name) eopvfqykws, 
oncd_activity.action_code jfmmnxiixi,
onca_action.action_type_code cxacivbbxh
  from   oncd_activity   
 
Left outer join oncd_activity_contact on (oncd_activity.activity_id = oncd_activity_contact.activity_id  
and  oncd_activity_contact.primary_flag ='Y' )   
 Left outer join	oncd_contact on (oncd_contact.contact_id = oncd_activity_contact.contact_id)
 Left outer join	cstd_contact_credit_union on (cstd_contact_credit_union.contact_id = oncd_activity_contact.contact_id)
 
 
 
Left outer join	oncd_company on (oncd_company.company_id = cstd_contact_credit_union.company_id and 
     cstd_contact_credit_union.primary_flag ='Y')
 
Left outer join	oncd_company_territory on (oncd_company_territory.company_id = cstd_contact_credit_union.company_id) and  
     cstd_contact_credit_union.primary_flag ='Y'     
 
 left outer join oncd_activity_user on (oncd_activity.activity_id = oncd_activity_user.activity_id)  
left outer join onca_action on (oncd_activity.action_code  = onca_action.action_code)
where          9=9 and  (oncd_activity.result_code='' or oncd_activity.result_code is null)
 order by oncd_activity.due_date desc

Open in new window

0
Buffyski
Asked:
Buffyski
  • 6
  • 5
1 Solution
 
Daniel WilsonCommented:
Without digging in too deeply ...

Have you tried putting DISTINCT right after the SELECT keyword (line 1)?
0
 
BuffyskiAuthor Commented:
No I hadn't.  I gave it a try but it didn't help.
0
 
ralmadaCommented:
what about something like this? Try to use Alias it makes your query easier to follow
select 
	a.activity_id,
	e.company_id  hftohbmhma,
 	e.company_name_1 tvtrqpheus,  
	a.activity_id zqdnixavvh, 
 	a.due_date nztjanesyf,     
	a.description xyxehnffxg,   
	a.activity_id, 
	c.contact_id vcpewprrmf, 
	d.contact_id wdoasjxpfp, 
	f.territory_code bkfutmzpfp,
	rtrim(c.first_name)  +',  ' + rtrim(c.last_name) eopvfqykws, 
	a.action_code jfmmnxiixi,
	onca_action.action_type_code cxacivbbxh
 
from   oncd_activity a   
 
 Left outer join 	oncd_activity_contact b on (a.activity_id = 	b.activity_id  and  b.primary_flag ='Y' )   
 Left outer join	oncd_contact c on (c.contact_id = b.contact_id)
 Left outer join	(select distinct contact_id, company_id, primary_flag from cstd_contact_credit_union) d on (d.contact_id = b.contact_id)
 Left outer join	oncd_company e on (e.company_id = cstd_contact_credit_union.company_id and d.primary_flag ='Y')
 Left outer join	oncd_company_territory f on (f.company_id = d.company_id) and d.primary_flag ='Y'     
 left outer join 	oncd_activity_user on (a.activity_id = oncd_activity_user.activity_id)  
 left outer join 	onca_action on (a.action_code  = onca_action.action_code)
 
where   (a.result_code='' or a.result_code is null)
and (d.company_id is null)
order by a.due_date desc

Open in new window

0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
BuffyskiAuthor Commented:
Ralmada,
without really studying it and just pasting it into my app here is the error message I am getting

SqlException: The multi-part identifier "cstd_contact_credit_union.company_id" could not be bound.
The multi-part identifier "oncd_activity.due_date" could not be bound.
0
 
ralmadaCommented:
I see, that's because your running the query from the application and it's using the old names. Ok so try like this then

select distinct oncd_activity.activity_id,oncd_company.company_id  hftohbmhma,
 oncd_company.company_name_1 tvtrqpheus,  
oncd_activity.activity_id zqdnixavvh, 
 oncd_activity.due_date nztjanesyf,     
oncd_activity.description xyxehnffxg,   
oncd_activity.activity_id, 
oncd_contact.contact_id vcpewprrmf, 
cstd_contact_credit_union.contact_id wdoasjxpfp, 
oncd_company_territory.territory_code bkfutmzpfp,
rtrim(oncd_contact.first_name) <$DB_PLUS> '  ' <$DB_PLUS> rtrim(oncd_contact.last_name) eopvfqykws, 
oncd_activity.action_code jfmmnxiixi,
onca_action.action_type_code cxacivbbxh
  from   oncd_activity   
 
Left outer join oncd_activity_contact on (oncd_activity.activity_id = oncd_activity_contact.activity_id  
and  oncd_activity_contact.primary_flag ='Y' )   
 Left outer join	oncd_contact on (oncd_contact.contact_id = oncd_activity_contact.contact_id)
 Left outer join	cstd_contact_credit_union on (cstd_contact_credit_union.contact_id = oncd_activity_contact.contact_id)
 
 
 
Left outer join	oncd_company on (oncd_company.company_id = cstd_contact_credit_union.company_id and 
     cstd_contact_credit_union.primary_flag ='Y')
 
Left outer join	oncd_company_territory on (oncd_company_territory.company_id = cstd_contact_credit_union.company_id) and  
     cstd_contact_credit_union.primary_flag ='Y'     
 
 left outer join oncd_activity_user on (oncd_activity.activity_id = oncd_activity_user.activity_id)  
left outer join onca_action on (oncd_activity.action_code  = onca_action.action_code)
where          (oncd_activity.result_code='' or oncd_activity.result_code is null)
and cstd_contact_credit_union.company_id is null
 order by oncd_activity.due_date desc
 

Open in new window

0
 
ralmadaCommented:
Or this one

select oncd_activity.activity_id,oncd_company.company_id  hftohbmhma,
 oncd_company.company_name_1 tvtrqpheus,  
oncd_activity.activity_id zqdnixavvh, 
 oncd_activity.due_date nztjanesyf,     
oncd_activity.description xyxehnffxg,   
oncd_activity.activity_id, 
oncd_contact.contact_id vcpewprrmf, 
cstd_contact_credit_union.contact_id wdoasjxpfp, 
oncd_company_territory.territory_code bkfutmzpfp,
rtrim(oncd_contact.first_name) <$DB_PLUS> '  ' <$DB_PLUS> rtrim(oncd_contact.last_name) eopvfqykws, 
oncd_activity.action_code jfmmnxiixi,
onca_action.action_type_code cxacivbbxh
  from   oncd_activity   
 
Left outer join oncd_activity_contact on (oncd_activity.activity_id = oncd_activity_contact.activity_id  
and  oncd_activity_contact.primary_flag ='Y' )   
 Left outer join	oncd_contact on (oncd_contact.contact_id = oncd_activity_contact.contact_id)
 Left outer join	(select distinct contact_id, company_id, primary_flag from cstd_contact_credit_union) cstd_contact_credit_union on (cstd_contact_credit_union.contact_id = oncd_activity_contact.contact_id and cstd_contact_credit_union.primary_flag ='Y' )
  
Left outer join	oncd_company on (oncd_company.company_id = cstd_contact_credit_union.company_id and 
     cstd_contact_credit_union.primary_flag ='Y')
 
Left outer join	oncd_company_territory on (oncd_company_territory.company_id = cstd_contact_credit_union.company_id) and  
     cstd_contact_credit_union.primary_flag ='Y'     
 
 left outer join oncd_activity_user on (oncd_activity.activity_id = oncd_activity_user.activity_id)  
left outer join onca_action on (oncd_activity.action_code  = onca_action.action_code)
where          (oncd_activity.result_code='' or oncd_activity.result_code is null)
and cstd_contact_credit_union.company_id is null
 order by oncd_activity.due_date desc

Open in new window

0
 
BuffyskiAuthor Commented:
The first query only returned 3 records instead of the 161 I am expecting.  They were the records with no contact_id

The second statement returned this error.
SqlException: Incorrect syntax near the keyword 'in'.
0
 
BuffyskiAuthor Commented:
oops.  Forget that error.  Both queries return the same result.
0
 
BuffyskiAuthor Commented:
In further examination one of the the activities does have a contact_ID assigned , but the contact full name and all other info is now displaying blank information.
0
 
ralmadaCommented:
What about this one
select 
	oncd_activity.activity_id,
	oncd_company.company_id  hftohbmhma,
 	oncd_company.company_name_1 tvtrqpheus,  
	oncd_activity.activity_id zqdnixavvh, 
 	oncd_activity.due_date nztjanesyf,     
	oncd_activity.description xyxehnffxg,   
	oncd_activity.activity_id, 
	oncd_contact.contact_id vcpewprrmf, 
	cstd_contact_credit_union.contact_id wdoasjxpfp, 
	oncd_company_territory.territory_code bkfutmzpfp,
	rtrim(oncd_contact.first_name) + ',  ' + rtrim(oncd_contact.last_name) eopvfqykws, 
	oncd_activity.action_code jfmmnxiixi,
	onca_action.action_type_code cxacivbbxh
from   oncd_activity   
 
Left outer join oncd_activity_contact on (oncd_activity.activity_id = oncd_activity_contact.activity_id)
Left outer join	oncd_contact on (oncd_contact.contact_id = oncd_activity_contact.contact_id)
Left outer join	(select distinct contact_id, company_id, primary_flag from cstd_contact_credit_union) cstd_contact_credit_union on (cstd_contact_credit_union.contact_id = oncd_activity_contact.contact_id)
Left outer join	oncd_company on (oncd_company.company_id = cstd_contact_credit_union.company_id)
Left outer join	oncd_company_territory on (oncd_company_territory.company_id = cstd_contact_credit_union.company_id)
left outer join oncd_activity_user on (oncd_activity.activity_id = oncd_activity_user.activity_id)  
left outer join onca_action on (oncd_activity.action_code  = onca_action.action_code)
 
where (oncd_activity.result_code='' or oncd_activity.result_code is null)
and  oncd_activity_contact.primary_flag ='Y'
and cstd_contact_credit_union.primary_flag ='Y'  
order by oncd_activity.due_date desc 

Open in new window

0
 
ralmadaCommented:
If not can you post the table structure for cstd_contact_credit_union
0
 
BuffyskiAuthor Commented:
Thank you for all your help.

Linda
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now