1

I need to create a view with a column that indicates which table does the row come from.

I'm using SQL Server 2005 Express

To be more specific, I have a database with a table called users, a table called developers and a separate one containing bank account data. I need a view that shows the bank account information of both users and developers, and an extra column that states if the row comes from a user or a developer.

I have this:

CREATE VIEW bankDataView
AS

SELECT accountinfo, name, lastname, devcode as id
FROM developer d INNER JOIN bankdata bd ON
d.bankdataID = bd.bankDataID
UNION
SELECT accountinfo, name, lastname, userID as id
FROM user u INNER JOIN bankdata bd ON
u.bankdataID = bd.bankdataID

But now I need to know how to get that extra column.

Is this possible, or should I consider an alternative approach?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
cangrejo
  • 2,189
  • 3
  • 24
  • 31

2 Answers2

4

just add an extra column to each select statement saying which table the data comes from. Then you can easily distinguish between the developers and users.

CREATE VIEW bankDataView
AS

SELECT accountinfo, name, lastname, devcode as id, 'Developer' as TableName
FROM developer d 
INNER JOIN bankdata bd 
    ON d.bankdataID = bd.bankDataID
UNION
SELECT accountinfo, name, lastname, userID as id, 'User' as TableName
FROM user u 
INNER JOIN bankdata bd 
    ON u.bankdataID = bd.bankdataID
Taryn
  • 242,637
  • 56
  • 362
  • 405
0

bluefeet is logically correct, but if you use UNION ALL instead of plain UNION performance will be better. Plain UNION does extra work of eliminating duplicate results form the union. There going to be no duplicate results thanks to the new TableName column

satcat66
  • 116
  • 3