Use one column to determine which other column to read...
Posted by: Mark Marshall
Date: March 12, 2006 03:03PM

I am working on a service industry database. In my database, there is a table that has an effective date, and six levels of service and their going rates. Like this:

serviceLevelRate
----------
id
level1
level2
level3
level4
level5
level6
effectiveDate


Then in another table, I have what basically amounts to a service contract. It looks like this:

contract
----------
id
person (points to person.id)
serviceLevel (a number from 1-6)
effectiveDate

Is it possible to join these tables in a query in such a way that I can pull out the proper level? I started down this path, and now I'm not sure if this is the way I want to go or not. The tables are linked through other fields using left joins.

I guess what I'm asking is, is there a way in MySQL to do something like this:

SELECT R.(CONCAT("level", C.level)) RATE
FROM contract C LEFT JOIN
rate R ON .... blah blah blah

Thanks.
Mark

Options: ReplyQuote


Subject
Written By
Posted
Use one column to determine which other column to read...
March 12, 2006 03:03PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.