MySQL Forums
Forum List  »  General

Linking to a LUT more than once
Posted by: Philip Jones
Date: August 05, 2011 07:33AM

Is it possible to link to the same lookup table more than once in the same query? Inutitively, I feel this should be very easy but I've been going around in circles for a few hours. My head is starting to spin and I'm no nearer a realistic solution. I have come up with a very complicated solution but the query is so slow on the database I'm using (~100,000 records) that it is impractical to use.

I'll try to describe a simplified version of the problem.

Imagine I have a table called 'dog' which includes an individual ID ('uniqueid') and a code that indicates the breed of the sire ('sire_code'). The breed code links to a lookup table (breedLUT) that includes a long list of all breeds.


table dog

|---------------|--------------|
| unique_id | sire_code |
|---------------|--------------|
| 1 | 3 |
| 2 | 5 |
| 3 | 1 |
| 4 | NULL |
| 5 | 5 |
| etc | ... |
|---------------|--------------|


table breedLUT

|----------|----------------|
| code | breed_name |
|----------|----------------|
| 1 | labrador |
| 2 | spaniel |
| 3 | collie |
| 4 | gsd |
| 5 | terrier |
| etc. | ... |
|----------|----------------|


In order to get a list of sire breeds for individual dogs, I use the query:

SELECT dog.unique_id AS dog,
breedLUT.breed_name AS sire

FROM dog LEFT JOIN breedLUT ON dog.sire_code = breedLUT.code;


This produces the result:

|---------|--------------|
| dog | sire |
|---------|--------------|
| 1 | collie |
| 2 | terrier |
| 3 | labrador |
| 4 | NULL |
| 5 | terrier |
| etc. | ... |
|---------|--------------|


The problem I'm having is when I try to expand the dog table to include the breed of the dam. Both the sire_code and the dam_code refer to breeds in the breedLUT table. The expanded table is a follows:

|---------------|--------------|--------------|
| unique_id | sire_code | dam_code |
|---------------|--------------|--------------|
| 1 | 3 | 2 |
| 2 | 5 | 3 |
| 3 | 1 | 4 |
| 4 | NULL | 5 |
| 5 | 5 | 5 |
| etc. | ... | ... |
|---------------|--------------|--------------|


The result I want is:

|---------|--------------|--------------|
| dog | sire | dam |
|---------|--------------|--------------|
| 1 | collie | spaniel |
| 2 | terrier | collie |
| 3 | labrador | gsd |
| 4 | NULL | terrier |
| 5 | terrier | terrier |
| etc. | ... | ... |
|---------|--------------|--------------|

... BUT I can't for the life of me figure out how to link to the same LUT twice in the same query.

Any suggestions would be massively appreciated. Apologies if there is a really simple answer - I really can't see the wood for the trees.

Thanks

Phil

Options: ReplyQuote


Subject
Written By
Posted
Linking to a LUT more than once
August 05, 2011 07:33AM


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.