Error when trying to JOIN two derived tables

Question

I'm going nuts trying to figure out this error. I am working in MySQL and need to join two derived tables on a common column. All columns in the two tables are aliased as are the tables.

Scheme is

stops (id, name)

route (num, company, pos, stop) where

stops.id <---> route.stop

These tables contain bus routes between cities and I want to figure out all the routes from 'Craiglockhart' to 'Sighthill' which require exactly two buses (i.e., a transfer point). Moreover, because the route table doesn't contain stop names (only ID's), we use some joins inside the derived tables to refer to stops by name; this is just a convenience)

So I tried to make two derived tables. One of all the routes that can take a person between 'Craiglockhart' and any station which is not 'Craighartlock' and an identical second derived table but for 'Sighthill'. I was able to get both such tables to work separately.

But, when I tried to join them along the common column of the so-called transfer station (i.e., the non-Craighartlock station in the first table and the non-Sighthill station in the second), I get an error.

Derived Table 1: This works fine and returns the table correctly. here, stop_b.name represents the transfer station which is not Craiglockhart and stop_a.name represents the Craighill "starting" station.

SELECT * FROM 
    (SELECT a.num AS num_a, a.company AS comp_a, stop_a.name AS name_a, 
            stop_b.name AS name_transfer FROM
               route a JOIN route b ON (a.company=b.company AND a.num=b.num)
                    JOIN stops stop_a ON (a.stop=stop_a.id)
                    JOIN stops stop_b ON (b.stop=stop_b.id)
                    WHERE stop_a.name = 'Craiglockhart' AND 
                          stop_b.name <> 'Craiglockhart') AS first_route

Derived Table 2: SAME THING but different table alias and station restriction. Here stop_b.name represents the transfer station (which is not Sighthill) and stop_a.name represents the Sighthill "ending" station.

SELECT * FROM 
    (SELECT a.num AS num_a, a.company AS comp_a, stop_a.name AS name_a, 
            stop_b.name AS name_transfer FROM
               route a JOIN route b ON (a.company=b.company AND a.num=b.num)
                    JOIN stops stop_a ON (a.stop=stop_a.id)
                    JOIN stops stop_b ON (b.stop=stop_b.id)
                    WHERE stop_a.name = 'Sighthill' AND 
                          stop_b.name <> 'Sighthill') AS second_route

But, when I try to join these along their common `name_transfer' column (alias for stop_b.name in both) I get an error:

SELECT * FROM
(
SELECT * FROM 
    (SELECT a.num AS num_a, a.company AS comp_a, stop_a.name AS name_a, 
            stop_b.name AS name_transfer FROM
               route a JOIN route b ON (a.company=b.company AND a.num=b.num)
                    JOIN stops stop_a ON (a.stop=stop_a.id)
                    JOIN stops stop_b ON (b.stop=stop_b.id)
                    WHERE stop_a.name = 'Craiglockhart' AND 
                          stop_b.name <> 'Craiglockhart') AS first_route
JOIN

(SELECT * FROM 
    (SELECT a.num AS num_a, a.company AS comp_a, stop_a.name AS name_a, 
            stop_b.name AS name_transfer FROM
               route a JOIN route b ON (a.company=b.company AND a.num=b.num)
                    JOIN stops stop_a ON (a.stop=stop_a.id)
                    JOIN stops stop_b ON (b.stop=stop_b.id)
                    WHERE stop_a.name = 'Sighthill' AND 
                          stop_b.name <> 'Sighthill') AS second_route)

ON (first_route.name_transfer = second_route.name_transfer)
)

I also tried replacing ON with USING (name_transfer) since the column I want to join the derived tables on is called that in both of the derived tables.

Any help would be greatly appreciated!

Answer 1

Try this one.

SELECT * FROM
    (SELECT * FROM 
        (SELECT a.num AS num_a, a.company AS comp_a, stop_a.name AS name_a, 
                stop_b.name AS name_transfer FROM
                   route a JOIN route b ON (a.company=b.company AND a.num=b.num)
                        JOIN stops stop_a ON (a.stop=stop_a.id)
                        JOIN stops stop_b ON (b.stop=stop_b.id)
                        WHERE stop_a.name = 'Craiglockhart' AND 
                              stop_b.name <> 'Craiglockhart') AS first_route) AS A
    JOIN

    (SELECT * FROM 
        (SELECT a.num AS num_a, a.company AS comp_a, stop_a.name AS name_a, 
                stop_b.name AS name_transfer FROM
                   route a JOIN route b ON (a.company=b.company AND a.num=b.num)
                        JOIN stops stop_a ON (a.stop=stop_a.id)
                        JOIN stops stop_b ON (b.stop=stop_b.id)
                        WHERE stop_a.name = 'Sighthill' AND 
                              stop_b.name <> 'Sighthill') AS second_route)B
    ON (A.name_transfer = B.name_transfer)

Answer 2

You need parentheses around a subquery when you use it in a FROM or JOIN clause. You have it in the FROM clause, but not in the JOIN clause. The problem seems to have come when you added the unnecessary SELECT * FROM SELECT around each subquery.

SELECT * FROM 
    (SELECT a.num AS num_a, a.company AS comp_a, stop_a.name AS name_a, 
            stop_b.name AS name_transfer FROM
               route a JOIN route b ON (a.company=b.company AND a.num=b.num)
                    JOIN stops stop_a ON (a.stop=stop_a.id)
                    JOIN stops stop_b ON (b.stop=stop_b.id)
                    WHERE stop_a.name = 'Craiglockhart' AND 
                          stop_b.name <> 'Craiglockhart') AS first_route
JOIN

    (SELECT a.num AS num_a, a.company AS comp_a, stop_a.name AS name_a, 
            stop_b.name AS name_transfer FROM
               route a JOIN route b ON (a.company=b.company AND a.num=b.num)
                    JOIN stops stop_a ON (a.stop=stop_a.id)
                    JOIN stops stop_b ON (b.stop=stop_b.id)
                    WHERE stop_a.name = 'Sighthill' AND 
                          stop_b.name <> 'Sighthill') AS second_route

ON first_route.name_transfer = second_route.name_transfer