0

I have a query with several left joins and with last left join in which one I'm using sub query (because it has to be ordered) I need several columns to represent. Query that I wrote:

SELECT pr.id, prs.requestStatus, prs.changeDate 
FROM `ho_port_phone_number_requests` pr
left join ho_port_phone_number_requests_rejection_reason rrr on rrr.id = 
pr.rejectionReasonId
left join ho_user u on u.userId = pr.csId
left join ho_customer c on c.id = pr.customerId
left JOIN 
(SELECT requestId, requestStatus, changeDate FROM 
ho_port_phone_number_request_status ORDER BY requestId DESC LIMIT 1) prs
ON prs.requestId = pr.id

I know that I can write two sub queries inside SELECT but then it's last much longer. The code that work but is slow:

SELECT pr.id,
(SELECT st.requestStatus FROM ho_port_phone_number_request_status st
WHERE st.requestId = pr.id ORDER BY requestId DESC LIMIT 1) as rStatus,
(SELECT st.changeDate FROM ho_port_phone_number_request_status st
WHERE st.requestId = pr.id ORDER BY requestId DESC LIMIT 1) as rDate
FROM `ho_port_phone_number_requests` pr
left join ho_port_phone_number_requests_rejection_reason rrr on rrr.id = 
pr.rejectionReasonId
left join ho_user u on u.userId = pr.csId
left join ho_customer c on c.id = pr.customerId

Any advice?

Dzoni
  • 31
  • 7
  • 1
    Sorry, I don't get what your question is here? What is wrong with the first example? – freefaller Jul 16 '20 at 15:36
  • It gives me null values for prs.requestStatus and prs.changeDate @freefaller – Dzoni Jul 16 '20 at 15:55
  • 1
    Ah, ok - the first example will only return a single row from the sub-total, due to your `LIMIT 1`, therefore only a single `request` is going to match it. I would suggest either looking at `group by` and potentially `GROUP_CONCAT` (although I've never used it before) or possible a [CTE](https://dev.mysql.com/doc/refman/8.0/en/with.html). Check out [this question](https://stackoverflow.com/q/2129693/930393) – freefaller Jul 16 '20 at 16:03

1 Answers1

-1

You can try below query it will return you proper result if requestId is unique for every pr.id.

SELECT pr.id, prs.requestStatus, prs.changeDate 
FROM `ho_port_phone_number_requests` pr
left join ho_port_phone_number_requests_rejection_reason rrr on rrr.id = 
pr.rejectionReasonId
left join ho_user u on u.userId = pr.csId
left join ho_customer c on c.id = pr.customerId
left JOIN ho_port_phone_number_request_status prs on prs.requestId = pr.id
where prs.requestId = (SELECT min(requestId) FROM 
ho_port_phone_number_request_status where requestId = pr.id)
group by pr.id
Vivek Jain
  • 2,730
  • 6
  • 12
  • 27