Karem Karem - 5 months ago 7
SQL Question

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

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