Jump to content


Photo

Combining tables vertically and horizontally (phew)


  • Please log in to reply
7 replies to this topic

#1 danep

danep
  • New Members
  • Pip
  • Newbie
  • 7 posts

Posted 28 August 2006 - 08:32 PM

Hi everyone!  Okay, here's my problem in a nutshell: I know how to combine tables "vertically" (using UNION), and "horizontally" (using JOIN)... but what if I need to do both?  Let me explain.

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?

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 28 August 2006 - 10:24 PM

The UNION should be fine, but the number/type of the columns has to match up, so that's for sure the problem.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 danep

danep
  • New Members
  • Pip
  • Newbie
  • 7 posts

Posted 28 August 2006 - 10:29 PM

You're correct, the only column they have in common is "id".  So instead of selecting all columns I tried "(SELECT inventory.name FROM cart......) UNION ALL (SELECT inventory.name FROM invoice.....)" , and it throws the same error.  Now it seems to me, that since the column being returned in both selects is identical, that this should work.  Do all columns have to match even if they're not being selected?  Or is this just bad syntax on my part?

Update: sorry, edited for clarity

#4 danep

danep
  • New Members
  • Pip
  • Newbie
  • 7 posts

Posted 28 August 2006 - 11:42 PM

So, once again, this is the query I am using:
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.

#5 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 29 August 2006 - 01:22 AM

Strange... the same field, from the same table, just UNIONed, should be fine...
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#6 danep

danep
  • New Members
  • Pip
  • Newbie
  • 7 posts

Posted 29 August 2006 - 02:29 AM

I noticed my hosting company is running MySQL 3.23.58, which I gather is pretty behind the times.  Could this be the problem?  Sorry, probably should've mentioned this earlier but didn't notice until now.

#7 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 29 August 2006 - 02:31 PM

Yup -- no UNION.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#8 danep

danep
  • New Members
  • Pip
  • Newbie
  • 7 posts

Posted 29 August 2006 - 03:35 PM

Thanks!  Wish I had thought of that sooner!




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users