This is a simple creating-view-on-fly example:
Select MyView.CustomerID, MyView.OrderID, Customer.ZipCode
from
Customer,
(Select CustomerID, OrderID, SKU from Orders) MyView
where
Customer.CustomerID = MyView.CustomerID;
This is a complicated one, with the use of “left join”.
“left join” ensures that list all the elements found in the left table, include the elements that don’t have a match from the right table.
SELECT mv.id, mv.TYPE, mv.description, mv.pegasus_param_valud_id, mv.uom,
tbhch.pcst_master_param_pegasus.pegasus_table,tbhch.pcst_master_param_pegasus.pegasus_special_case
from
(SELECT tbhch.pcst_master_param_detail.id, tbhch.pcst_master_param_detail.TYPE,
tbhch.pcst_master_param_detail.description, tbhch.pcst_master_param_detail.pegasus_param_valud_id,
tbhch.pcst_master_uom.description AS uom
FROM tbhch.pcst_master_param_detail left join tbhch.pcst_master_uom
ON tbhch.pcst_master_param_detail.uom_id=tbhch.pcst_master_uom.id) mv
left join tbhch.pcst_master_param_pegasus
ON mv.pegasus_param_valud_id=tbhch.pcst_master_param_pegasus.pegasus_param_value_id
Parameter all properties:
SELECT mvvv.*, tbhch.pcst_master_tab.description AS tab from
(SELECT mvv.*, tbhch.pcst_master_param_codetype.tab_id from
(SELECT mv.id, mv.TYPE, mv.description, mv.pegasus_param_valud_id AS pegaid, mv.uom,
tbhch.pcst_master_param_pegasus.pegasus_table,
tbhch.pcst_master_param_pegasus.pegasus_special_case AS pegasus_label,
tbhch.pcst_master_param_pegasus.pegasus_value_column AS pegacol
from
(SELECT tbhch.pcst_master_param_detail.id, tbhch.pcst_master_param_detail.TYPE,
tbhch.pcst_master_param_detail.description, tbhch.pcst_master_param_detail.pegasus_param_valud_id,
tbhch.pcst_master_uom.description AS uom
FROM tbhch.pcst_master_param_detail left join tbhch.pcst_master_uom
ON tbhch.pcst_master_param_detail.uom_id=tbhch.pcst_master_uom.id) mv
left join tbhch.pcst_master_param_pegasus
ON mv.pegasus_param_valud_id=tbhch.pcst_master_param_pegasus.pegasus_param_value_id) mvv
left join tbhch.pcst_master_param_codetype
ON mvv.id=tbhch.pcst_master_param_codetype.param_id) mvvv
left join tbhch.pcst_master_tab
ON mvvv.tab_id=tbhch.pcst_master_tab.id
