I don't understand why in the below query there has to be reference to zones table twice (as zpu and zdo). When I try modified query with one "zones" table instead of splitting it into zpu and zdo I have different outcome. The outcome table has to have two columns, one with pickup location and second with dropoff location. Locations are contained withing "zones" table.

Can't figure out why is that and what am I lacking in SQL mechanics.

Tables:

Trips

Zones

Query

select
    tpep_pickup_datetime,
    tpep_dropoff_datetime,
    total_amount,
    CONCAT(zpu."Borough", ' / ', zpu."Zone") AS "pick_up_loc",
    CONCAT(zdo."Borough", ' / ', zpu."Zone") AS "drop_off_loc"
from
    trips t,
    zones zpu,
    zones zdo
WHERE
    t."PULocationID" = zpu."LocationID" AND
    t."DOLocationID" = zdo."LocationID"

Outcome (good table):

CodePudding user response：

You select trips and join addresses, and you wonder why you are forced to join the addresses again and why it doesn't suffice to tell the DBMS once to get you the addresses.

This misunderstanding is probably bolstered by the ancient join syntax you are using - a syntax used in the 1980s mainly until explicit joins made it into the SQL standard in 1992.

When you join tables, you actuall join table rows.

With select * from trips, zones you join every trip row with every zone row. With eight trip rows and 10 zones you get 80 result rows. So you joined all ten zones to each trip. With select * from trips t, zones zpu, zones zdo you get a result of 800 rows, each trip combined with all ten zones and these again combined with all ten zones, i.e. all 100 possible zone combinations per trip.

In your query you have WHERE t.pulocationid = zpu.locationid AND t.dolocationid = zdo.locationid. For one trip there is only one of its 100 intermediate result rows that match this criteria and the other 99 are dismissed.

Now, if you joined the zones table only once, you had ten rows per trip in your intermediate result, each row would contain one zone. Now to your criteria, there are two options: use AND or OR:

select * from trips t, zones z
where t."PULocationID" = z."LocationID" AND t."DOLocationID" = z."LocationID"

This would only select rows for which t.pulocationid = t.dolocationid, because the criteria demand them to both match the one zone in the joined row. In your sample data there is not a single trip where the two locations are equal. You would get an empty result set.

select * from trips t, zones z
where t."PULocationID" = z."LocationID" OR t."DOLocationID" = z."LocationID"

With this query you would get the row where the zone matches the t.pulocationid and the row where the zone matches the t.dolocationid. I.e. you would get two result rows per trip, one with the pickup zone, one with the drop off zone.

But you want one result row per trip with both zones in that same result row.

Explicit joins make this more readable:

select
  tpep_pickup_datetime,
  tpep_dropoff_datetime,
  total_amount,
  CONCAT(zpu."Borough", ' / ', zpu."Zone") AS "pick_up_loc",
  CONCAT(zdo."Borough", ' / ', zpu."Zone") AS "drop_off_loc"
from trips t
inner join zones zpu on zpu."LocationID" = t."PULocationID"
inner join zones zdo on zdo."LocationID" = t."DOLocationID";

This does exactly the same thing as your query, but it is easier to read. Take a trip, join the pickup location row, join the drop off location row.