I need some help with a database view. I'm looking to pull together three tables to create a view that I can use like a standard table. I have a couple reference fields that I need to use to dig out fields from those referenced tables. Here's a quick table def:
Table: u_customer_accounts
-------------------------------------
u_id = Number
u_name = string
u_mpf = Reference to u_mpf
u_reportsto = reference to sys_user
Table: u_mpf
----------------
u_id = Number
u_itar = boolean
u_name = String
I would like to create a view that gets all records from u_customer_accounts along with "name" from sys_user and the value of "u_itar" flag from u_mpf. I've created a simple test that can get u_itar from u_mpf, but I cannot seem to get sys_user in to the picture yet.
The funny thing is, I could do this with a SQL statement, but I cannot seem to get it with the Database Views builder. In MySQL it would go something like this:
select u_customer_accounts.u_id,
u_customer_accounts.u_name,
u_mpf.u_name,
u_mpf.u_itar,
sys_user.location,
from
u_customer_accounts,u_mpf,sys_user.location
where
u_customer_accounts.u_mpf=u_mpf.sys_id and
u_customer_accounts.u_reportsto=sys_user.sys_id
This isn't hard, just a learning curve that I can't seem to breach (yet).
