Geek asks and answers

Adding an additional column for viewing that is not in the table

I want to create a view with a join of three tables. But as a result of the query, I want one additional column, for example "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 the join of the required three tables. But how can I get the table id column in the given release? This column is missing from any of the three tables.

+7
source share
7 answers

Looks like you just want to add extra string coding to the list SELECT

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
);
+12
source 
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
);
+5
source

?

, , :

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
0

There is a better solution for this;

NULL AS COLUMN_NAME is an identifier.

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

What have I done for the same.

  • open phpmyadmin.
  • select a database.
  • click on sql menu
  • run the following command: -
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) ;

It worked for me.

0
source

I made this decision and want to use a new field to exchange data between two columns, but the created field has a constant value of zero, so when I want to change the data of this field, I get an error, so what should I do? thank

0
source 
create or replace view view1(col1,col2,col3) as select col1,col2,nul
-2
source

More articles:


All Articles