PostgreSQL INNER JOIN/FROM Error

Question

I'm brand new to SQL and I'm trying to figure out why I'm getting an error. I'm trying to add 2 columns to a query.

Here is the original code:

select f.id ride_id,f.driver_id,f.fleetid,f.fare,f.tip,f.service_fee,(f.fare+f.tip+f.service_fee) total,f.pick_up_city,f.pick_up_state,f.created_at
from
(select r.id,ra.driver_id,r.created_at,r.pick_up_city,r.pick_up_state,ra.xid fleetid,
MAX(CASE WHEN cli.category='fare' THEN cli.amount ELSE NULL END)/100 fare,
MAX(CASE WHEN cli.category='gratuity' THEN cli.amount ELSE NULL END)/100 tip,
MAX(CASE WHEN cli.category='flywheel_service_fee' THEN cli.amount ELSE NULL END)/100 service_fee
from rides r,charges c,charge_line_items cli,(select distinct ri.id,h.driver_id,f.xid
from rides ri inner join hails h on ri.id=h.ride_id
 inner join vehicles v on h.vehicle_id=v.id
  inner join fleets f on v.fleet_id=f.id
 where ri.status IN ('completed', 'waiting_for_payment_data', 'waiting_for_payment_instrument', 'processing_payment', 'payment_pending') 
and h.status='completed' and ri.created_at between '2015-07-15 04:00:00' and '2015-09-11 04:00:00' 
 and f.xid in ('10202')) ra
where r.id=ra.id and r.id=c.ride_id and c.id=cli.charge_id
group by r.id,ra.xid,ra.driver_id) f

I'm trying to find the driver first_name and last_name. I am adding INNER JOIN drivers AS dr on h.driver_id::VARCHAR = dr.xid and the dr.first_name, dr.last_name in the SELECT clause. (I put asterisks around the piece I added.)

select f.id ride_id,f.driver_id,**dr.first_name,dr.last_name,**f.fleetid,f.fare,f.tip,f.service_fee,(f.fare+f.tip+f.service_fee) total,f.pick_up_city,f.pick_up_state,f.created_at
from
(select r.id,ra.driver_id,r.created_at,r.pick_up_city,r.pick_up_state,ra.xid fleetid,
MAX(CASE WHEN cli.category='fare' THEN cli.amount ELSE NULL END)/100 fare,
MAX(CASE WHEN cli.category='gratuity' THEN cli.amount ELSE NULL END)/100 tip,
MAX(CASE WHEN cli.category='flywheel_service_fee' THEN cli.amount ELSE NULL END)/100 service_fee
from rides r,charges c,charge_line_items cli,(select distinct ri.id,h.driver_id,f.xid
from rides ri inner join hails h on ri.id=h.ride_id
 inner join vehicles v on h.vehicle_id=v.id
  inner join fleets f on v.fleet_id=f.id
     **inner join drivers AS dr on h.driver_id::VARCHAR = dr.xid**
 where ri.status IN ('completed', 'waiting_for_payment_data', 'waiting_for_payment_instrument', 'processing_payment', 'payment_pending') 
and h.status='completed' and ri.created_at between '2015-07-15 04:00:00' and '2015-09-11 04:00:00' 
 and f.xid in ('10202')) ra
where r.id=ra.id and r.id=c.ride_id and c.id=cli.charge_id
group by r.id,ra.xid,ra.driver_id) f

When I run this query, I get an error saying "missing FROM-clause entry for table dr". Normally, I could just JOIN the table I want to SELECT from but for some reason, there is something in the query that is not letting me do this.

Any help would be greatly appreciated. I am still learning and really want to figure out what I'm doing wrong.

Thanks!

Patrick · Accepted Answer · 2015-09-11 20:22:42Z

1

You have added the JOIN in a second-level sub-query. That is fine, but you have to "propagate" the two columns in the select lists of the second-level sub-query out to the main level, using the aliases assigned to the sub-queries. Proper outlining of the code makes the error and the solution obvious:

SELECT f.id AS ride_id, f.driver_id,
       f.first_name, f.last_name,
       f.fleetid, f.fare, f.tip, f.service_fee, (f.fare+f.tip+f.service_fee) total,
       f.pick_up_city, f.pick_up_state, f.created_at
FROM (
  -- First sub-query starting
  SELECT r.id, ra.driver_id, r.created_at, r.pick_up_city, r.pick_up_state, ra.xid AS fleetid,
         ra.first_name, ra.last_name,
         max(CASE WHEN cli.category = 'fare' THEN cli.amount ELSE NULL END)/100 fare,
         max(CASE WHEN cli.category = 'gratuity' THEN cli.amount ELSE NULL END)/100 tip,
         max(CASE WHEN cli.category= 'flywheel_service_fee' THEN cli.amount ELSE NULL END)/100 service_fee
  FROM rides r, charges c, charge_line_items cli, (
    -- Second sub-query starting
    SELECT DISTINCT ri.id, h.driver_id, f.xid, dr.first_name, dr.last_name
    FROM rides ri
    INNER JOIN hails h ON ri.id = h.ride_id
    INNER JOIN vehicles v ON h.vehicle_id = v.id
    INNER JOIN fleets f ON v.fleet_id = f.id
    INNER JOIN drivers dr ON h.driver_id::varchar = dr.xid
    WHERE ri.status IN ('completed', 'waiting_for_payment_data', 'waiting_for_payment_instrument', 'processing_payment', 'payment_pending') 
      AND h.status = 'completed'
      AND ri.created_at BETWEEN '2015-07-15 04:00:00' AND '2015-09-11 04:00:00' 
      AND f.xid IN ('10202')) ra
    -- End of second sub-query
  WHERE r.id = ra.id
    AND r.id = c.ride_id
    AND c.id = cli.charge_id
  -- The below GROUP BY clause is incomplete, see added line
  GROUP BY r.id, ra.xid, ra.driver_id
           , r.created_at, r.pick_up_city, r.pick_up_state, ra.first_name, ra.last_name) f
  -- End of first sub-query
;

Otherwise the original query is a curious mix of coding styles, with - for instance - two styles for listing multiple tables (comma-separated and JOIN clause), odd capitalization and - as you have just learned - crappy outlining. Seems like you are not the only one who has some things to learn...

answered Sep 11, 2015 at 20:22

Patrick

33k7 gold badges73 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ryan Whitehead Over a year ago

Thanks a ton! Yeah, I'm pretty huge on formatting so I had a hard time figuring this out. This is great though!

Patrick Over a year ago

Welcome to SO, Ryan. If this answer solves your problem, you should accept it (tick the gray check mark to the left of the question). Like that other users will know this is a proper answer. You may even consider up-voting or down-voting {:-( using the up/down buttons if you think my answer is worth the attention of other users / dreadful. This is the way that other users can quickly identify what is good/bad and the basis for the reputation system.

Collectives™ on Stack Overflow

PostgreSQL INNER JOIN/FROM Error

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related