Another quick mysql question

[torifile]

I set up an online registration system using drupal and ubercart. The setup worked perfectly and I'm very happy with the relative ease of getting it all working with google checkout. One deficiency of the setup has been the difficulty in getting the data OUT of the system.

I know that there's an easy mysql query I can use to get my data out, I just don't know what it is. Here's the situation:

uc_orders.uid matches up with users.uid to give me the name of the person who ordered the item.

uc_order_products.order_id matches with uc_order.order_id to give me the order number for that particular "item"'s order.

Each individual order has multiple rows in the uc_order_products table depending on the number of items ordered. I'd like to somehow collapse all these rows into columns so that each uid has a unique row.

Example:

User #1 orders items A, B, C and D. His order number is #12.

The uc_order table would have


uid order_id
1 12


The uc_order_products table would have


order_id product
12 A
12 B
12 C
12 D


What I'd like is to get a table that looks like this:


uid lastname order_id product product product product
1 Smith 12 A B C D


I've been able to get a join of all that data but I haven't been able to get it all one row. I realize that some people may have null values in one of the 4 product columns, but that's ok. Thoughts?

[torifile]

Just to be clear, what I'm looking at is using order_id to connect the uc_order_products table to the users table.

[Brad]

I'd like to somehow collapse all these rows into columns so that each uid has a unique row.
This is actually a bit difficult. I've encountered this situation before and have not seen an elegant solution for it, but I'll dig around to see if I've missed something.

[torifile]

Well, I've figured out how to sorta do it - GROUP_CONCAT(expr). It kind of works for what I want. The only issue is that I've got more than 3 tables. Turns out that the users' names are in another table. In essence, if I want to create a roster, I can only go by usernames rather than real names unless I can figure out how to word a 4 table join... This shit makes my head spin.

[torifile]

Christ on a stick. mysql queries suck balls.


SELECT users.uid, users.name, users_roles.rid, profile_values.fid, profile_values.value, GROUP_CONCAT(profile_values.value) FROM users LEFT JOIN users_roles USING (uid) LEFT JOIN profile_values USING (uid) WHERE users_roles.rid = 4 GROUP BY users.uid
All that just to do something that should be a piece of cake. And is a piece of cake using Filemaker Pro. I didn't realize how easy that program makes my life.