Using outer join in SQL query?

[Chris Dunning]

I'm trying to create a query that will select everything I need to know about my orders so I can create a CSV file and work with them. I'm running into a snag with the orders_total table. If an order has sales tax attached to it, I need to know how much the sales tax was. However, if an order did not have sales tax, I still need the rest of the information about the order. So I have something like this:

select 
<long list of columns> 
from 
orders as o,
?	orders_products as op,
?	orders_total as ot,
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?orders_total as ott,
?	orders_total as si,
?	orders_total as st

?	WHERE
?	o.orders_id=op.orders_id
?	AND
?	o.orders_id=ot.orders_id
?	AND
?	ott.orders_id = ot.orders_id
AND
si.orders_id=ott.orders_id
?	AND
?	ot.class = 'ot_shipping'
?	AND
?	ott.class = 'ot_total'
AND
si.class='ot_insurance'
?	AND
?	st.class ='ot_tax'
AND
?	st.orders_id = ot.orders_id;

 

This returns only 3 of my hundreds of orders - the ones that have sales tax. Removing the references to sales tax returns all orders, but without the sales tax information (obviously). A good friend of mine who does Oracle for a living suggested that I could use an "outer join," which is designated by a "(+)." Apparently in Oracle syntax, that means to make this particular value null and still return the row if it does not exist. I found this page on the mySQL site:http://dev.mysql.com/doc/maxdb/en/8c/ccce2...261/content.htm

That page backs up my friend's suggestion. So I tried this:

st.class ='ot_tax'(+)

and it caused a syntax error. This:

st.class ='ot_tax(+)'

eliminates the syntax error, but returns 0 rows.

edit - looking at that makes it obvious - sql is looking for the class 'ot_tax(+)' which obviously does not exist. /edit

I've also tried various ways of putting a (+) on this line:

st.orders_id = ot.orders_id

but I can't seem to put it anywhere without causing a syntax error.

 

Any SQL masters out there have a suggestion?

[OceanRanch]

Try replacing

 

o.orders_id=ot.orders_id

 

with

 

o.orders_id *= ot.orders_id

 

Inner/outter joins are setup with the *= or =* contruct.

 

HTH

Tom

[Chris Dunning]

Hrm....that returns this error:

ERROR 1064: You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near '* si.orders_id

  AND

  ot.orders_id=* st.orders_id

  AND

 

I tried both *= and =* - no luck.

[OceanRanch]

Hi Chris,

 

I was mixing sybase with mysql etc....

 

I had this snippet saved

equivalent of *= in mySQL

 

I was trying to convert this SQL statement to mySQL:

 

$sql = "select * from table1,table2 where topicID = '$topicID' and table1.CommentID *= table2.replyCommentID order by recordID asc" ;

 

this is what I came up with after much trial & error:

 

$sql = "select * from table1 LEFT JOIN table2 ON table1.commentID=table2.replyCommentID where table1.topicID = '$topicID' order by recordID asc " ;

 

So you might want to try replacing

 

o.orders_id=op.orders_id

 

with

 

LEFT JOIN o ON o.orders_id=op.orders_id

 

or

 

LEFT JOIN orders ON o.orders_id=op.orders_id

 

I'm not sure which one will work.

 

HTH

Tom

[Chris Dunning]

I've been playing with that and can't get it to work.

 

I understand the idea of a left join, I think - but can I specify two conditions for it? There should only be a match if the order_id is right and the ot_class is right - and I'm trying to pull three different classes here. Here's the full query:

 

"SELECT o.orders_id as orders_id, o.date_purchased as date_purchased, o.customers_name as customers_name, o.customers_telephone as customers_telephone, o.customers_email_address as customers_email_address, o.billing_name as billing_name, o.billing_street_address as billing_street_address, o.billing_city as billing_city, o.billing_postcode as billing_postcode, o.billing_state as billing_state, o.billing_country as billing_country, o.delivery_name as delivery_name, o.delivery_street_address as delivery_street_address, o.delivery_city as delivery_city, o.delivery_postcode as delivery_postcode, o.delivery_state as delivery_state, o.delivery_country as delivery_country, op.products_model as items_ordered, op.products_quantity as quantity_ordered, ot.title as shipping_method, ot.value as shipping_cost, si.value as shipping_insurance, st.value as sales_tax, ott.value as order_total, o.cc_type as cc_type, o.cc_owner as cc_owner, o.cc_number as cc_number, o.cc_expires as cc_expires, o.cvvnumber as cc_cvv FROM ".TABLE_ORDERS." as o, ".TABLE_ORDERS_PRODUCTS." as op, ".TABLE_ORDERS_TOTAL." as ot, ".TABLE_ORDERS_TOTAL." as ott, ".TABLE_ORDERS_TOTAL." as si, ".TABLE_ORDERS_TOTAL." as st WHERE o.orders_id=op.orders_id AND o.orders_id=ot.orders_id AND ott.orders_id = ot.orders_id AND ot.class = 'ot_shipping' AND ott.class = 'ot_total' AND ot.orders_id = si.orders_id AND ot.orders_id=st.orders_id AND si.class='ot_shipping_insurance' AND st.class='ot_sales_tax';";

 

Where does the join belong?

[OceanRanch]

Hi Chris,

 

I checked the following query using LEFT JOINS on osc orders and orders_total

table and it returned ALL of the orders and NULLed out those did not have

a matching ot_shipping class.

 

SELECT orders.orders_id AS orders_id,

orders.date_purchased AS date_purchased,

orders_total.value AS shipping_total

from orders

LEFT JOIN orders_total ON orders.orders_id = orders_total.orders_id and orders_total.class = 'ot_shipping'

 

I haven't tried to do this against another instance of orders_total but it

should work. I'll be messing with this in my spare time (right!!) but for

now at least you see one of the ways the JOIN needs to be to run properly.

 

I follow up with you as we proceed, got to go for now...

 

HTH

Tom

[Chris Dunning]

I also need to pull information from orders_products in this query -

where orders.orders_id=orders_products.orders_id

Should the "where" clause go before or after the "left join" clause, or does it matter?

[OceanRanch]

The "where" clause comes after the "table" clause (which can contain the JOIN clauses).

 

However, if you are going to include product information in this query you'll end up with many rows of order/order_total information. You might want to consider separating the queries and get all the order/order_total fields in a single row then loop through that using the order_id to query the products and many associated products tables.

 

Keep it simple and work the query incrementally until you get it correct. If you try to do the entire report or whatever in one fell swoop, I have found more often than not it takes more time and debugging headaches.

 

 

HTH

Tom

[project_aism]

You got your syntax all wrong for mysql outer joins.

 

Too much talk. Not enough sql-ing.

 

SELECT * FROM

    orders o,

    orders_products op,

    orders_total ot,

    orders_total ott,

    orders_total si

    left outer join orders_total st on st.class ='ot_tax'

    AND st.orders_id = ot.orders_id

WHERE

o.orders_id=op.orders_id

AND o.orders_id=ot.orders_id

AND ott.orders_id = ot.orders_id

AND si.orders_id=ott.orders_id

AND ot.class = 'ot_shipping'

AND ott.class = 'ot_total'

AND si.class='ot_insurance'

 

Trial and error is going to get you nowhere.

[Chris Dunning]

Thanks to all who helped on this project, especially project_aism who had the winning answer!

 

I didn't want to just

select *

because there's some info in those tables that's not necessary. Here's the final query I came up with:

$filelayout_sql = "SELECT
	 o.orders_id as orders_id,
	 os.orders_status_name as orders_status,
	 o.date_purchased as date_purchased,
	 o.customers_name as customers_name,
	 o.customers_telephone as customers_telephone,
	 o.customers_email_address as customers_email_address,
	 o.billing_name as billing_name,
	 o.billing_street_address as billing_street_address,
	 o.billing_city as billing_city,
	 o.billing_postcode as billing_postcode,
	 o.billing_state as billing_state,
	 o.billing_country as billing_country,
	 o.delivery_name as delivery_name,
	 o.delivery_street_address as delivery_street_address,
	 o.delivery_city as delivery_city,
	 o.delivery_postcode as delivery_postcode,
	 o.delivery_state as delivery_state,
	 o.delivery_country as delivery_country,
	 op.products_model as items_ordered,
	 op.products_quantity as quantity_ordered,
	 ot.title as shipping_method,
	 ot.value as shipping_cost,
	 si.value as shipping_insurance,
	 o.usps_track_num as usps_track_num,
	 o.ups_track_num as ups_track_num,
	 o.fedex_track_num as fedex_track_num,
	 st.value as sales_tax,
	 ott.value as order_total,
	 o.cc_type as cc_type,
	 o.cc_owner as cc_owner,
	 o.cc_number as cc_number,
	 o.cc_expires as cc_expires,
	 o.cvvnumber as cc_cvv
	 FROM
	 ".TABLE_ORDERS." as o,
	 ".TABLE_ORDERS_PRODUCTS." as op,
	 ".TABLE_ORDERS_TOTAL." as ot,
	 ".TABLE_ORDERS_TOTAL." as ott,
	 ".TABLE_ORDERS_TOTAL." as si,
	 ".TABLE_ORDERS_STATUS." as os 
	 left outer join ".TABLE_ORDERS_TOTAL." st on st.class ='ot_tax' AND st.orders_id = ot.orders_id
	 WHERE
	 o.orders_status= " . $orders_status . " 
	 AND
	 o.orders_id=op.orders_id
AND o.orders_id=ot.orders_id
AND ott.orders_id = ot.orders_id
AND si.orders_id=ott.orders_id
AND ot.class = 'ot_shipping'
AND ott.class = 'ot_total'
AND si.class='ot_insurance'
AND os.orders_status_id=o.orders_status
AND os.language_id=1;";

 

$orders_status is set earlier in the script as $HTTP_GET_VARS['orders_status']. To select all orders, I ran the same query without that particular line.

 

So...this pulls all of the address (customer's default, billing and shipping) information, all of the products ordered, shipping charges, shipping insurance charges, sales tax if it was charged, the name of the orders_status, all tracking numbers for the shipment...that's a whole lot of data. Next task is importing the edited data back into the database...whee!

[OceanRanch]

To project_asiam,

 

From the Mysql manual "The {OJ ... LEFT OUTER JOIN ...} syntax shown in the preceding list exists only for compatibility with ODBC. "

 

SELECT orders.orders_id AS orders_id,

orders.date_purchased AS date_purchased,

orders_total.value AS shipping_total

from orders

LEFT JOIN orders_total ON orders.orders_id = orders_total.orders_id and orders_total.class = 'ot_shipping'

 

My query is identical to your's. No big deal but your comment about too much talk and not enough sqling is off base.

 

Glad to hear that you came in the thread with the winning entry. Not a great way for me to start off my week on helping folks knowing I'll be second guessed and critiqued.

 

Tom