0

I'm trying to join three tables in order to generate a list of all our users and their locations.

The first table lists all of our users with two separate IDs--userId and uid. The second table is an "orders" table where user location data (delivery zone and billing zone) is stored by uid, and we have a third table that acts as a location key (e.g., zone_id 18 = zone_name Florida).

What I want is each userId, plus the converted human-readable zone_name for each user's billing zone and delivery zone.

My code works fine when I try only to convert one of these zones. So this query works great: 

SELECT u.userId, o.delivery_postal_code, o.billing_postal_code, z1.zone_name
FROM webapp.users AS u
LEFT OUTER JOIN practice_drupal.uc_orders AS o
    ON u.uid = o.uid
    LEFT OUTER JOIN practice_drupal.uc_zones AS z1
        ON o.billing_zone = z1.zone_id;

But I get an error when I try to generate the zone_name for the second zone (delivery). The error is "Unknown column 'z2.zone_name' in 'field list' ". Here's my query: 

SELECT u.userId, o.delivery_postal_code, z1.zone_name, o.billing_postal_code,  z2.zone_name
FROM webapp.users AS u
LEFT OUTER JOIN practice_drupal.uc_orders AS o
    ON u.uid = o.uid
LEFT OUTER JOIN practice_drupal.uc_zones AS z1
    ON o.billing_zone = z1.zone_id;
LEFT OUTER JOIN practice_drupal.uc_zones AS z2
    ON o.delivery_zone = z2.zone_id;

I'm confused because my scenario seems almost identical to this one, with the exception of using outer joins, but I can't get bluefeet's solution to work for me: SQL Inner-join with 3 tables?

Any ideas? Thanks!

Community
  • 1
  • 1
Geoff D.
  • 1
  • 4
    Your joins are conceptually correct. I do see an errant extra `;` after `z1.zone_id` but would not expect that to produce the error message you received. (I would expect a 1064, check syntax near `LEFT OUTER JOIN...`. – Michael Berkowski Jul 23 '15 at 22:01
  • Oh god, that was it. Thanks very much! – Geoff D. Jul 23 '15 at 22:02
  • @MichaelBerkowski you caught the right problem, but the execution wouldn't have gotten far enough for that error since he references z2 in the 'first" query's select. – Uueerdo Jul 23 '15 at 22:24
  • @Uueerdo I removed an earlier comment -- depends on the client API. Some would break because they permit only one statement and error on the second. Others would parse and execute the first, then the second, still others may just stop after the first entirely. – Michael Berkowski Jul 23 '15 at 22:33

0 Answers0