This is my current query and it's working fine as I expect it.
SELECT a.ID, a.CreatedDate, a.Status, b.OrderTotal,
e.Rate, f.RouteType, f.Code, g.Country, h.Network
from orders AS a
LEFT JOIN order_details AS b ON a.ID = b.OrderID
LEFT JOIN order_routes AS d ON a.ID = d.OrderID
LEFT JOIN userrate AS e ON e.ID = d.RouteID
LEFT JOIN ratelist AS f ON f.ID = e.RateListID
LEFT JOIN country AS g ON f.CountryID = g.ID
LEFT JOIN network AS h ON f.NetworkID = h.ID
WHERE a.UserID = 16 ORDER BY a.CreatedDate DESC
However now I am trying to add another column which shows me count of appearances of each OrderID from order_routes
My order routes look like this right now
ID OrderID RouterID
1 1 1
2 1 2
3 2 2
4 2 3
5 2 5
So I want column which shows me how many appearances OrderID have in order_routes table.
I think I need sub query inside my query but when I try that it giving me error. To let you know
a.ID = d.OrderID
e.ID = d.RouteID
Please help.