CODEWITHSUNDEEP
postgresql
Ryan Whitehead
Ryan Whitehead

Reputation: 21

PostgreSQL INNER JOIN/FROM Error

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!

Upvotes: 2

Views: 789

Answers (1)

Patrick
Patrick

Reputation: 32234

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...

Upvotes: 1

Related Questions