Adding extra column to view, which is not present in table

Question

I want to create view with union of three tables. But in result query I want one extra column like 'tableId'.

My code is like

CREATE OR REPLACE VIEW DETAILS
AS SELECT 
* FROM
(
SELECT 
    T1.ID,
    T1.AMOUNT,
    T1.STATUS,
    T1.ADDEDBY,
    T1.ADDEDON
FROM Table1 T1
UNION ALL
SELECT 
    T2.ID,
    T2.AMOUNT,
    T2.STATUS,
    T2.ADDEDBY,
    T2.ADDEDON
FROM Table2 T2
UNION ALL
SELECT
    T3.ID,
    T3.BILLAMOUNT,
    T3.STATUS,
    T3.ADDEDBY,
    T3.ADDEDON  
FROM Table3 T3
);

This gives me union of required three tables. But how can i get table Id column in resulted output? This column is not present in any of the three tables.

score 13 · Accepted Answer · answered Jul 23 '12 at 08:02

13

It sounds like you just want to add an additional hard-coded value to your SELECT list

CREATE OR REPLACE VIEW DETAILS
AS SELECT 
* FROM
(
SELECT 
    T1.ID,
    T1.AMOUNT,
    T1.STATUS,
    T1.ADDEDBY,
    T1.ADDEDON,
    'T1' tableID
FROM Table1 T1
UNION ALL
SELECT 
    T2.ID,
    T2.AMOUNT,
    T2.STATUS,
    T2.ADDEDBY,
    T2.ADDEDON,
    'T2' tableID
FROM Table2 T2
UNION ALL
SELECT
    T3.ID,
    T3.BILLAMOUNT,
    T3.STATUS,
    T3.ADDEDBY,
    T3.ADDEDON,
    'T3' tableID  
FROM Table3 T3
);

answered Jul 23 '12 at 08:02

Justin Cave

227,342
24
367
384

I am getting 'View's SELECT contains a subquery in the FROM clause' is this syntax specific to Oracle? I have mysql (running through phpmyadmin) thx – Awena Apr 01 '15 at 13:21
@Awena - The syntax should be ANSI standard (though the standard might require the inline view to be aliased). I have no idea, however, if MySQL supports it or what tweaks would be needed to make the query work on MySQL. – Justin Cave Apr 01 '15 at 14:36
1

@JustinCave I got it working by removing 'SELECT * FROM ()' and just including CREATE OR REPLACE VIEW DETAILS AS ...then here my three Selects/Unions.. perhaps this can help someone using phpmyadmin.. Thank you Justin for the snippet! – Awena Apr 01 '15 at 14:41

score 6 · Answer 2 · answered Jul 23 '12 at 08:02

CREATE OR REPLACE VIEW DETAILS
AS SELECT 
* FROM
(
SELECT 
    T1.ID,
    T1.AMOUNT,
    T1.STATUS,
    T1.ADDEDBY,
    T1.ADDEDON,
    'Table1' as tableid
FROM Table1 T1
UNION ALL
SELECT 
    T2.ID,
    T2.AMOUNT,
    T2.STATUS,
    T2.ADDEDBY,
    T2.ADDEDON,
    'Table2' as tableid
FROM Table2 T2
UNION ALL
SELECT
    T3.ID,
    T3.BILLAMOUNT as AMOUNT,
    T3.STATUS,
    T3.ADDEDBY,
    T3.ADDEDON,    
    'Table3' as tableid
FROM Table3 T3
);

score 0 · Answer 3 · answered Jul 23 '12 at 08:02

Where does the data for the column come from?

You can add completely static data if that is what you are looking for:

select 'hello there' from Table1;

create view v1 as select 123 as table_id, a_column from Table2;

select 'Table1' as table_id, a_column from Table1
union all
select 'Table2', a_column from Table2

score 0 · Answer 4 · answered Dec 16 '15 at 07:38

0

There is a better solution for this;

NULL AS COLUMN_NAME is an identifier to.

CREATE VIEW MY_VIEW
    AS
        (SELECT A.*,NULL AS COLUMN1,NULL AS COLUMN2,NULL AS COLUMN3
            FROM MY_TABLE A)

answered Dec 16 '15 at 07:38

hurricane

6,521
2
34
44

score 0 · Answer 5 · answered Aug 30 '17 at 11:11

What i did for the same.

open phpmyadmin.
select database.
click on sql menu
run following command as-

CREATE VIEW table_name AS SELECT tableA.id, tableA.name, tableA.duty_hours, tableA.rate, NULL AS 'additional_field1',NULL AS 'additional_field2', NULL AS 'additional_field3', tableB.name, tableB.email, tableB.charge, NULL AS 'additional_field4' FROM tableA join tableB using (id) ;

That worked for me.

score -2 · Answer 6 · edited Oct 27 '15 at 14:26

-2

create or replace view view1(col1,col2,col3) as select col1,col2,nul

edited Oct 27 '15 at 14:26

Artjom B.

61,146
24
125
222

answered Oct 27 '15 at 10:13

kanha senapati

1

Adding extra column to view, which is not present in table

6 Answers6

Linked