Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2016
    Posts
    1

    Unanswered: Lookup from Multiple Columns without self join.

    I have two tables:TabA


    ID,ID2, Field1, Field2
    1, 1, red, Honda
    1, 2, blue, ford
    2, 1, black, mustang

    TabB
    Value, Description
    red, colur red
    blue, colur blue
    black, colur black
    honda, makes cheap cars
    ford, makes cars
    mustang, american muscle


    How can I get an output like so:

    1 , 1, red color red, honda, makes cheap cars


    I tried referencing TabB multiple times in my SQL, but the DBA says that if they run that in production, it would likely crash the system. That tables has millions of rows.

    I'm only months into coding, so please help me out

  2. #2
    Join Date
    Oct 2007
    Posts
    52
    Provided Answers: 3
    best to join to the B table twice. There should be an index on the Value column, so, not sure why your DBA says it will crash the system. As this would, usually, outperform an AND/OR condition against the table.

    Here is what I would picture it looking like:
    Code:
    select a.id, a.id2, a.field1, b.description, a.field2, b1.description
       from TabA a
    inner join TabB b
        on a.field1 = b.value
    inner join TabB b1
        on a.field2 = b1.value
    where ....some conditions apply...
    That should perform better than:

    Code:
    select a.id, a.id2, a.field1, b.description, a.field2, b1.description
       from TabA a
    inner join TabB b
        on (a.field1 = b.value
          or a.field2 = b.value)
    where ....some conditions apply...
    Dave Nance

Posting Permissions

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