none
Creating a join based on calculated fields RRS feed

  • Question

  • hi,

    appologies in advance if this question is already answered, but is it possible to create a join based on a calculated field of results?

    i have two tables that contain the same infomation about products other than the product number(tables a and b). i have a link table that contains the reference for the product numbers.

    is it possible to take the results of joining table a to the link table, in order to compare a and b based on the extracted product numbers from the link table

    as far as i can see joins require fully qualifing column names, is there any way around this?

    Regards

    DPERROTT
    Tuesday, June 23, 2009 11:02 AM

Answers

  • Normally you reference a calculated result in a JOIN or WHERE clause by pushing it down to a subquery, then referencing its result in the parent.  But in your case, I think you can more easily get your desired result by a simple 3-way join, i.e.:

    SELECT
    l.productnoa as productnumber,
    productaname,
    productaprice,
    productbname,
    productbprice
    FROM tablea a
    LEFT OUTER JOIN linktable l
    ON a.productno=l.productnoa
    INNER JOIN tableb b
    ON b.productno = productnumber
    Michael Asher
    • Proposed as answer by h0xff Tuesday, June 23, 2009 11:34 AM
    • Marked as answer by dperrott Tuesday, June 23, 2009 11:44 AM
    Tuesday, June 23, 2009 11:31 AM

All replies

  • Sure, you can join on any expression, e.g:

    SELECT *
    FROM Table1
    JOIN Table2
    ON Table1.col1 + Table1.col2 < SQRT((Table2.x)
    Michael Asher
    Tuesday, June 23, 2009 11:08 AM
  • thanks, but that isn't exactly what i wanted to do,

    i need to base the join on the result of the calculated field.

    select (select productnoa as productnumber from linktable where tablea.productno = linktable.productnoa), productaname, productaprice, productbname, productbprice
    from tablea inner join on tableb.productno = productnumber
    the result would show every product that was in table b that is in table a

    apologies if this doesn't make sense,

    DPERROTT
    Tuesday, June 23, 2009 11:22 AM
  • Normally you reference a calculated result in a JOIN or WHERE clause by pushing it down to a subquery, then referencing its result in the parent.  But in your case, I think you can more easily get your desired result by a simple 3-way join, i.e.:

    SELECT
    l.productnoa as productnumber,
    productaname,
    productaprice,
    productbname,
    productbprice
    FROM tablea a
    LEFT OUTER JOIN linktable l
    ON a.productno=l.productnoa
    INNER JOIN tableb b
    ON b.productno = productnumber
    Michael Asher
    • Proposed as answer by h0xff Tuesday, June 23, 2009 11:34 AM
    • Marked as answer by dperrott Tuesday, June 23, 2009 11:44 AM
    Tuesday, June 23, 2009 11:31 AM
  • that is brilliant, i didn't know i could do three way joins, i guess you do learn something new everyday !


    cheers,

    DPERROTT
    Tuesday, June 23, 2009 11:45 AM
  • any idea's on changing the query into an update query to copy tablea.productname into tableb.productame ??

    cheers

    DPERROTT
    Tuesday, June 23, 2009 11:58 AM
  • update tablea
    set productaname = productbname
    FROM tablea a
    LEFT OUTER JOIN linktable l
    ON a.productno=l.productnoa
    INNER JOIN tableb b
    ON b.productno = productnumber
    Tuesday, June 23, 2009 3:54 PM