How would I combine these 2 SQL statement?

Question

select to4.ProportionOfRoute, to4.RouteID 
from tbl_operatorrouterelation to4 
where to4.operatorID = (
    select to2.operatorid 
    from tbl_operatordesc to2 
    where to2.Name = "OK Travel"
)


select tr.Frequency, tr.routeID
from tbl_route tr
where tr.routeID IN (
    select to3.RouteID 
    from tbl_operatorrouterelation to3 
    where to3.operatorID = (
        select to2.operatorid 
        from tbl_operatordesc to2 
        where to2.Name = "OK Travel"
    )
)

So the first one selects values from a table with multiple unique RouteID's. The second one selects values from another table with those same RouteID's.

I need to multiple the ProportionOfRoute's with the corresponding Frequency values together so this all has to be in one query.

Thanks! I've been working on this for 2 days now

You need to `INNER JOIN` the tables instead of doing separate queries. That will combine both queries and remove both of the `IN` sub-selects. — Ken White, Dec 03 '20 at 19:22

O. Jones · Answer 1 · 2020-12-03T20:11:09.393

This sequence of JOIN operations should do what you want.

SELECT tbl_operatorrouterelation.ProportionOfRoute, 
       tbl_route.RouteId,
       tbl_route.Frequency
  FROM tbl_operatordesc 
  JOIN tbl_operatorrouterelation 
         ON tbl_operatordesc.operatorID = tbl_operatorrouterelation.operatorID
  JOIN tbl_route 
         ON tbl_route.routeID = tbl_operatorrouterelation.routeID
 WHERE tbl_operatordesc = "OK Travel";

It looks like your schema (your table layout) has two entities, route and operatordesc. It looks like they have a many-to-many relationship between them that's stored in your operatorrouterelation table: If an operator handles a route, there's a row in that table containing both the operatorID and the routeID. This means that an operator may have multiple routes, and a route may have multiple operators.

So, when you want to retrieve information about the operator called "OK Travel" including information about the routes they operate, you start with tbl_operatordesc. You then join it to tbl_operatorrouterelation, and from there you join to tbl_route.

I removed all the table aliases (t02, t03, ...) from my query for the sake of clarity.

Pro tip: As you write queries, do your best to understand the entity-relationship structure of your data. When you do understand it, it's much easier to write correct queries.

How would I combine these 2 SQL statement?

1 Answers1