A little help

[PedroMP80]

I'm going to describe the steps that I have to take to get the result but I can't make it into one sql statement.

We have three entities envolved: Store, Peter(1st Client), Jon(2nd Client) and Susan(3rd Client)

Peter buys a license in the store and its given the prefix(Basic01) and his information is stored in the table Client.

Then Peter sells one license to Jon and then Jon gets the prefix(Home01) and his information is also stored in the table Client. In the data stored has the information of Peter but with reference in another table,Salesman, with the id of 01.

Now Jon sells a product to susan wich envolves a contract, and Susan's information is stored in the table client with the prefix (PF01).

with the contract I can get the information of the code of Peter in the table Salesman but I want the code of Peter in the table Client.

Note: in the data of Peter in the table client it has the reference to the salesman.id of Peter.


Select Client.Salesman
From Client
Where Client.id = Contract.Vendor

Result: 01

Select Name
From Client
Where salesman=01 and name='Basic%'

Result: Peter (???)

I know this is confusing that is why I'm asking the experts!!

If anyone could help I'm stuck with this for 2 days.

[rmiao]

Can you post schema and sample data of client table?

[PedroMP80]

I'm going to put here the short version of the tables

Client Table

ID--------Name---Salesman
Basic01---Peter----01
Basic02---Sara-----02
Home01---Jon------01
Home02---Tim------02
PF01------Susan---03

Salesman Table

ID---Name
01---Peter
02---Sara
03---Jon

Contract Table

ID---Client--Salesman
01---PF01---03


Of course there other fields in that tables but those are the most important.

Well with the contrat 01, I want to retrieve the name Peter for calculate his commissions.

[rmiao]

Are those id columns in salesman and contract table related? Doesn't look like. Any relationship among those tables?

[PedroMP80]

Client.ID -> Contract.Client

Salesman.ID->Client.Salesman

Salesman.ID->Contract.Salesman

I think these are the relationships that crystal assumes.

The ID in Contract and Salesman aren't related