Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2003
    Posts
    3

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

    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 ?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    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.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    rats, tony beat me again
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2003
    Posts
    3
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •