How do I equal SQL composite / multivalued variable against another multivalued composite variable

Question

I am trying to work with composite variables just as easy as one variable. I am doing a self join, and I am trying to match the same company and number with another company and number with specific values.

route(company, num, stop, pos) Every "num" (or bus route number) has multiple "stop" (stop numbers addresses) on their bus route, and 1 through X "pos" (or stop orders).

select a.company, a.num
from route as a
join route as b
on a.company = b.company and a.num = b.num
where (a.company, a.num) = any(
   select a.company, a.num 
   from a 
   join b
   where a.stop = XXX, b.stop = YYY)

Routes are made unique in the table by company and num like a composite primary key. I would like to know which routes stop at XXX and YYY. Is there anyway I can do this using the ANY function. I know how to do it with just where statements and group by. My issue is specifically "where (a.company, a.num) = any(select a.company, a.num..."

Answer 1

Believe you are using MSSQL. for ANY or SOME, it can compare scalar values only, not pairs like (a.company, a.num).

If you stick to use ANY, you could join the two values using comma - as they are unique, the joined value will be unique too. something like:

select a.company, a.num
from route as a
join route as b
on a.company = b.company and a.num = b.num
where (a.company + ',' + a.num) = any(
   select a.company + ',' + a.num 
   from a 
   join b
   where a.stop = XXX, b.stop = YYY)