How to group a view by the tables from which it gathers its data? I have four different tables, let's say "LAMPS", "CARS", "DRONES" and "OVENS". Each of these tables has a column called "STATUS", which has a value of either 'ON' or 'OFF'. I want to create a view that show the data from all these four tables.

I then want to filter this view by the "STATUS" column. I am interested in only seeing the entries with the 'OFF' value. 

Finally, I want to group the results by the table from which it came. I'm thinking of creating a new column in the view that would get the original_table_name for each of the rows and group by that value.

+------------+------------+-----------------+--------+
| TABLE_NAME |  SOMECOL1  |    SOMECOL2     | STATUS |
+------------+------------+-----------------+--------+
| LAMPS      | somevalue1 | someothervalue1 | OFF    |
| CARS       | somevalue2 | someothervalue2 | OFF    |
+------------+------------+-----------------+--------+


This is what I have so far:

CREATE VIEW my_view 
  AS SELECT * FROM LAMPS l, CARS c, DRONES d, OVENS s WHERE l.STATUS = 'OFF' OR c.STATUS = 'OFF' OR d.STATUS = 'OFF' OR s.STATUS = 'OFF';


Without modifying any of the original tables, how can I group a multi-table view by the tables from which it gathers its results?
Maybe you need

CREATE VIEW my_view 
AS 
SELECT *, 'LAMPS' AS TABLE_NAME FROM LAMPS WHERE STATUS = 'OFF'
UNION ALL
SELECT *, 'CARS'                FROM CARS WHERE STATUS = 'OFF'
UNION ALL
SELECT *, 'DRONES'              FROM DRONES WHERE STATUS = 'OFF'
UNION ALL
SELECT *, 'OVENS'               FROM OVENS WHERE STATUS = 'OFF';


?