MySQL Forums :: General :: Linking to a LUT more than once


Advanced Search

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 Philip Jones 08/05/2011 07:33AM
Re: Linking to a LUT more than once Shawn Taylor 08/05/2011 08:25AM
Re: Linking to a LUT more than once Philip Jones 08/05/2011 08:43AM
Re: Linking to a LUT more than once Shawn Taylor 08/05/2011 09:11AM
Re: Linking to a LUT more than once Philip Jones 08/05/2011 09:17AM
Re: Linking to a LUT more than once Philip Jones 08/05/2011 10:31AM
Re: Linking to a LUT more than once Shawn Taylor 08/05/2011 11:18AM
Re: Linking to a LUT more than once Rick James 08/08/2011 07:00PM
Re: Linking to a LUT more than once Philip Jones 08/05/2011 04:46PM
Re: Linking to a LUT more than once Shawn Taylor 08/09/2011 05:49PM
Re: Linking to a LUT more than once Rick James 08/09/2011 07:20PM
Re: Linking to a LUT more than once Shawn Taylor 08/10/2011 08:52AM
Re: Linking to a LUT more than once Rick James 08/10/2011 07:07PM
Re: Linking to a LUT more than once Shawn Taylor 08/12/2011 12:29PM


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.