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
I'll take a guess that there is a column named something like
carrier_id that can be used to join the
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).