SQL: Combining results

Question

SELECT * FROM `restaurants` WHERE name LIKE '%string%'

SELECT * FROM `restaurants` WHERE address LIKE '%string%'

SELECT * FROM `menu_items` WHERE name LIKE '%string%'

I have these queries.

At the moment i show each query result seperatly. I would like to combine them all.

And all the results should have aliases with where they come from. Example when it show a item/row from menu_items you should be able to see in the column "Type" that it is a 'item'.

And 'address' for the restaurants address results and 'name' for the restaurants name results.

in MySQL

How can i do this?

My final output should look like this: You search after "s"

Spagetti - item
Sonus Suni - restaurant
Sunssisway 1232 - restaurant address
Delicous Spaga - item

So i would need to create my own alias column.. 'item' AS 'Type' etc..

Answer 1

If you want to combine tables with different columns, you have to specify which columns you want.

SELECT
    'Name' AS Type,
    Id AS R_Id,
    NULL AS I_Id,
    Name,
    Address,
    NULL AS M_Id
FROM Restaurants
WHERE Name LIKE '%string%'
UNION ALL
SELECT
    'Address' AS Type,
    Id AS R_Id,
    NULL AS I_Id,
    Name,
    Address,
    NULL AS M_Id
FROM Restaurants
WHERE Address LIKE '%string%'
UNION ALL
SELECT
    'Item' AS Type,
    NULL AS R_Id,
    Id AS I_Id,
    Name,
    NULL AS Address,
    M_Id
FROM Menu_Items
WHERE Name LIKE '%string%'