all combination output in mysql

Question

I have three tables: table1, table2, table3.

Table1 contains:

Title1  desc
LHR     LONDON HEATHROW
LGW     LONDON GATWICK

Table2 contains:

Title2  desc2
W1      TESTW1
W2      TESTW2

Table3 contains:

vehicle    desc3
SALOON     SALOON DESC
VIP        VIP DESC
EXECUTIVE  EXECUTIVE DESC

I want to output all combinations like this:

title-common title-common2 vehicle    
LHR          W1            saloon
LHR          W1            VIP
LHR          W1            EXECUTIVE
LHR          W2            saloon
LHR          W2            VIP
LHR          W2            EXECUTIVE   
LGW          W1            saloon
LGW          W1            VIP
LGW          W1            EXECUTIVE
LGW          W2            saloon
LGW          W2            VIP
LGW          W2            EXECUTIVE   
W1           LHR           saloon
W1           LHR           VIP
W1           LHR           EXECUTIVE
W2           LHR           saloon
W2           LHR           VIP
W2           LHR           EXECUTIVE
W2           LGW           saloon
W1           LGW           VIP
W1           LGW           EXECUTIVE
W2           LGW           saloon
W2           LGW           VIP
W2           LGW           EXECUTIVE

How should I write this query? I tried cross join, but I did not got exactly what I want.

Answer 1

It seems that you want all the pairs where one item comes from table 1 and the other item comes from table 2, in either order. You can do this by creating two cross joins and taking the UNION:

(
    SELECT
        Title1 AS title-common,
        Title2 AS title-common2,
        vehicle
    FROM table1
    CROSS JOIN table2
    CROSS JOIN table3
)
UNION
(
    SELECT
        Title2 AS title-common,
        Title1 AS title-common2,
        vehicle
    FROM table1
    CROSS JOIN table2
    CROSS JOIN table3
)