We help IT Professionals succeed at work.

Need some help creating an SQL view to "normalize" a parent table and 3 child tables

Hi Team,.
   I am using Centos 5.5 with mySQL 5.0.77.  I have a requirement to run a report in Crystal Reports but the tables I have are difficult to "join" in Crystal Reports and seems that the easiest is to create a view in mySQL.  My table schemas are something like this: A Master ChangeRecord table and 3 "child" table that all have foreign keys pointing to the Changenumber field of the ChangeRecord (MASTER) table like so:

ChangeRecord table

Changenumber
ImplementationDate
Implementor
etc.
etc.

Firewalls

Changenumber
FirewallName
etc.
etc.

NetworkDevices

Changenumber
NetworkDeviceName
etc.
etc.

Servers
Changenumber
DeviceName
etc.
etc.

Open in new window


What I need is to create a view that has fields Changenumber, DeviceName and DeviceType where DeviceType is the name of the table the object came from and where the resulting view is  some kind of cross-join of the 3 "child" tables to their master..

Example:
Changenumber       DeviceName  DeviceType
Change0001         Firewall-A    Firewalls
Change0001         Firewall-B    Firewalls
Change0001         Firewall-C    Firewalls
Change0001         Firewall-D    Firewalls
Change0001         Router-A     NetworkDevices
Change0001         Router-B     NetworkDevices
Change0001         Router-C     NetworkDevices
Change0002         Server-A    Servers
Change0002         Server-B    Servers
Change0002         Server-C    Servers
Change0003         Server-D    Servers
Change0004         Server-E    Servers

Open in new window


It doesn't look too difficult to do, I just don't know how to setup the SQL construct.

Thanks very much for all the help.
Comment
Watch Question

Commented:
Create view ChangeNumberDevices
as

select ChangeRecord.changenumber, Firewalls.Firewallname ,"Firewall"
from ChangeRecord join Firewalls
on changerecord.changenumber = Firewalls.changenumber 

union

select ChangeRecord.changenumber, NetworkDevices.NetworkDeviceName,"Networkdevices"
from ChangeRecord join NetworkDevices
on changerecord.changenumber = NetworkDevices.changenumber 

union

select ChangeRecord.changenumber, Servers.DeviceName,"Servers"
from ChangeRecord join Servers
on changerecord.changenumber = Servers.changenumber

Open in new window

Author

Commented:
Hi dsine,

  Thanks for that! Seems to work fine.  We're almost there.  Just two things:

# 1: I ended up with a view where the column names are  misleading.  The data is correct but the column names seemed to have inherited the column name of the first tables like so:
ChangeNumber      Firewallname  Firewalls
Change0001         Firewall-A    Firewalls
Change0001         Firewall-B    Firewalls
Change0001         Firewall-C    Firewalls
Change0001         Firewall-D    Firewalls
Change0001         Router-A     NetworkDevices
Change0001         Router-B     NetworkDevices
Change0001         Router-C     NetworkDevices
Change0002         Server-A    Servers
Change0002         Server-B    Servers
Change0002         Server-C    Servers
Change0003         Server-D    Servers
Change0004         Server-E    Servers

Open in new window


The column name should be
Changenumber       DeviceName  DeviceType


#2.  I noticed that there are some ChangeRecords where there is neither a firewall, network device or router, but the ChangeRecord should still appear in the view.  I believe I need to do a "left outer join" of these tables?   In the case where there are no corresponding records in the children table, I should substitute NONE and Others in the 2nd and 3rd columns like this:

ChangeNumber      DeviceName     DeviceType
Change0001         Firewall-A    Firewalls
Change0001         Firewall-B    Firewalls
Change0001         Firewall-C    Firewalls
Change0001         Firewall-D    Firewalls
Change0001         Router-A     NetworkDevices
Change0001         Router-B     NetworkDevices
Change0001         Router-C     NetworkDevices
Change0002         Server-A    Servers
Change0002         Server-B    Servers
Change0002         Server-C    Servers
Change0003         Server-D    Servers
Change0004         Server-E    Servers
Change0005         None          Others
Change0007          None         Others

Open in new window


Many thanks again for the prompt help.
Commented:
Don't have access to a sql server but the change below should take care of both.
Create view ChangeNumberDevices
as

select ChangeRecord.changenumber Changenumber, Firewalls.Firewallname DeviceName,"Firewall" DeviceType
from ChangeRecord join Firewalls
on changerecord.changenumber = Firewalls.changenumber 

union

select ChangeRecord.changenumber, NetworkDevices.NetworkDeviceName,"Networkdevices"
from ChangeRecord join NetworkDevices
on changerecord.changenumber = NetworkDevices.changenumber 

union

select ChangeRecord.changenumber, Servers.DeviceName,"Servers"
from ChangeRecord join Servers
on changerecord.changenumber = Servers.changenumber

union

select changerecord.changenumber,"none", "Others"
from 
changerecord
where not exists (select 1 from Firewalls where Firewalls.changenumber=changerecord.changenumber)
and not exists (select 1 from NetworkDevices where NetworkDevices.changenumber=changerecord.changenumber)
and not exists (select 1 from Servers where Servers.changenumber=changerecord.changenumber)

Open in new window

Author

Commented:
Great!  Tested and everything seems to work as intended.

Thanks very much.