Having problem using left join with group_concat

I am new to mysql -- so apologies for the newbie questions. I've researched a bit, but due to lack of general understanding, my brain feels like a pretzel at this point.

What I want to do is output a list of tickets -- this output has several fields, including a field for blocking tickets. The following SQL will give me, more or less, what I want:

select j3.pkey, j3.summary, blockers.blocking_tickets, j3.reporter, j3.assignee,     j3.created, j3.resolutiondate

from 
    (select j2.id, group_concat(j1.pkey) blocking_tickets
    from jiraissue j1, issuelink, issuelinktype, jiraissue j2 
    where j1.id=issuelink.SOURCE 
    and j2.id=issuelink.DESTINATION 
    and iss开发者_StackOverflow中文版uelinktype.id=issuelink.linktype
    and  issuelinktype.INWARD = 'is blocked by'
    group by j2.pkey) blockers, jiraissue j3

where 
blockers.id = j3.id
AND
((DATE_SUB(CURDATE(),INTERVAL 24 Hour) <= j3.resolutiondate) or (DATE_SUB(CURDATE(),INTERVAL 24 Hour) <= j3.created))

-- I realize that I don't need a subquery here -- this was just a stepping stone.. anyway, the problem is that my query doesn't show me all the tickets (what I want in my output are all recorder returned for the tickets, whether or not they have blocking tickets in the blocking ticket field).

So, I figured I needed some sort of left join. This is where I am totally confused.

I tried this, but I get: Unknown column 'blockers.id' in 'on clause'

select j3.pkey, j3.summary, blockers.blocking_tickets, j3.reporter, j3.assignee, j3.created, j3.resolutiondate

from 
(select j2.id, group_concat(j1.pkey) blocking_tickets
    from jiraissue j1, issuelink, issuelinktype, jiraissue j2 
    where j1.id=issuelink.SOURCE 
    and j2.id=issuelink.DESTINATION 
    and issuelinktype.id=issuelink.linktype
    and  issuelinktype.INWARD = 'is blocked by'
    group by j2.pkey) blockers, jiraissue j3

left join jiraissue on blockers.id = jiraissue.id

where ((DATE_SUB(CURDATE(),INTERVAL 24 Hour) <= j3.resolutiondate) or (DATE_SUB(CURDATE(),INTERVAL 24 Hour) <= j3.created));

Any tips appreciated. I acknowledge that I am probably doing some pretty ridiculous stuff -- so please be gentle! :)

Thanks.

---

Looks like there are some leftovers from previous query. Probably should be

    group by j2.pkey) blockers

left join jiraissue j3 on blockers.id = j3.id

---

I think your problem is that your nested query doesn't produce a column name id, it just has j2.id and MySQL isn't stripped the j2 prefix off. Try changing

select js.id, group_concat /*...*/

to

select j2.id as id, group_concat /*...*/

---

To me, this bit seems out of place:

group by j2.pkey) blockers, jiraissue j3

Why not simply:

group by j2.pkey) blockers

?