[SOLVED] sub query (I think)

[Brian W]

I'm making a query of all the tasks in a project. Tasks have users assigned, often times more than one.

What I'd like to do is something to the effect of

SELECT t.task, t.id as tid, t.date, p.project, p.id as pid,

CONCAT_WS(", ", (

SELECT u.Name FROM `users`

WHERE u.ID in (

SELECT uid FROM `assign_assoc` WHERE `tid` = tid

)

) as Assigned

FROM projects p

LEFT JOIN tasks t

on p.id = t.pid

WHERE tid = 33

Now I know that that is probably horribly incorrect, but I hope the concept reaches though the scribble.

I just jahted that down, my actual query will have a couple more things going, I just want to know how to make a comma separated list of the users assigned in one field (`Assigned`) all within one query to the db.

 

Thanks for any help, links, input, slaps to the back of the head, ect. :-)

[Mchl]

I think you're looking for GROUP_CONCAT

[Brian W]

Thanks, I think that put me in the right direction...

So here is what I have:

SELECT

p.Project as `Project`,

t.id as `ID`,

GROUP_CONCAT(u.Username) as Assigned,

t.task as `Task Description`,

s.Value as Status,

t.Cdate as `Last Updated`,

CASE t.Billed

WHEN 1 THEN 'Yes'

WHEN 0 THEN 'No'

END as Billed,

t.Actual_Hours AS `Hours`

FROM projects t

LEFT JOIN projectlist p

ON t.ProjectID = p.ProjectID

LEFT JOIN status_types as s

ON t.Status = s.Order

LEFT OUTER JOIN assigned a

ON t.id = a.tid

LEFT OUTER JOIN bexusers u

ON a.uid = u.id

WHERE

t.Billed = 0

AND t.Cdate > 0

AND t.Cdate < 1239993726

ORDER BY Project

Excuse the names of the tables and fields, many of them aren't names correctly... I'll be re-doing this db soon.

Any ways, my problem now is that I only get one row which the field `Assigned` has the same username 4 times. The task it returns does not even have any one assigned... so that's a bug with my query.

The row it is returning is the first row that was being returned before I added the GROUP_CONCAT.

[fenway]

GROUP_CONCAT() without a GROUP BY clause?

[Brian W]

Sorry, I don't follow. I can't find any example online where someone is doing something like what I'm doing.

[fenway]

For debugging, forget the group_concat... what I was mentioning is that you're not grouping by anything, so what do you expect group concat to do?

[Brian W]

create a comma seperated list of usernames.

I'm not sure what field I should group by, that is why I'm so lost and asking here.

Up till yesterday when Mchl replied, I'd never seen that function.

[fenway]

Let's start again.

 

Show me some sample output from the original/current query (without group-concat).

[Brian W]

sorry, I'll do that if u still would find that helpful... but here is where I am now.

SELECT

p.Project as `Project`,

t.id as `ID`,

GROUP_CONCAT(u.Username SEPARATOR ', ') as Assigned,

t.task as `Task Description`,

s.Value as Status,

t.Cdate as `Last Updated`,

CASE t.Billed

WHEN 1 THEN 'Yes'

WHEN 0 THEN 'No'

END as Billed,

t.Actual_Hours AS `Hours`

FROM projects t

LEFT JOIN projectlist p

ON t.ProjectID = p.ProjectID

LEFT JOIN status_types as s

ON t.Status = s.Order

LEFT JOIN assigned a

ON t.num = a.tid

LEFT OUTER JOIN bexusers u

ON a.uid = u.id

WHERE

t.Billed LIKE '%'

AND t.Cdate > 0

AND t.Cdate < 1240001157

GROUP BY t.id

ORDER BY Project, t.num DESC

I attached a screenshot from phpMyAdmin of the query results

As you can see, it seems to be grabbing duplicate names for the assigned field in some cases. I also get the wrong name altogether in a field.

In the screenshot, I struck out (with red lines) the results that shouldn't be there that i could tell.

[Brian W]

No, I did not attach one 

Here it is

 

[attachment deleted by admin]

[Brian W]

I solved duplicates by using "DISTINCT" within the GROUP_CONCAT function.

SELECT

p.Acronym as `Project`,

t.id as `ID`,

GROUP_CONCAT(DISTINCT u.Username SEPARATOR ', ') as Assigned,

t.task as `Task Description`,

s.Value as Status,

t.Cdate as `Last Updated`,

CASE t.Billed

WHEN 1 THEN 'Yes'

WHEN 0 THEN 'No'

END as Billed,

t.Actual_Hours AS `Hours`

FROM projects t

LEFT JOIN projectlist p

ON t.ProjectID = p.ProjectID

LEFT JOIN status_types as s

ON t.Status = s.Order

LEFT JOIN assigned a

ON t.num = a.tid

LEFT OUTER JOIN bexusers u

ON a.uid = u.id

WHERE

t.Billed LIKE '%'

AND t.Cdate > 0

AND t.Cdate < 1240006118

GROUP BY t.id

ORDER BY Project, t.num DESC

Still trying to figure out why I get users showing up as assigned in tasks they have nothing to do with.

[Brian W]

I went back to the sub query idea... which was the right track.

Thanks for the help guys, ended up using GROUP_CONCAT within the

SELECT

p.Acronym as `Project`,

t.id as `ID`,

(

SELECT GROUP_CONCAT(u.username SEPARATOR ', ')

FROM assigned a

LEFT JOIN bexusers u

ON a.uid = u.id

WHERE tid = t.num

) as Assigned,

t.task as `Task Description`,

s.Value as Status,

t.Cdate as `Last Updated`,

CASE t.Billed

WHEN 1 THEN 'Yes'

WHEN 0 THEN 'No'

END as Billed,

t.Actual_Hours AS `Hours`

FROM projects t

LEFT JOIN projectlist p

ON t.ProjectID = p.ProjectID

LEFT JOIN status_types as s

ON t.Status = s.Order

WHERE

t.Billed LIKE '%'

AND ( t.Cdate > 0 AND t.Cdate < 1240009108 )

GROUP BY t.id

ORDER BY Project, t.num DESC

 

[fenway]

So, solved?

[Brian W]

Sorry, yes Solved. Onto the next hurdle!