OUTER join problem

HI I have select statement with 7 outer joins, like result i am receiving 5 rows, should be only one. What is good strategy / tools to find out why Dusan

Dusan wrote: > > HI > I have select statement with 7 outer joins, like result i am receiving 5 rows, > should be only one. What is good strategy / tools to find out why > Dusan Large economy sized bottle of ibuprofen! Build the query one table at a time and see when the results go out of expectations. You might also post it here and perhaps someone will spot something. Art S. Kagel

OK here is select statement SELECT applicant.applicantid, applicant.lastname, applicant.firstname, applicant.status, applicant.ssn_sin, client.name, client.creditapp, site.client_id, site.site_name, site.site_id, branch.region_desc, division.division_desc, sales_rep.salesrep, coordinator.coordinator, serviceline.serviceline_desc, stiwhser.description, contact.contact_last_name, contact.contact_first_name, contact.phone, contact.phone_ext, contact.fax, orders.po_required, orders.po_num, last_act.last_user, last_act.last_date, last_act.last_action, assignment.order_id, assignment.status, assignment.type, assignment.end_reason, assignment.contact_id, assignment.response_date, assignment.start_date, assignment.est_end_date, assignment.act_end_date, assignment.rpt_title, site.address_1, site.address_2, assignment.rpt_lastname, assignment.rpt_firstname, assignment.rpt_phone, assignment.rpt_phone_ext, assignment.start_hour, assignment.end_hour, assignment.pay_method, assignment.pay_rate, assignment.pay_rate_type, assignment.bill_rate, assignment.bill_rate_type, assignment.dept, assignment.split, assignment.consignee, assignment.pay_office, assignment.clearance, assignment.experience, assignment.language, assignment.notes, assignment.environment, assignment.fit_check, assignment.fit_check_complete, assignment.fit_check_date, assignment.result, assignment.emp_eval_req, assignment.eval_sent_date, assignment.eval_format, assignment.eval_complete, assignment.eval_rec_date, assignment.eval_comments, assignment.followup_req, assignment.followup_date, orders.order_id, assignment.division_id, assignment.amount, assignment.orderupdated, assignment.job_title, payroll.pref_comm, assignment.assignment_id, assignment.assignment_id FROM assignment, orders, client, site, division, branch, OUTER(serviceline), OUTER(applicant), OUTER(payroll), OUTER(stiwhser), OUTER(coordinator), OUTER(sales_rep), OUTER(contact), OUTER(ass_last, last_act) WHERE assignment.order_id =264 AND assignment.division_id = division.division_id AND division.region_id = branch.region_id AND assignment.order_id = orders.order_id AND assignment.site_id = site.site_id AND site.client_id = client.client_id AND assignment.serviceline_id = serviceline.serviceline_id AND assignment.applicantid = applicant.applicantid AND assignment.applicantid = payroll.applicantid ANDassignment.job_title_id = stiwhser.id AND assignment.salesrep_id = sales_rep.salesrep_id AND assignment.contact_id = contact.contact_id AND assignment.coordinator_id = coordinator.coordinator_id AND assignment.assignment_id = ass_last.assignment_id AND ass_last.last_id = (SELECT MAX(A.last_id) FROM ass_last A WHERE A.assignment_id = assignment.assignment_id) AND ass_last.last_id = last_act.last_id AND (orders.order_type LIKE 'TEMP%') what I found is that if I delete from payroll it is working fine, but that is confusing becouse I have no rows in payroll for this order_id Art S. Kagel wrote in message <3911E45C.272868D@bloomberg.net>... >Dusan wrote: >> >> HI >> I have select statement with 7 outer joins, like result i am receiving 5 rows, >> should be only one. What is good strategy / tools to find out why >> Dusan > >Large economy sized bottle of ibuprofen! Build the query one table at a >time and see when the results go out of expectations. You might also post >it here and perhaps someone will spot something. > >Art S. Kagel

I went ahead and reformated your select to this. SELECT (Lots of columns) FROM assignment, orders, client, site, division, branch, OUTER(serviceline), OUTER(applicant), OUTER(payroll), OUTER(stiwhser), OUTER(coordinator), OUTER(sales_rep), OUTER(contact), OUTER(ass_last, last_act) WHERE assignment.order_id =264 AND assignment.division_id = division.division_id AND division.region_id = branch.region_id AND assignment.order_id = orders.order_id AND assignment.site_id = site.site_id AND site.client_id = client.client_id AND assignment.serviceline_id = serviceline.serviceline_id AND assignment.applicantid = applicant.applicantid AND assignment.applicantid = payroll.applicantid AND assignment.job_title_id = stiwhser.id AND assignment.salesrep_id = sales_rep.salesrep_id AND assignment.contact_id = contact.contact_id AND assignment.coordinator_id = coordinator.coordinator_id AND assignment.assignment_id = ass_last.assignment_id AND ass_last.last_id = (SELECT MAX(A.last_id) FROM ass_last A WHERE A.assignment_id = assignment.assignment_id) AND ass_last.last_id = last_act.last_id AND (orders.order_type LIKE 'TEMP%') What is the primary key of payroll? You are only joining with the applicantid, and it sounds like maybe there should be more columns needed for the join to that table. Will In article <OTBQ4.45$dv6.597@client>, "Dusan Mijavec" <dmijavec@bradson.com> wrote: > OK > here is select statement > > SELECT applicant.applicantid, applicant.lastname, applicant.firstname, > applicant.status, applicant.ssn_sin, client.name, client.creditapp, > site.client_id, site.site_name, site.site_id, branch.region_desc, > division.division_desc, sales_rep.salesrep, coordinator.coordinator, > serviceline.serviceline_desc, stiwhser.description, > contact.contact_last_name, contact.contact_first_name, contact.phone, > contact.phone_ext, contact.fax, orders.po_required, orders.po_num, > last_act.last_user, last_act.last_date, last_act.last_action, > assignment.order_id, assignment.status, assignment.type, > assignment.end_reason, assignment.contact_id, assignment.response_date, > assignment.start_date, assignment.est_end_date, assignment.act_end_date, > assignment.rpt_title, site.address_1, site.address_2, > assignment.rpt_lastname, assignment.rpt_firstname, assignment.rpt_phone, > assignment.rpt_phone_ext, assignment.start_hour, assignment.end_hour, > assignment.pay_method, assignment.pay_rate, assignment.pay_rate_type, > assignment.bill_rate, assignment.bill_rate_type, assignment.dept, > assignment.split, assignment.consignee, assignment.pay_office, > assignment.clearance, assignment.experience, assignment.language, > assignment.notes, assignment.environment, assignment.fit_check, > assignment.fit_check_complete, assignment.fit_check_date, > assignment.result, assignment.emp_eval_req, assignment.eval_sent_date, > assignment.eval_format, assignment.eval_complete, > assignment.eval_rec_date, assignment.eval_comments, > assignment.followup_req, assignment.followup_date, orders.order_id, > assignment.division_id, assignment.amount, assignment.orderupdated, > assignment.job_title, payroll.pref_comm, assignment.assignment_id, > assignment.assignment_id FROM assignment, orders, client, site, > division, > branch, OUTER(serviceline), OUTER(applicant), OUTER(payroll), > OUTER(stiwhser), OUTER(coordinator), OUTER(sales_rep), OUTER(contact), > OUTER(ass_last, last_act) WHERE assignment.order_id =264 AND > assignment.division_id = division.division_id AND division.region_id = > branch.region_id AND assignment.order_id = orders.order_id AND > assignment.site_id = site.site_id AND site.client_id = client.client_id > AND assignment.serviceline_id = serviceline.serviceline_id AND > assignment.applicantid = applicant.applicantid AND > assignment.applicantid = payroll.applicantid AND> assignment.job_title_id > = stiwhser.id AND assignment.salesrep_id = sales_rep.salesrep_id AND > assignment.contact_id = contact.contact_id AND > assignment.coordinator_id > = coordinator.coordinator_id AND assignment.assignment_id = > ass_last.assignment_id AND ass_last.last_id = (SELECT MAX(A.last_id) > FROM ass_last A WHERE A.assignment_id = assignment.assignment_id) AND > ass_last.last_id = last_act.last_id AND (orders.order_type LIKE > 'TEMP%') > > what I found is that if I delete from payroll it is working fine, but that > is confusing becouse I have no rows in > payroll for this order_id > > Art S. Kagel wrote in message <3911E45C.272868D@bloomberg.net>... > >Dusan wrote: > >> > >> HI > >> I have select statement with 7 outer joins, like result i am receiving 5 > rows, > >> should be only one. What is good strategy / tools to find out why > >> Dusan > > > >Large economy sized bottle of ibuprofen! Build the query one table at a > >time and see when the results go out of expectations. You might also post > >it here and perhaps someone will spot something. > > > >Art S. Kagel > > Sent via Deja.com http://www.deja.com/ Before you buy.