Combining tables vertically and horizontally (phew)
Posted 28 August 2006 - 08:32 PM
I have three tables: a shopping cart (containing, among other things, a product ID and session id), a product inventory (containing a product ID and product name/info), and a list of previously purchased items (containing the product ID and associated invoice number). What I need to do is basically create a data set of all items currently in the user's shopping cart, plus all items on one of their previous orders, and then order all of that by product name. I've tried using a join like so:
"SELECT * FROM cart, invoice_details INNER JOIN inventory ON inventory.id=cart.id OR inventory.id=invoice_details.id WHERE cart.session='$user_session' OR invoice_details.invoice_id=$invoice_id ORDER BY product_name"
Dunno if that makes sense... anyway, this query returns hundreds of results, basically everything from invoice_details and the user's cart - not at all what I was aiming for. So then I tried
"(SELECT * FROM cart INNER JOIN inventory ON inventory.id=cart.id WHERE cart.session='$user_session') UNION ALL (SELECT * FROM invoice_details INNER JOIN inventory ON inventory.id=invoice_details.id WHERE invoice_details.invoice_id=$invoice_id) ORDER BY class"
This throws and error, a la Warning: mysql_fetch_object(): supplied argument is not a valid MySQL result resource.
Can anybody offer an alternative or point out what's wrong?
Posted 28 August 2006 - 10:29 PM
Update: sorry, edited for clarity
Posted 28 August 2006 - 11:42 PM
SELECT inventory.item_name FROM cart INNER JOIN inventory ON inventory.item_id=cart.item_id WHERE cart.session='$user_session' UNION ALL SELECT inventory.item_name FROM invoice_details INNER JOIN inventory ON inventory.item_id=invoice_details.item_id WHERE invoice_details.invoice_id=$invoice_id
And I'm getting a syntax error. Each select by itself runs fine, it's something to do with the Union that's causing an error.
Posted 29 August 2006 - 02:29 AM
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users