Rearranging joins

[Ofina]

I have a moderately complex report with lots of joins. In debugging my report, I took a look at the SQL representation. I'm not going to go into how it looks right now, but I would like to rearrange it such that the main table that is being drawn from is listed first and all the other are left outer joins from that one. As it stands, a side table is listed first with a right outer join to a bunch of nested joins.

Basically I would like to clean it up some and hopefully fix my bug in the process. From everything I can see, not that I'm an expert, it *should* work, but I've heard that left joins are more stable than right ones.

So, 1) How can I rearrange the tables in the join section to be cleaner, and 2) What determines their sequence in the first place?

Fiona

 

[lbass]

You can control the joins by going into database->database expert->linking tab and rearranging tables and changing joins there. Your left most table will end up being the first table in your query, but I can't predict the order of subsequent tables, as it depends upon whether you are linking to the same main table in a fan formation or linking tables linearly.

You are very likely in the wrong forum. Try forum149 or forum767 in the future.

-LB

 

[Ofina]

Well, I already thought of that one and it didn't change a thing. My main table (the one that I want all left outer joins off of) is most distinctly showing at the far left.

The table joins still show the minor table first, which incidentally is way over on the right now.

The other tables don't really matter to me much. It's mostly linear, but there is one link that is off that main line.

And, sorry if I put this in the wrong forum. I saw other debugging questions in this forum, so thought I had the right one.

Fiona

 

[lbass]

Please explain the tables and joins more specifically and then show us a copy of the SQL query. I have never had occasion to use a right outer join, and I think that join is why that table is appearing first, so I'm wondering whether there is a different approach to that particular join.

-LB

 

[Ofina]

No, the table isn't appearing first because it's a right outer join. It's a right outer join because the table is appearing first. I didn't have it as a right outer at first. And that's when I noticed that it was showing first in the joins, so I switched it to right thinking that that would solve my problem. But, it didn't.

Well, I have a table of shipment data and I have tables of customer and supplier info. The shipment data should be the main table with left joins to all the others, but a sub-table of the customer table is showing up first. The main problem is that sometimes the field that is being pulled from that sub-table is NULL and therefore it's not pulling in anything for that record. A right outer join is supposed to fix that, but it isn't.

I don't see how showing you the sql could possibly help as I don't need to know what's wrong with it. I only need to know how to control it since I can't go in and change it manually. But, here you go. I might just actually start over and make sure that it's doing it right at each stage.

SELECT oe_hdr.customer_id,
vessel_report.supplier_id,
vessel_report.vessel_name,
vessel_report.est_avail_ship_date,
vessel_report.arrival_date,
address.name,
address.mail_address1,
address.mail_address2,
address.mail_city,
address.mail_state,
address.mail_postal_code,
address.mail_country,
address.central_phone_number,
address.central_fax_number,
contacts.first_name,
contacts.last_name,
oe_hdr.ship2_name,
oe_hdr.ship2_add1,
oe_hdr.ship2_add2,
oe_hdr.ship2_city,
oe_hdr.ship2_state,
oe_hdr.ship2_zip,
oe_hdr.ship2_country,
oe_hdr.ship_to_phone,
class.class_type,
class.class_number,
class.class_description,
address_1.central_fax_number,
contacts_1.first_name,
contacts_1.last_name,
customer.customer_name,
supplier.supplier_name
FROM (((((class
RIGHT OUTER JOIN (((vessel_report
LEFT OUTER JOIN po_hdr ON vessel_report.po_no=po_hdr.po_no)
LEFT OUTER JOIN supplier ON vessel_report.supplier_id=supplier.supplier_id)
LEFT OUTER JOIN oe_hdr ON po_hdr.sales_order_number=oe_hdr.order_no) ON class.class_id=oe_hdr.class_2id)
LEFT OUTER JOIN address ON oe_hdr.carrier_id=address.id)
LEFT OUTER JOIN contacts ON oe_hdr.carrier_id=contacts.address_id)
LEFT OUTER JOIN address_1 ON oe_hdr.address_id=address_1.id)
LEFT OUTER JOIN contacts_1 ON oe_hdr.contact_id=contacts_1.id)
LEFT OUTER JOIN customer ON oe_hdr.customer_id=customer.customer_id
WHERE class.class_type='OE' AND class.class_number=2 AND (vessel_report.est_avail_ship_date>={ts '2008-10-01 00:00:00'} AND vessel_report.est_avail_ship_date<{ts '2008-10-01 00:00:01'}) AND vessel_report.vessel_name=VESSEL NAME AND customer.customer_name=CUSTOMER NAME AND supplier.supplier_name=SUPPLIER NAME

 

[lbass]

If you are using left outer joins because the tables to the right of these joins can be null, then you also cannot have selections on these tables (customer, supplier) or you will effectively undo the joins. You could handle this by adding the selection criteria to the joins in the 'from' clause instead of in the 'where' clause. This would entail creating the query as a command, and using that as your datasource.

Your selection on the class table is probably also contributing to the order of tables. You need a left outer join on the class table, not a right, if the class table key can be null. Your query would then look something like this:

SELECT oe_hdr.customer_id,
vessel_report.supplier_id,
vessel_report.vessel_name,
vessel_report.est_avail_ship_date,
vessel_report.arrival_date,
address.name,
address.mail_address1,
address.mail_address2,
address.mail_city,
address.mail_state,
address.mail_postal_code,
address.mail_country,
address.central_phone_number,
address.central_fax_number,
contacts.first_name,
contacts.last_name,
oe_hdr.ship2_name,
oe_hdr.ship2_add1,
oe_hdr.ship2_add2,
oe_hdr.ship2_city,
oe_hdr.ship2_state,
oe_hdr.ship2_zip,
oe_hdr.ship2_country,
oe_hdr.ship_to_phone,
class.class_type,
class.class_number,
class.class_description,
address_1.central_fax_number,
contacts_1.first_name,
contacts_1.last_name,
customer.customer_name,
supplier.supplier_name
FROM (((((((((vessel_report
LEFT OUTER JOIN po_hdr ON vessel_report.po_no=po_hdr.po_no and
(vessel_report.est_avail_ship_date>={ts '2008-10-01 00:00:00'} AND
vessel_report.est_avail_ship_date<{ts '2008-10-01 00:00:01'}) AND
vessel_report.vessel_name='{?VESSEL NAME}')

left outer join class on
ON class.class_id=oe_hdr.class_2id and
class.class_type='OE' AND
class.class_number=2)

LEFT OUTER JOIN supplier ON vessel_report.supplier_id=supplier.supplier_id AND supplier.supplier_name= '{?SUPPLIER NAME}')

LEFT OUTER JOIN oe_hdr ON po_hdr.sales_order_number=oe_hdr.order_no)

LEFT OUTER JOIN address ON oe_hdr.carrier_id=address.id)

LEFT OUTER JOIN contacts ON oe_hdr.carrier_id=contacts.address_id)

LEFT OUTER JOIN address_1 ON oe_hdr.address_id=address_1.id)

LEFT OUTER JOIN contacts_1 ON oe_hdr.contact_id=contacts_1.id)

LEFT OUTER JOIN customer ON oe_hdr.customer_id=customer.customer_id AND customer.customer_name= '{?CUSTOMER NAME}')

I'm assuming the name fields are based on parameters. You would create them in the command. You might need to add additional parens around the selection criteria within the joins, depending upon your datasource.

-LB

 

[Ofina]

Well, you did clue me in to what was wrong. I noticed, while looking into what you were telling me, that my selection on class type and class number were redundant anyway. It was taken care of by how I was linking in to that table in the first place. I realize now that if those are null and they are selection criteria (not to be confused with parameters, which I initially did upon reading your message) that it will not bring anything in, as you said.

It is now giving me data even when there is no class assigned. So thank you.

Even though my original question wasn't answered (how do I rearrange the tables in the join statement...but I know now that that wouldn't have solved my problem anyway), my problem is now gone by taking out that selection and making the join right outer. By the way, taking out the selection did nothing to the order of tables in the join statement. Class is still first.

Thanks.

Fiona

 

[Ofina]

I just chanced upon the answer to my original question, if anyone is at all interested.

You can rearrange the tables in the join statement depending on how you drag your join. If you drag from table 1 to table 2, then table 1 is on the "left". If you drag the other way, then table 2 is on the "left".