How Can I include inner join column

Hello Experts,

Is there a way to include a column in my SELECT (returning results) that I get as part of a sub-select query within an inner join, which I later use as a WHERE condition?

From my code below I would like to see Tot_comm in my results set, because I am actually building a PDF report where I need to display the total commission before the actual details, so I cannot use an accumulating variable.

SELECT res.id, user_initials, pickup_date, pickup_time, trip_type, pickup_status, niagara_trip_id, booking_date, booking_location AS bookloc_id, bookloc_table.name AS bookloc_name, res.driver_id, product, products.display_as AS product_name, pickup_location AS pickup_loc_id, pickup_loc_table.name AS pickup_loc_name, pickup_loc_table.phone AS pickup_loc_phone, agent_id, CONCAT(agents.fName, ' ', LEFT(agents.lName, 1), '.') AS agent_name, group_name, room, family_qty, adult_qty, senior_qty, student_qty, child_qty, infant_qty, total_pax, total_cost, deposit, cash, credit_card, debit, prepaid, comm_total, trans_status, comm_status, reserv_comment, res.archived AS archived, combo_assoc_id, comm_owe 

FROM reservations AS res INNER JOIN locations AS bookloc_table ON bookloc_table.id = res.booking_location INNER JOIN locations AS pickup_loc_table ON pickup_loc_table.id = res.pickup_location INNER JOIN products ON products.id = res.product INNER JOIN (SELECT agent_id AS comm_agent_id, booking_location AS comm_book_loc, SUM(comm_owe) AS tot_com FROM reservations AS res 
                                                                                                                                                                                                                                                             
                                                                                                                                                                           WHERE trip_type != 'M' AND res.archived = 0 AND product != 'E' AND pickup_status != 'X' AND comm_status = 'Owe' AND comm_owe != 0 AND ( (pickup_date = '2013-09-02' AND pickup_time BETWEEN '00:00' AND '23:59') OR (pickup_date = '2013-09-03' AND pickup_time BETWEEN '00:00' AND '23:59') OR (pickup_date = '2013-09-04' AND pickup_time BETWEEN '00:00' AND '23:59') OR (pickup_date = '2013-09-05' AND pickup_time BETWEEN '00:00' AND '23:59') OR (pickup_date = '2013-09-06' AND pickup_time BETWEEN '00:00' AND '23:59') OR (pickup_date = '2013-09-07' AND pickup_time BETWEEN '00:00' AND '23:59') OR (pickup_date = '2013-09-08' AND pickup_time BETWEEN '00:00' AND '23:59') ) GROUP BY agent_id, booking_location HAVING SUM(comm_owe) >0 ) AS comm_tot_table ON res.agent_id = comm_tot_table.comm_agent_id AND res.booking_location = comm_tot_table.comm_book_loc INNER JOIN agents ON agents.id = res.agent_id WHERE trip_type != 'M' AND res.archived = 0 AND product != 'E' AND pickup_status != 'X' AND comm_status = 'Owe' AND comm_owe != 0 AND ( (pickup_date = '2013-09-02' AND pickup_time BETWEEN '00:00' AND '23:59') OR (pickup_date = '2013-09-03' AND pickup_time BETWEEN '00:00' AND '23:59') OR (pickup_date = '2013-09-04' AND pickup_time BETWEEN '00:00' AND '23:59') OR (pickup_date = '2013-09-05' AND pickup_time BETWEEN '00:00' AND '23:59') OR (pickup_date = '2013-09-06' AND pickup_time BETWEEN '00:00' AND '23:59') OR (pickup_date = '2013-09-07' AND pickup_time BETWEEN '00:00' AND '23:59') OR (pickup_date = '2013-09-08' AND pickup_time BETWEEN '00:00' AND '23:59') ) 

ORDER BY bookloc_name ASC, agent_name ASC, trip_type DESC, pickup_date ASC, pickup_time ASC 

Open in new window


Any help would be appreciated.
APD TorontoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HuaMin ChenProblem resolverCommented:
You can also put one "Sum" column like

SELECT res.id, user_initials, (select sum(...) from ...) as total2, pickup_date, pickup_time, trip_type, pickup_status, niagara_trip_id, booking_date, booking_location AS bookloc_id, bookloc_table.name AS bookloc_name, res.driver_id, product, products.display_as AS product_name, pickup_location AS pickup_loc_id, pickup_loc_table.name AS pickup_loc_name, pickup_loc_table.phone AS pickup_loc_phone, agent_id, CONCAT(agents.fName, ' ', LEFT(agents.lName, 1), '.') AS agent_name, group_name, room, family_qty, adult_qty, senior_qty, student_qty, child_qty, infant_qty, total_pax, total_cost, deposit, cash, credit_card, debit, prepaid, comm_total, trans_status, comm_status, reserv_comment, res.archived AS archived, combo_assoc_id, comm_owe 

FROM reservations AS res INNER JOIN locations AS bookloc_table ON bookloc_table.id = res.booking_location INNER JOIN locations AS pickup_loc_table ON pickup_loc_table.id = res.pickup_location INNER JOIN products ON products.id = res.product INNER JOIN (SELECT agent_id AS comm_agent_id, booking_location AS comm_book_loc, SUM(comm_owe) AS tot_com FROM reservations AS res 
                                                                                                                                                                                                                                                             
                                                                                                                                                                           WHERE trip_type != 'M' AND res.archived = 0 AND product != 'E' AND pickup_status != 'X' AND comm_status = 'Owe' AND comm_owe != 0 AND ( (pickup_date = '2013-09-02' AND pickup_time BETWEEN '00:00' AND '23:59') OR (pickup_date = '2013-09-03' AND pickup_time BETWEEN '00:00' AND '23:59') OR (pickup_date = '2013-09-04' AND pickup_time BETWEEN '00:00' AND '23:59') OR (pickup_date = '2013-09-05' AND pickup_time BETWEEN '00:00' AND '23:59') OR (pickup_date = '2013-09-06' AND pickup_time BETWEEN '00:00' AND '23:59') OR (pickup_date = '2013-09-07' AND pickup_time BETWEEN '00:00' AND '23:59') OR (pickup_date = '2013-09-08' AND pickup_time BETWEEN '00:00' AND '23:59') ) GROUP BY agent_id, booking_location HAVING SUM(comm_owe) >0 ) AS comm_tot_table ON res.agent_id = comm_tot_table.comm_agent_id AND res.booking_location = comm_tot_table.comm_book_loc INNER JOIN agents ON agents.id = res.agent_id WHERE trip_type != 'M' AND res.archived = 0 AND product != 'E' AND pickup_status != 'X' AND comm_status = 'Owe' AND comm_owe != 0 AND ( (pickup_date = '2013-09-02' AND pickup_time BETWEEN '00:00' AND '23:59') OR (pickup_date = '2013-09-03' AND pickup_time BETWEEN '00:00' AND '23:59') OR (pickup_date = '2013-09-04' AND pickup_time BETWEEN '00:00' AND '23:59') OR (pickup_date = '2013-09-05' AND pickup_time BETWEEN '00:00' AND '23:59') OR (pickup_date = '2013-09-06' AND pickup_time BETWEEN '00:00' AND '23:59') OR (pickup_date = '2013-09-07' AND pickup_time BETWEEN '00:00' AND '23:59') OR (pickup_date = '2013-09-08' AND pickup_time BETWEEN '00:00' AND '23:59') ) 

ORDER BY bookloc_name ASC, agent_name ASC, trip_type DESC, pickup_date ASC, pickup_time ASC 

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
You may use SUM(...) OVER(PARTITION BY ...) instead of that subquery, however you cannot use it directly in a a where clause unless the overall query is treated as a derived table - see line 21
SELECT
*
FROM (
        SELECT
              res.id,             .... more here

            , SUM(comm_owe) OVER(PARTITION BY agent_id,booking_location) AS tot_com
        FROM reservations AS res
              INNER JOIN locations AS bookloc_table ON bookloc_table.id = res.booking_location
              INNER JOIN locations AS pickup_loc_table ON pickup_loc_table.id = res.pickup_location
              INNER JOIN products ON products.id = res.product
              INNER JOIN agents ON agents.id = res.agent_id
        WHERE trip_type != 'M'
              AND res.archived = 0
              AND product != 'E'
              AND pickup_status != 'X'
              AND comm_status = 'Owe'
              AND comm_owe != 0
              AND pickup_date BETWEEN '2013-09-02' AND '2013-09-08'
      ) AS derived
WHERE tot_com > 0
ORDER BY
      bookloc_name ASC
    , agent_name   ASC
    , trip_type    DESC
    , pickup_date  ASC
    , pickup_time  ASC

Open in new window

But I also would like to point out that there is nothing to be gained by using AND pickup_time BETWEEN '00:00' AND '23:59' so you may as well just simplify the way you select a date range
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.