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.