SQL: Joining different look-up IDs in a record to the same secondary t 1

[chipboard]

Hi all,

So I'm familiar with the technique of using SQL to return the text values in an external table which are linked to my main table using Lookup fields as follows:

SELECT
MainTable.RecordId, SecondTable.RecordName
FROM
MainTable INNER JOIN SecondTable
ON
MainTable.SecondTableId = SecondTable.SecondTableId

But my main table in reality has a few different Lookup values which all take their value from the second table. They all select values from the same secondary table, as I thought this would save space. But in one record the three different columns can have different values.

So my question is this: when using an SQL query to return data, how do I specify to return the text value from SecondTable once for each different value in these three columns?

Looking forward to any help available - thanks...

 

[drlex]

I think I understand your request, but it may be best to post some example data (made-up, if sensitive) and the required results.

If it's a situation where the second table carries multiple information, then you'll need to alias it for as many times as you need to seek information.

example
Table 2 is a description table with 3 columns: type, id and description whilst table 1 is a product table

Table 1: Product
product_code, main_group, sub_group

Table 2: Detail
type, code, description

To get the group description, one needs to join on code and main_group for type = 1; to get the sub-group, a join on code and sub_group for type = 2. The product itself has a description for type = 0

To return a fully described product, one needs 3 joins to table 2:

Select T1.product_code, T2.description as 'Product Descr.', T1.main_group, T3.Description as 'Group Descr.', T1.sub_group, T4.Description as 'Sub Group Descr.'
From
Product T1 inner join Detail T2 on T2.code = 0 and T1.product_code = T2.code
inner join Detail T3 on T3.code = 1 and T1.main_group = T3.code
inner join Detail T4 on T4.code = 2 and T1.sub_group = T4.code

If I've misunderstood, please ignore the foregoing and just post some example data.


soi là, soi carré

 

[chipboard]

Hi Drlex, thanks for your response.

I think I understand what you are getting at but here are some examples to make sure we are on the same page:

So for example my Second table could have the following data:

SecondTableId     SecondTableText
1                 Days
2                 Years
3                 Months

And my main table could have the following:

MainTableId  Fld1 Lookup1 Fld2 Lookup2 Fld3 Lookup3
1            5    1       7    3       9    2
2            3    3       2    3       8    1
3            7    2       9    1       8    2

And I'd like to see the following results:

1    5 days   7 months 9 years
2    3 months 2 months 8 days
3    7 years  9 days   8 years

From your previous explanation it seems I have to create extra tables - where do your T3 and T4 table names come from??

Thanks again for your help,

 

[drlex]

Thanks for the information - it does confirm my interpretation.

There's no creation of extra tables; the T2, T3 & T4 are aliases to allow multiple reference to the same table in the select statement. If you just re-used T2 in place of T3 and T4, then it would resolve to give only those lines in the MainTable that had the same value in Lookup1, Lookup2 and Lookup3.

With your example, the code required would be like

SELECT T1.MainTableId, T1.Fld1, T2.SecondTableText , T1.Fld2, T3.SecondTableText, T1.Fld3, T4.SecondTableText
From MainTable T1 inner join Second Table T2 on T1.Lookup1 = T2.SecondTableId
 inner join Second Table T3 on T1.Lookup2 = T3.SecondTableId
 inner join Second Table T4 on T1.Lookup3 = T4.SecondTableId

(as per Boris: NOT TESTED!)

Hopefully that makes sense.

soi là, soi carré

 

[chipboard]

It does - thanks, I'll try it and let you know how I get on.

 

[chipboard]

Hi drlex, it worked fine and I'm getting the results I need. I didn't really know about aliases before so I can see now that they are pretty useful!

Thanks for your help,

 

[drlex]

You're welcome - I learned most of my SQL from hanging around this forum and am still learning.

soi là, soi carré