CODEWITHSUNDEEP
sql
Raphael
Raphael

Reputation: 171

Select inner join with multiple fields from same table

I currently have two tables in my database, one for the values (using Foreign Keys) and one for their translations (same table has translation for more than one attribute type).

I am trying to perform a single inner join from the Values table to the Translations table and translate two or more fields.

I know this is a bad database design but this database is used only to generate a single report that doesnt change *

Translation table

**id, attribute, value, name**
  1 , office   , 2    , office1
  2 ,  office  , 3    , office2
  3 ,  office  , 4    , office3
  4 ,  office  , 5    , office4
  5 ,  segment , 31   , segment1
  6 ,  segment , 32   , segment2
  7 ,  segment , 33   , segment3
  8 ,  segment , 34   , segment4

Values table

**office, segment, sum**
  2     , 31     , 1234
  3     , 31     , 4321
  5     , 34     , 9813
  2     , 33     , 8371

The result should be like this:

Results table

**office  , segment  , sum**
  office1 , segment1 , 1234
  office2 , segment1 , 4321
  office4 , segment4 , 9813
  office1 , segment3 , 8371

Is it possible? if yes, how?

Thank you.

Upvotes: 0

Views: 123

Answers (3)

Elfentech
Elfentech

Reputation: 747

This should look like :

SELECT t.name
      ,t.attribute
      ,v.sum
  FROM translation t
      ,VALUES      v
 WHERE t.value = v.office

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270993

You can do this by joining twice to the translation table:

SELECT toff.name as office, tseg.name as segment, v.`sum`
from values v join
     translation toff
     on v.office = toff.value and toff.attribute = 'office' join
     translation tseg
     on v.segment = tseg.value and tseg.attribute = 'segment';

I assume the attribute names are important for matching purposes.

Upvotes: 1

D Stanley
D Stanley

Reputation: 152634

Looks like you want to join the Translation table back to itself through the Values table:

SELECT o.name office, s.name segment, v.sum
FROM Translation o
INNER JOIN Values v ON o.value = v.office
INNER JOIN Translation s on v.segment = s.value

Upvotes: 0

Related Questions