We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

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

Medium Priority
182 Views
Last Modified: 2012-05-06
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

Comment
Watch Question

Without digging in too deeply ...

Have you tried putting DISTINCT right after the SELECT keyword (line 1)?

Author

Commented:
No I hadn't.  I gave it a try but it didn't help.

Commented:
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

Author

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.

Commented:
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

Commented:
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

Author

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'.

Author

Commented:
oops.  Forget that error.  Both queries return the same result.

Author

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.
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Commented:
If not can you post the table structure for cstd_contact_credit_union

Author

Commented:
Thank you for all your help.

Linda
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.