help please with table links

[Former Member]

I need to extract the name of the person who is responsible for a task, using these tables, this is only a summarised version.

Task

Task	CustomerNum	RoleNum
1	500	3
2	0	2000
3	0	2005
4	600	3

customer

CustomerNum	Name
500	James Smith
600	John O’Brien
1000	Sam Peters
200	Donna Murphy
700	Peter Burns

Role 

RoleNum	RoleName	CustomerNum
3	Staff	500
2000	consultants	1000
3	Staff	600
2005	representatives	200
2005	representatives	500
3	Staff	700

So the report I need to produce looks something link this

James Smith Number of tasks to complete:  2

Task 1

Task 3

John O'Brien Number of tasks to complete:  1

Task 4

I having problems extracting the person's name who is responsible for the task.

The problem I have is if the role number is 3 then I only need to link

Task.Customer to Customer.CustomerNum

to get the Name

But if the role number is greater than or equal to 2000 I need to link

Task.RoleNum to Role.RoleNum

Role.CustomerNum to Customer.CustomerNum

to get the name

I don't know how to do this.  Can someone please help me?

thank you

[nscheaffer · Accepted solution]

I'll give it a try.  Basically you need to have two different SQL statement with different join paths depending on the value of Task.RoleNum and then union the results together.  Like this...

SELECT Customer.Name, Task.Task

FROM Task

INNER JOIN Customer ON Task.CustomerNum = Customer.CustomerNum

WHERE Task.RoleNum < 2000

UNION

SELECT Customer.Name, Task.Task

FROM Task

INNER JOIN Role ON Task.RoleNum = Role.RoleNum

INNER JOIN Customer ON Role.CustomerNum = Customer.CustomerNum

WHERE Task.RoleNum >= 2000

Click on this link to run this SQL in SQL Fiddle.

You would need to put this SQL in a Command Object.  Here is a video on how to do that.

Enjoy,

Noel

[Former Member · Accepted solution]

Thank you Noel for your reply, I didn't know about the Command object.  I really appreciate it, you solved my problem.  I have learnt alot.

[nscheaffer · Accepted solution]

It looks like I forgot to include the link to the video I referenced on how to create and use a Command Object.  Sounds like you figured it out.  Here is the link anyway...

Crystal Reports Tutorial Creating SQL Commands Business Objects Training Lesson 17.2 - YouTube

Glad this helped you out.  Can you mark my answer as "Correct"?

Thanks,

Noel