0

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?

  • Please show us the SQL you have so far so we can see where you got stuck. – Colin 't Hart Aug 29 '18 at 7:42
  • Thanks for your comment. I have added what you requested. – edoreld Aug 29 '18 at 7:47
0

This is how I usually do that: view is UNION (ALL) of all my tables. I include an origin column which shows where those rows come from.

I wouldn't restrict rows to status = off at this stage; tomorrow you'll want to check ons so - what, will you recreate the view? Let it be.

create or replace view my_view as
  select 'L' origin, l.id, l.name, l.status, ... from lamps l
  union all
  select 'C' origin, c.id, c.name, c.status, ... from cars c
  union all
  select 'D' origin, d.id, d.name, d.status, ... from drones d
  union all
  select 'O' origin, o.id, o.name, o.status, ... from ovens o;

Then, it is a matter of a simple counting:

select v.origin, count(*) cnt
from my_view v
where v.status = 'OFF'
group by v.origin;
  • Author do not need count, he need a separate fields values. In those case group by v.origin will cause the error. With a message like "field ... is not a member of GROUP BY expression or an argument of aggregate function". – Akina Aug 29 '18 at 8:01
  • The OP has said: "I want to group the results by the table from which it came" - I don't know what kind of a group is it. Counting rows per groups is one kind of that "grouping". This is just an example, and - in that example - error you mentioned wouldn't be raised. – Littlefoot Aug 29 '18 at 8:06
  • Maybe the author has not clearly formulated the problem, and you are right. But, as shown in example output he want to obtain some separate fields named SOMECOL1 and SOMECOL2, so it looks like he want to obtain the grouping in output (i.e. sorting). – Akina Aug 29 '18 at 8:21
1

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

?

0
SELECT * FROM (SELECT 'LAMPS', SOMECOL1, SOMECOL2, status FROM LAMPS
UNION ALL
SELECT 'CARS', SOMECOL1, SOMECOL2, status FROM CARS
UNION ALL
SELECT 'DRONES', SOMECOL1, SOMECOL2, status FROM DRONES
UNION ALL
SELECT 'OVENS', SOMECOL1, SOMECOL2, status FROM OVENS) as temp WHERE status = 'OFF'

OR

SELECT * FROM (SELECT 'LAMPS', SOMECOL1, SOMECOL2, status FROM LAMPS WHERE status = 'OFF'
UNION ALL
SELECT 'CARS', SOMECOL1, SOMECOL2, status FROM CARS WHERE status = 'OFF'
UNION ALL
SELECT 'DRONES', SOMECOL1, SOMECOL2, status FROM DRONES WHERE status = 'OFF'
UNION ALL
SELECT 'OVENS', SOMECOL1, SOMECOL2, status FROM OVENS WHERE status = 'OFF') as temp 
  • 1st query may be expensive (especially when % of records with 'OFF' status is low). In the 2nd query the additional external wrapper is excessive. – Akina Aug 29 '18 at 8:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.