How to create a View with rows from multiple tables with different columns?

Question

How to create a View with rows from multiple tables with different columns?

sg2000-429020

Ten Centuries

Points: 1106
More actions
May 6, 2008 at 9:25 am

#207110

I have three tables with the following column names:
TabA: aID, col, acol1, acol2
TabB: bID, col, bcol1
TabC: cID, col, ccol1
The columns "aID", "bID" and "cID" are the primary key column; column "col" has the same name and type for all three tables, columns "acol1", "bcol1" and "ccol1" have the same type for all three tables and the column acol2 is only present in TabA.
I would like to create a view that contains all rows from the three tables in which each rows will have the following columns:
ViewABC: From, ID, col, col1, acol2
Depending on where a row in the View come from; its "From" column will contain "A", "B" or "C" ; its "ID" column will contain the value of aID, bID or cID column; its "col1" column will contain the value of acol1, bcol1 or ccol1 column; and its "acol2" column will contain the value from acols column of TabA only.
For example, with the following rows:
TabA: 1, aaaa1, a1a1a1, x1x1x1
TabA: 2, aaaa2, a2a2a2, x2x2x2
TabB: 1, bbbb1, b1b1b1
TabC: 1, cccc1, c1c1c1
The ViewABC ahould have the followng rows:
"A", 1, aaaa1, a1a1a1, x1x1x1
"A", 2, aaaa2, a2a2a2, x2x2x2
"B": 1, bbbb1, b1b1b1, (null)
"C": 1, cccc1, c1c1c1, (null)
Any idea how this can be accomplished?
Appreciate any suggestions. Thanks in advance.
sg2000

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 1

I'mn not sure you can do this. There isn't a way to necessarily determine which table a column is coming from. Unless you have a way of determining which value is in the table.

John Rowan SSC Guru Points: 56440 More actions · Answer 2

SELECT 'A',

aID,

Col,

aBol1,

aBol2

FROM TabA

UNION ALL

SELECT 'B',

bID,

Col,

bCol1,

NULL

FROM TabB

UNION ALL

SELECT 'C',

cID,

Col,

cCol1,

NULL

FROM TabC

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

sg2000-429020 Ten Centuries Points: 1106 More actions · Answer 3

Steve and John:

Thanks both of you for the quick response, and also to John for the nice solution which works good enough for me.

However, I am still trying to find a way of combining the columns aID, bID and cID into one column called ID in the View (since these are all the primary keys).

Any ideas?

sg2000

GSquared SSC Guru Points: 260824 More actions · Answer 4

Put a column alias in there.

SELECT 'A' as [Table],

aID as ID,

Col,

aCol1,

aCol2

FROM TabA

UNION ALL

SELECT 'B',

bID,

Col,

bCol1,

NULL

FROM TabB

UNION ALL

SELECT 'C',

cID,

Col,

cCol1,

NULL

FROM TabC

That will name the first column "Table" and the second column "ID".

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

sg2000-429020 Ten Centuries Points: 1106 More actions · Answer 5

GSquared, thank you very much for the suggestion. Yes, it works perfectly now.

sg2000