Need help with a JOIN query

[sdotsen]

Not sure which forum this belongs in, mysql or php but I'm leaning more towards PHP due to the fact that I have to massage the data and make sure it displays correctly.

 

We're rebuilding our shopping cart and based on some open source code we found online, my colleague and I started to hack it to pieces so that it fits into our environment. I lost the fight of keeping vital information in the same table as the parent item but he wants to separate them. For example, for each item, there's a shipping cost and a warranty cost associate with EACH item. Instead, he moved these two items out of the table and created a parent-child table. So Product A (id of 100) is associated with Warranty A (id of 200). To make it more complicated, the products table lists everything including each product, warranty code and shipping codes. Instead of a simple query and while loop to display the results, I'm forced to write JOIN queries. It's fine but I'm lost and I'm ending up making multiple calls to the DB.

 

Table Products

--------------

product_id (unique ID of warranty code, product and shipping code)

product_desc

price

 

Table products_item

--------------

parent_id (ID of the product i.e. Widget A0

child_id (ID of the warranty i.e. 2 yrs warranty)

 

table order_item

--------------

order_id

product_id

quantity

 

table orders

--------------

order_id

person_first_name

person_last_name

 

 

So basically if I wanted to display an order summary of what a customer orders I would do the following.

 

select person_first_name, person_last_name, order_id, product_desc from orders a, order_item b, products c where a.order_id = b.order_id AND b.product_id = c.product_id

 

 

Now that gives me the person's name, the order ID and the product they ordered. Now how the heck do I pull the warranty info and shipping code relevant to this particular product?

[Moon-Man.net]

select	person_first_name,
	person_last_name,
	order_id,
	product_desc 
from	orders ord,
	order_item itm,
	products pro
LEFT JOIN ( products_item ) on ( products_item.parent_id= pro.product_id ) 
where	ord.order_id = itm.order_id 
  AND	itm.product_id = pro.product_id

 

I don't usually use MySql but from the reference manual that is how you do it

http://dev.mysql.com/doc/refman/5.0/en/join.html

 

Hope that helps

 

[sdotsen]

Close but no cigar.

 

Query result ...

+------------------------+-----------------------+-------+------------+

| od_shipping_first_name | od_shipping_last_name | od_id | pd_name    |

+------------------------+-----------------------+-------+------------+

| John                  | Smith                |  1084 | Widget A |

| John                  | Smith                |  1084 | Widget B    |

+------------------------+-----------------------+-------+------------+

 

 

No warranty/shipping info related to each widget. Here's how the order_item table looks like.

 

+-------+-------+--------+-------------+---------------+---------+-----------+

| od_id | pd_id | od_qty | warranty_id | warranty_cost | ship_id | ship_cost |

+-------+-------+--------+-------------+---------------+---------+-----------+

|  1084 |    40 |      1 | NULL        |          NULL | 12      |    150.00 |

|  1084 |    32 |      1 | NULL        |          NULL | 12      |    150.00 |

 

Ignore warranty_id and how it's NULL, if I can just pull the naming convention for the ship_id, I can get the warranty to work.

So the table above means for order # 1084, there were two items that were ordered (pd_id 40 and 32 - Widget A & Widget B).

They both have shipping id of 12, which is located in the products table (product_id = 12, product_desc = Turnkey)

 

 

 

 

[Moon-Man.net]

select       person_first_name,

person_last_name,

order_id,

product_desc

from       orders,

order_item,

products

LEFT JOIN products_item on orders.order_id=order_item.order_id

where order.order_id = order_item.order_id

  AND order_item.product_id = products.product_id

 

 

Give that a shot. I think I got confused with the data layout.

If that doesn't work, can you please post all the table layout and data like you have for query result ETC?

 

[sdotsen]

tbl_order_item  (this table captures all the order info, specifically the order number, the product id and it's shipping and warranty code

------------------

+-------+-------+--------+-------------+---------------+---------+-----------+

| od_id | pd_id | od_qty | warranty_id | warranty_cost | ship_id | ship_cost |

+-------+-------+--------+-------------+---------------+---------+-----------+

|  1084 |    40 |      1 | NULL        |          NULL | 12      |    150.00 |

|  1084 |    32 |      1 | NULL        |          NULL | 12      |    150.00 |

 

 

tbl_product (this table captures all the product info, this also includes the warranty/shipping code info)

It's rather long so I'll post the relevant fields.

 

+-------+-------+------------+

| pd_id | pd_name | pd_price |

+-------+-------+------------+

|  40 |    Widget A |      50.00 |

|  32 |    Widget B |      40.00 |

| 12  | Turnkey    |    150.00 |

| 11 | Curbside    |    40.00 |

 

tbl_order (this table captues the customer's info and their order number which is referenced against the tbl_order_item table)

 

+-------+-------+------------+-----------------+

| od_id | od_ship_first_name | od_ship_last_name |

+-------+-------+------------+-----------------+

|  1084 |    John |      Smith            |

 

 

----------------------

 

I think those are the 3 important tables I need to make this work. basically the query you gave me and what I've gotten so far spits out the following.

 

+------------------------+-----------------------+-------+------------+

| od_shipping_first_name | od_shipping_last_name | od_id | pd_name    |

+------------------------+-----------------------+-------+------------+

| John                  | Smith                |  1084 | Widget A |

| John                  | Smith                |  1084 | Widget B    |

+------------------------+-----------------------+-------+------------+

 

As you can see above, it prints out the correct number of items that John Smith ordered. It was able to pull the order number and listed each item that was relevant to order number 1084. What it doesnt do is pull the shipping data. The problem I am having is the shipping code description is also in the "tbl_product" table. I've been fighting to have it listed in the tbl_product as an extra field so that when you pull all information relevant to Widget A, you get everything you want. But it seems doing the parent-child relationship makes it a tough query.