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:
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..
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
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.