Crystal Reports Left Outer Join

Technical Questions
Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions

Topic: Left Outer Join
	Page of 5 Next >>

Author	Message
LShadrin Newbie Joined: 20 Jul 2010 Location: United States Online Status: Offline Posts: 24	Topic: Left Outer Join Posted: 20 Jul 2010 at 1:41pm
	I am new to Crystal Reports and I am building a report where I want to display all the values that are assigned to a person. I have Table 1 = Lead Status and Table 2 = Assigned to. I have a left outer join going from table 1 to table 2. What am I doing wrong?
	Luka
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 20 Jul 2010 at 2:27pm
	what are you joining on? what is in each table?
	IP Logged

LShadrin Newbie Joined: 20 Jul 2010 Location: United States Online Status: Offline Posts: 24	Posted: 20 Jul 2010 at 3:31pm
	There are 11 fields in Lead Status for each Assigned To person and if they did not select the other 10 fields, I want to be able to see them. The reason I have AMGR Client in there is because I want to be able to pull data only for a certain day and I was going to do a selection expert to retreive that data. Here is my SQL code: SELECT "AMGR_Client"."Last_Modify_Date", "A_Assigned_To"."A_Assigned_To", "A_Lead_status"."A_Lead_status" FROM ("CandidateLeads"."dbo"."A_Assigned_To" "A_Assigned_To" LEFT OUTER JOIN "CandidateLeads"."dbo"."A_Lead_status" "A_Lead_status" ON ("A_Assigned_To"."Client_Id"="A_Lead_status"."Client_Id") AND ("A_Assigned_To"."Contact_Number"="A_Lead_status"."Contact_Number")) LEFT OUTER JOIN "CandidateLeads"."dbo"."AMGR_Client" "AMGR_Client" ON ("A_Assigned_To"."Client_Id"="AMGR_Client"."Client_Id") AND ("A_Assigned_To"."Contact_Number"="AMGR_Client"."Contact_Number") ORDER BY "A_Assigned_To"."A_Assigned_To", "A_Lead_status"."A_Lead_status" Am I way off from what I am trying to accomplish?
	Luka
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 20 Jul 2010 at 3:47pm
	sorry not sure if you are on the correct track or not. I am not familiar with the tables and am not quite 'seeing' your data here. I need to know what is stored in each table independently and then what you want to see at in the report to see if a join makes sense. Can you post a few sample rows per table and then what you expect to see in your report from those tables. Also be careful of outer joins and then using select statements in the report. Often a select statement will turn an outer join back into an inner join.
	IP Logged

LShadrin Newbie Joined: 20 Jul 2010 Location: United States Online Status: Offline Posts: 24	Posted: 21 Jul 2010 at 4:15am
	In the Assigned_To table, I have names stored there. There are 20 names in there. In the A_Lead_Stuats, I have the status listed in there. There is: Contact Attempted (Left Message or Email) Contact Made (Phone or Face-to-Face) Contract Pending Contracted Already Contracted Initial (New/Unworked Lead) Interview Scheduled (Day/Time) Materials Provided [Email/Interview Packet] No Show For Interview Not Interested Not Interested/ Recontact Not Qualified In each AMRGR_Client record the "Assigned_To" person goes into and if they select "Contract Pending" I want Contract Pending to show up as 1, then in the next client record, if they select "Contracted", a 1 to apear aftter that one, etc. John Doe Contact Attempted (Left Message or Email) 0 Contact Made (Phone or Face-to-Face) 0 Contract Pending 1 Contracted 1 Already Contracted 0 Initial (New/Unworked Lead) 0 Interview Scheduled (Day/Time) 0 Materials Provided [Email/Interview Packet] 0 No Show For Interview 0 Not Interested 0 Not Interested/ Recontact 0 Not Qualified 0
	Luka
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 21 Jul 2010 at 5:28am
	So all your incidents of contact for a client (from an 'employee') is in the AMGR_Client table. All of your types of contact are in the a lead status all of your 'employees' that make contact are in the assigned to table. Is that all correct? 1. Is the AMGR_client table one row per contact with a code that matchs to the lead status table or can you have more than one contact type in one row of this data? 2. In your sample is 'John Doe' an 'emplyee' from 'assigned to' or a 'customer' from AMGR?
	IP Logged

LShadrin Newbie Joined: 20 Jul 2010 Location: United States Online Status: Offline Posts: 24	Posted: 21 Jul 2010 at 5:32am
	1. I am not really understanding your question but the AMGR_Client has all the contact information for the customer and only one employee (Assigned_To) is assigned to that customer. 2. John Doe is the employee and is the person in the Assigned_To field
	Luka
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 21 Jul 2010 at 5:36am
	Does AMGR_Client have somehting like one column called 'COntact_type" with a code in it that matches the codes from A lead status?
	IP Logged

LShadrin Newbie Joined: 20 Jul 2010 Location: United States Online Status: Offline Posts: 24	Posted: 21 Jul 2010 at 5:40am
	The Client_ID and the Client_Number are the ones that are the same in both tables.
	Luka
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 21 Jul 2010 at 6:19am
	Sorry for all the questions but I just can't seem to get a handle on your structure. Can you post sample rows from AMGR Client?
	IP Logged

Page of 5 Next >>

Post Reply

Post New Topic

Printable version

Printable version

Forum Jump

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

This page was generated in 0.031 seconds.