CODEWITHSUNDEEP
sqlsql-server
Anthony Sims
Anthony Sims

Reputation: 1

How do I join two tables with multiple related columns?

I think I'm asking the right question, but let me show you my code before you down-vote my post:

SELECT  co_ship.ship_date,
        co_ship.co_num,
        co.whse,
        co_ship.qty_shipped * co_ship.price,
        co_ship.price,
        co_ship.qty_shipped,
        co.cust_num

FROM    coitem, co_ship, co 

WHERE   co.co_num = coitem.co_num AND coitem.co_release = co_ship.co_release AND coitem.co_line = co_ship.co_line AND coitem.co_num = co_ship.co_num

What I am trying to do, is to get rid of the WHERE statement and replace it with a JOIN. (And yes, I realize that my code is probably horribly wrong) Here is what I'm trying:

SELECT  co_ship.ship_date,
    co_ship.co_num,
    co.whse,
    co_ship.qty_shipped * co_ship.price,
    co_ship.price,
    co_ship.qty_shipped,
    co.cust_num

FROM    coitem, co_ship, co 

JOIN    co
ON      coitem.co_num = co.co_num 

JOIN    coitem
ON      co_ship.co_release=coitem.co_release AND co_ship.coline=coitem.co_line AND co_ship.co_num=co_item.co_num

However, this does not work. I get this error: "The objects "co" and "co" in the FROM clause have the same exposed names. Use correlation names to distinguish them."

I am not entirely sure how to resolve it.

Upvotes: 0

Views: 63

Answers (1)

jpw
jpw

Reputation: 44881

When using the explicit join syntax you don't want more than one item as the source; the rest should appear in the joins (so no from table1, table2, ...)

Your joins should most likely look like this:

FROM    coitem
JOIN    co_ship 
    ON coitem.co_release = co_ship.co_release 
   AND coitem.co_line    = co_ship.co_line 
   AND coitem.co_num     = co_ship.co_num
JOIN    co ON co.co_num = coitem.co_num

so remove co_ship and co from the from clause.

Upvotes: 2

Related Questions