Multiple joins involving the same product table & ldquo; column does not exist & rdquo; error - MySQL

advertisements

I'm new to joins and I'm sure this is ridiculously simple.
If I remove one join in the query the remainder of the query works regardless of which join I remove. But as shown it gives the error saying the column doesn't exist. Any pointers?

select 

loc_carr.address1 as carr_addr1,

loc_cust.address1 as cust_addr1

from db_name.carrier, db_name.customer

join db_name.location as loc_carr on vats.carrier.location_id=loc_carr.location_id

join db_name.location as loc_cust on vats.customer.location_id=loc_cust.location_id

thanks


I'll take a guess that there is a column named something like carrier_id that can be used to join the carrier and customer tables. Given that assumption, try this:

select
     loc_carr.address1 as carr_addr1
   , loc_cust.address1 as cust_addr1

from vats.carrier  as a

join vats.customer as b
  on b.carrier_id=a.carrier_id

join vats.location as loc_carr
  on loc_carr.location_id=a.location_id

join vats.location as loc_cust
  on loc_cust.location_id=b.location_id

Notice the use of aliases for the table references to make things easier to read. Also note how I'm using explicit SQL join syntax (instead of listing tables separated by commas).