Andy Andy - 2 months ago 6
SQL Question

Create view for two tables that have similar column

I have 2 tables.


  • tableOne
    has columns
    Id, SN, SyN, SSN, APU



Here are 3 example rows:

Id SN SyN SSN APU
----------------------------
'1' 'a' 'a' 'a' 'a'
'2' 'b' 'b' 'b' 'b'
'3' 'c' 'c' 'c' 'c'



  • tableTwo
    has columns
    Id, Uri, MAM



Sample data:

Id Uri MAM
--------------------
'4' 'aa' 'aa'
'5' 'bb' 'bb'


I'm trying to create a view that will use the Id columns into one column and have a view like so:

Header:

Id | SN | SyN | SSN | APU | Uri | MAM
--------------------------------------------
1 | a | a | a | a | null | null
2 | b | b | b | b | null | null
3 | c | c | c | c | null | null
4 | null | null | null | null | aa | aa
5 | null | null | null | null | bb | bb


Here's what I have:

SELECT *
FROM CIR.BusinessApplication AS BA
FULL OUTER JOIN CIR.NetworkAddressableDevice AS NAD
ON BA.Id = NAD.Id


If the code is run, it basically places the two tables side by side. I also tried "CREATE VIEW CIR_REPORT AS" and the code and I was receiving a few errors.

Answer

You won't be able to create a view with SELECT * since the tables both have a field named ID, you can use COALESCE() for the ID field since both aren't always populated, then just list out the other fields:

SELECT COALESCE(BA.ID,NAD.ID) AS ID
      ,SN ,SyN ,SSN ,APU ,Uri ,MAM
FROM CIR.BusinessApplication AS BA
FULL OUTER JOIN CIR.NetworkAddressableDevice AS NAD
  ON BA.Id = NAD.Id 

That's the only error that jumps out, if there are others please add them to your question.