SQL: Combining results

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 th开发者_StackOverflowey 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..

---

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%'

---

If all three tables has the same number of columns you can use UNION ALL:

SELECT * FROM `restaurants` WHERE name LIKE '%string%'
UNION ALL
SELECT * FROM `restaurants` WHERE address LIKE '%string%'
UNION ALL
SELECT * FROM `menu_items` WHERE name LIKE '%string%'

!!!Important:

The column names from the first SELECT statement are used as the column names for the results returned. Selected columns listed in corresponding positions of each SELECT statement should have the same data type. (For example, the first column selected by the first statement should have the same type as the first column selected by the other statements.)

If the data types of corresponding SELECT columns do not match, the types and lengths of the columns in the UNION result take into account the values retrieved by all of the SELECT statements.

For more details with examples see official MySQL Documentation on UNION statement

EDIT: Update

If menu_items table has FK on restaraunts table you can join both tables and select consolidated output data, for instance:

SELECT r.*, mi.*
FROM restaurants r 
INNER JOIN menu m ON m.R_ID = r.ID
INNER JOIN menu_items mi ON m.ID = mi.M_ID
WHERE r.name LIKE '%string%' 
  AND r.address LIKE '%string%'
  AND mi.name LIKE '%string%'