Possible to get it in one query ?

[rookiecoder]

Hi,
I have the following tables structure
employee

id (Primary)
contract_id
location_id
rank (enum field)


contract

id (Primary)
location_id
rank (enum field...same as that of employee)


rank_name

name (corresponding name to each rank)
rank (enum field...same as that of employee)


location

id (Primary)
location_name



So now I want to retrieve the following details of an employee given his id
- rank_name.name from the employee.rank as well as contract.rank
- location.location_name from the employee.location_id as well as contract.location.id

i.e I need the four above mentioned fields
How will I go about doing this in one select query? Or is it not possible?

[bazz]

I'm tiddly right now but surely you can get rid of the rank_name table by putting the employee name into the table 'employee'?

I'll respond more fully tomorrow afternoon.

bazz

[rookiecoder]

Yeah , the rank_name table does look unneccessary however it is something thats been around for a while in the system ( will need to check at a lot of places to see why it exists)

However, the issue with location table will remain

[bazz]

I would have the tables like this


employee
* id (Primary)
* contract_id
* location_id
* rank_id

contract
* id (Primary)
* location_id

rank
* id (PK)
* rank_data

location
* id (Primary)
* location_name


Does that show all the relationships you need?

bazz

[rookiecoder]

Hi,
The rank table consists of two fields, a enum field and a varchar field.

The enum field has the values in same order as those mentioned in contract and employee tables' rank fields

[oesxyl]

I don't understand how the database is build, what relation do you have between tables.
I guess will be something like this:

select rank_name.name, employee.rank, contract.rank, location.location_name
from rank_name, employee, contract, location
where employee.location_id = location.id and employee.contract_id = contract.id
and rank_name.rank = employee.rank


best regards