[SOLVED] Query Help

[haku]

I have a query I can't figure out how to write. Been playing with it for a while with no luck. Here are the details:

 

Table 1: node

Columns: nid, uid

 

Table 2: to_do

Columns: nid, title

 

Table 3: to_do_assigned_users

Columns: nid, uid

 

Table 4: users

Columns: uid, name

 

How it all fits together...

 

A user creates a node. Node creation creates rows in three tables:

 

* It creates a row in table 1 (nodes) that contains the user id (uid) of the creator, and a node id (nid)

* It creates a row in table 2 (to_do) that contains the title of the node (title) and inserts the nid from the first row (nid)

* In table 3 (to_do_assigned_users) it creates one row for each user who has been assigned to the node. Each row contains the node id (nid) from table 1, as well as the user id (uid) of the user who was assigned to the node.

* Usernames (for both the node creator and assigned users) are drawn from table 4 (users) using their user id (uid)

 

What I am trying to do...

 

I am trying to create an HTML table that shows all the nodes a user has created. Each row in this table will represent one node. There will be two columns in the table - 'title' and 'assigned users'. Assigned users will be an HTML list of all users assigned to that node.

 

So I want my query to...

 

I want the query to grab the node title and assigned usernames for all nodes created by the current user. Or to say it another way, I want the query to return a bunch of rows, with each row returned by the query containing:

 

* the node title

* all the usernames for that node

--- only rows created by the current user should be returned (the uid in table 1)

 

This is where I get a little lost - I don't know if it is possible to have one row return multiple usernames. And if it doesn't, then I'm not sure how to go about this so that I can group the usernames together on the PHP side of things.

 

Any thoughts on this?

 

Edit: table 1 (nodes) and table to (to_do) cannot be grouped together into one table. It may seem like that looking at my explanation, but I have actually scaled this down to an easy to see level. There is actually a lot more complexity in the system, and the tables listed have more columns than I wrote in my explanation. I just scaled this down to the core of my problem - I will be able to add the rest myself after.

[haku]

If anyone is reading this right now, I edited my original post a little.

[kickstart]

Hi

 

Without test running (so bound to be a typo in it), I think this is the kind of thing you are after:-

 

SELECT a.nid, b.title GROUP_CONCAT(d.name)
FROM (SELECT nid FROM node WHERE uid = '$CurrentUserId') a
LEFT OUTER JOIN to_do b ON a.nid = b.nid
LEFT OUTER JOIN to_do_assigned_users c ON a.nid = c.nid
LEFT OUTER JOIN users d ON c.uid = d.uid
GROUP BY a.nid, b.title

 

All the best

 

Keith

[haku]

That's exactly what I wanted mate! That's a nice little bit of sql there - I'm impressed to say the least. GROUP_CONCAT() is a new one for me (I just looked it up in my mysql book - its page 869, I'm only around page 500 so far ), but I will definitely use it in the future. I have wondered how to concatenate columns in the past, and been left at a loss.

 

Thanks for taking the time to read through my long explanation and come up with something.

 

edit: it's working perfectly by the way.