两个不相关的表，具有匹配和反对功能

Question

How do I combine two unrelated tables into one SQL select statement request. However, both tables need to have the Match and Against functions for full text search. I'm getting a blank response and when I do a single table SQL match and against request it works fine but not when I do two tables.

Table One: transport - only id is a primary key integer auto_increment but the rest are varchar

    +----+---------+-----------+--------------+
    | id | title   | type      | tags         |
    +----+---------+-----------+--------------+
    | 1  | triumph | motorbike | sport, black |
    +----+---------+-----------+--------------+
    | 2  | bmw     | car       | hatchback    |
    +----+---------+-----------+--------------+

Table Two: automobile - - only id is a primary key integer auto_increment but the rest are varchar

    +----+-----------+-----------+------------+---------+
    | id | name      | kind      | link       | listed  |
    +----+-----------+-----------+------------+---------+
    | 1  | suzuki    | motorbike | /bike/new/ | green   |
    +----+-----------+-----------+------------+---------+
    | 2  | volkwagan | car       | /car/new/  | limited |
    +----+-----------+-----------+------------+---------+

I need it to print out something like this (just a note, not sure how I would need id - perhaps two columns id_automobile and id_transport to references both above tables)

    +----+-----------+-----------+------------+--------------+
    | id | title     | type      | link       | tags         |
    +----+-----------+-----------+------------+--------------+
    | 1  | suzuki    | motorbike | /bike/new/ | green        |
    +----+-----------+-----------+------------+--------------+
    | 2  | triumph   | motorbike |            | sport, black |
    +----+-----------+-----------+------------+--------------+
    | 3  | bmw       | car       |            | hatchback    |
    +----+-----------+-----------+------------+--------------+
    | 4  | volkwagan | car       | /car/new/  | limited      |
    +----+-----------+-----------+------------+--------------+

My failed attempt:

    <table>
      <tr>
        <th>Title</th>
        <th>Type</th>
        <th>Link</th>
        <th>Tags</th>
      </tr>

    if(isset($_GET['search'])) {
        $search = $_GET['search'];
    } else {
        $search = '';
    }

    $sql = "SELECT * FROM `transport` WHERE MATCH(title, tags) AGAINST('".$search."') CROSS JOIN `automobile` WHERE MATCH(name, listed) AGAINST('".$search."')";
    $result = $conn->query($sql);

    if ($result->num_rows > 0) {
        // output data of each row
        while($row = $result->fetch_assoc()) {
            echo "<tr>";
            echo "<td>".$row["title"]."</td><td>".$row["type"]."</td><td>".$row["link"]."</td><td>".$row["tags"]."</td>";
            echo "</tr>";
        }
    } else {
        echo "0 results";
    }
    $conn->close();


    </table>

I would appreciate any help please

Answer 1

A UNION clause should give you the results that you need.

Just make sure that bost SELECTs, combined with UNION, have similar column sets. You can use AS to define aliases.

Something like this:

SELECT title, type, '' AS link, tags
WHERE MATCH(title, tags) AGAINST('...')
FROM transport
UNION
SELECT name AS title, kind AS type, link, listed AS tags
FROM automobile
WHERE MATCH(title, kind) AGAINST('...')