Re: OUTER issue - please help
On 30 Nov 2005 08:32:10 -0800, thelinuxguy1997@yahoo.com wrote: > The following query returns values for sd, sd1, and sd2 records ONLY if > there are records in the database. If at least one record is not the > the database then the query returns empty values even for those records > that ARE in the database. > Could please tell me what it is wrong here? > Thanks in advance for any help. > Sil > > SELECT > so.*, sp.*, ep.*, cu.*, sd.*, sd1.*, sd2.*, q.* > FROM svc_order so, svc_package sp, evc_package ep, > OUTER(pkg_customer_map pc, customer cu, svc_order_de > tail sd, svc_order_detail sd1, svc_order_detail sd2, queue q, step s, > step_instance si, tm_instance ti) > WHERE > sp.svc_package_id in (SELECT distinct sp.svc_package_id FROM > svc_package sp WHERE sp.name matches '*' > ) > AND sp.service_tp_id in (select service_tp_id from service_tp WHERE > service_spec_id = 401) > AND so.svc_package_id = sp.svc_package_id > AND so.order_num matches 'TEST0000902' > AND q.queue_id = s.queue_id > AND s.step_id = si.step_id > AND si.tm_instance_id = ti.tm_instance_id > AND si.step_inst_id = (SELECT MAX(step_inst_id) FROM step_instance > WHERE tm_instance_id = ti.tm_instan > ce_id) > AND ti.tm_instance_id = so.tm_instance_id > AND ep.name = sp.name > AND pc.svc_package_id = so.svc_package_id > AND cu.cust_id = pc.cust_id > AND sd.svc_order_id = so.svc_order_id > AND sd.name = 'BANDWIDTH' > AND sd1.svc_order_id = so.svc_order_id > AND sd1.name = 'RT_BANDWIDTH' > AND sd2.svc_order_id = so.svc_order_id > AND sd2.name = 'PD_BANDWIDTH'; The trouble is your join structure in the WHERE clause does not match the join structure in the FROM clause. Pulling equality join conditions (only) out of the query, you are joining the following pairs of table aliases: so <--> sp q <--> s s <--> si si <--> ti ti <--> so ep <--> sp pc <--> so cu <--> pc sd <--> so sd1 <--> so sd2 <--> so Your FROM clause structure is: FROM so, sp, ep, OUTER(pc, cu, sd, sd1, sd2, q, s, si, ti) To be a sensible query, you need to have the tables listed in the OUTER join all joined to each other, and then the result joined to the three dominant tables. You probably need to use the FROM clause like: FROM so, sp, ep, OUTER sd, OUTER sd1, OUTER sd2, OUTER(ti, si, s, q), OUTER(pc, cu) Depending on your real requirements, you might need extra outer joins inside the parenthesized lists of joins - for example, OUTER(pc, OUTER cu); you haven't given us anywhere near enough information to answer that for you. Try reading the article on classic Informix outer joins at http://www.savage.net.au/SQL -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/ sending to informix-list