CODEWITHSUNDEEP
sqlsql-servert-sql
Code Grasshopper
Code Grasshopper

Reputation: 620

Creating a table or view from one field in other tables

I know this may be quite simple, but its very late and for the life of me I cannot remember how to do it.

I have four tables, A,B and C. Each one has an ID, Name and some other columns.

What I need is a table E that has ID, Name_A, Name_B and Name_C . So if I add another name to say B, it will update this new E table to contain it as well.

I tried creating a view:

Create View VwNames
as
SELECT        dbo.TableA.name AS NameA, 
              dbo.TableB.name AS NameB, 
              dbo.TableC.name AS NameC

FROM          dbo.TableA, 
              dbo.TableB, 
              dbo.TableC

But I was getting a view with repeated names, for example

Juan   Pedro  Mario
Juan   Mario  Pedro
Mario  Juan   Pedro
Mario  Pedro  Juan
Pedro  Juan   Mario
Pedro  Mario  Juan

What I need is something that shows me:

Juan
Mario
Pedro

Or 

Juan Mario Pedro

Any help would be really appreciated I've been searching for hours now.

Thanks a lot in advance

EDIT:

How my views is looking as of now:

Current View

Upvotes: 0

Views: 23

Answers (3)

Recursive
Recursive

Reputation: 952

Create View VwNames
as
SELECT        distinct a.name AS NameA, 
              b.name AS NameB, 
              c.name AS NameC

FROM          dbo.TableA a join
              dbo.TableB b 
            on b.id=a.id
            join 
              dbo.Table c
            on c.id=b.id

Upvotes: 1

Arion
Arion

Reputation: 31239

You could use a union. Like this:

SELECT dbo.TableA.name FROM dbo.TableA UNION
SELECT dbo.TableB.name FROM dbo.TableB UNION
SELECT dbo.TableC.name FROM dbo.TableC

This will get you to output:

Juan
Mario
Pedro

Upvotes: 1

Dgan
Dgan

Reputation: 10285

TRY LIKE THIS :

Create View VwNames
as

SELECT    distinct    dbo.TableA.name AS NameA, 
              dbo.TableB.name AS NameB, 
              dbo.TableC.name AS NameC

FROM          dbo.TableA TA LEFT JOIN
              ON TA.col1=TB.col1
              dbo.TableB TB LEFT JOIN
              ON TB.col1=T.col1
              dbo.Table T

Upvotes: 1

Related Questions