A table with 2 extern ID in the same row...?

bikelink · #1 (**permalink**) 03-23-06, 04:40

Table1 :

Code (Primary key) │ ID-Desc1 │ ID-Desc2
"aaaaa" │ 100 │ 3450
"bbbbb" │ 102 │ 3454

________________________________________________

Table2

IDDescGeneric │ DescriptionText │ IDlanguage ecc...
100 │ art1 │ 1031
102 │ art2 │ 1031
3450 │ art3 │ 1031
3454 │ art4 │ 1031

I need to obtain rows as
"aaaaa" │ art1 | art3
"bbbbb" │ art2 | art4

the structure already exists..
Do you think that's a good choice to have 2 IDs fields in the same row, in order to obtain a value from unique ID from another table ?
I'm not that's good...but i'm not very expert..

I suppose to obtain the result that I need to write a nested query... or divide the tables..

Any suggest to works better ?

andrewst · #2 (**permalink**) 03-23-06, 07:08

You can query it easily enough just by joining to the lookup table twice:

select ...
from table1
join table2 t2a on t2a.IDDescGeneric = table1.IDdesc1
join table2 t2b on t2b.IDDescGeneric = table1.IDdesc2;

There is nothing wrong in principle in having two foreign keys from one table referening the same parent table - e.g. a bank transfer table might have a from_account_id and a to_account_id. However what you have here looks to me suspiciously like a One True Lookup Table which as you can see from that link, I don't like.

r937 · #3 (**permalink**) 03-23-06, 07:13

Quote:

Originally Posted by bikelink

Do you think that's a good choice to have 2 IDs fields in the same row, in order to obtain a value from unique ID from another table ?

this is a very common structure

sometimes it is better to have multiple occurrences in a separate one-to-many table, but it is often also okay to have two of them side by side the way you have it

Code:

select t1.code
     , t2one.DescriptionText as Description_one
     , t2two.DescriptionText as Description_two
  from Table1 as t1
inner
  join Table2 as t2one
    on t2one.IDDescGeneric = t1.IDDesc1
inner
  join Table2 as t2two
    on t2two.IDDescGeneric = t1.IDDesc2

r937 · #4 (**permalink**) 03-23-06, 07:14

rats, tony beat me again

bikelink · #5 (**permalink**) 03-24-06, 05:40

Quote:

Originally Posted by r937

this is a very common structure

sometimes it is better to have multiple occurrences in a separate one-to-many table, but it is often also okay to have two of them side by side the way you have it

Code:

select t1.code
     , t2one.DescriptionText as Description_one
     , t2two.DescriptionText as Description_two
  from Table1 as t1
inner
  join Table2 as t2one
    on t2one.IDDescGeneric = t1.IDDesc1
inner
  join Table2 as t2two
    on t2two.IDDescGeneric = t1.IDDesc2

ok ! Thank you. now i'm going to try it

Internet Services

Web Development

Digital Marketing

Consumer Tech