MySQL选择行值为Key => Value

Question

I have a MySQL structure set up similar to this from Basecamp:

bc_projects

project_id | title                    | active
2618731   | 'Sample Title'    | 1
2618732   | 'Sample Title 2' | 0

bc_todolists

todolist_id | project_id | title
6731098   | 2618731   | 'Launch Status'
6731099   | 2618731   | 'Some Other List'

bc_todos

todo_id     | todolist_id | title                  | completed     | content
39180738 | 6731098   | 'Client Signoff' | 1366041432 | 'YYYY-MM-DD'
39180739 | 6731098   | 'Team Signoff' | null                | ''

What I need to do is get all project related details into 1 row for each project (or some other equally effective SQL)

The next part is even tricker. I would like to get either content or completed from bc_todos depending on whether or not content is null or = 'YYYY-MM-DD'. I have attempted to run this myself but am stuck at this point. Here is what i have so far:

SELECT 
    bc_projects.title
    (SELECT GROUP_CONCAT(todolist_id SEPARATOR ',') FROM `bc_todolists` WHERE project_id = bc_projects.project_id) AS lists
FROM 
    bc_projects
WHERE 
    bc_projects.active = 1

I know I can pull the data from bc_todos using the joined lists in the query now, but the key value pairing is messing my head up. Any help would be appreciated!

Answer 1

Not sure if this is what you want

SELECT a.title,

GROUP_CONCAT(b.todolist_id SEPARATOR ',') AS lists,

CASE WHEN c.content IS NULL

     THEN GROUP_CONCAT(c.completed SEPARATOR ',')

ELSE GROUP_CONCAT(c.content SEPARATOR ',')

END as outlist

FROM bc_projects a

LEFT OUTER JOIN `bc_todolists` b ON a.project_id = b.project_id

LEFT OUTER JOIN bc_todos c ON b.todolist_id = c. todolist_id

WHERE a.active = 1