CODEWITHSUNDEEP
mysqlsqlmysql-workbench
itadaki33
itadaki33

Reputation: 65

MYSql multiple join query kando

I have a question for school that I can't quite figure out.

For each card on the board with the name “Product launch”, list the details of the card, the title of the column it is on, who created the card and member(s) that the card is assigned to.

I'm trying to work through this put not getting anywhere really.

select card.description, (select user.UserName where card.Creator = user.userID)
FROM card, user, board, `column`
JOIN board ON board.BoardID=`column`.boardID
where board.boardname = "Product launch"

Any help would be appreciated

kando sql

Upvotes: 0

Views: 37

Answers (1)

user1191247
user1191247

Reputation: 12998

10/10 for honesty in admitting that you are asking the internet to do your homework for you ;-) You will find that you are more likely to get help if you show clear signs of trying to help yourself.

If you are struggling then start by taking small logical steps and building it up progressively. Start with just retrieving the board with the given name (yes, I know you have already done this) -

SELECT *
FROM `Board`
WHERE `Board`.`BoardName` = 'Product launch'

then the next step is to satisfy the "for each card on the board" requirement. Based on your ERD we know we need to get the Columns to get to the Cards, so add Columns next -

SELECT `Board`.`BoardName`, `Column`.`ColumnTitle`
FROM `Board`
INNER JOIN `Column`
    ON `Board`.`BoardID` = `Column`.`BoardID`
WHERE `Board`.`BoardName` = 'Product launch'

then the Cards -

SELECT `Board`.`BoardName`, `Column`.`ColumnTitle`, `Card`.*
FROM `Board`
INNER JOIN `Column`
    ON `Board`.`BoardID` = `Column`.`BoardID`
INNER JOIN `Card`
    ON `Column`.`ColumnID` = `Card`.`ColumnID`
WHERE `Board`.`BoardName` = 'Product launch'

then continue the process to add the Creator and then the member(s) via CardAssignment. You may want to look at GROUP BY and GROUP_CONCAT when adding the member(s).

If you continue to struggle don't just say I cannot figure it out. Have a go and then come back with details of what you have tried and what hasn't worked as you expected. Researching and having a go are huge parts of the learning experience.

Upvotes: 2

Related Questions