CODEWITHSUNDEEP
mysqlsqldatabase
Mr.
Mr.

Reputation: 10102

inner join in nested way on MySQL

this is my scheme:

board(name, catId)
cat(id, catName)
userBoard(boardName, username)
msg(boardName, username, title, text)

assume the username is "foo". i am trying to achieve the following but have no clue how to do it. i am interested in natural join of

userBoard.username = "foo" AND userBoard.boardName = board.name AND board.catId = cat.id AND msg.username = "foo" AND msg.boardName = board.name

what shall be the query for MySQL?

UPDATE: i forgot to mention that i am interested in the following returned values

`board.name, cat.catName, msg.title, msg.text`

Upvotes: 2

Views: 179

Answers (2)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

Try this:

Select b.name, c.catName, m.title, m.text
from board b
   inner join Cat c on b.catId = c.id
   inner join userBoard ub on b.name = ub.boardName
   inner join msg on m b.name = m.boardName
where ub.username = "foo" and m.username = "foo"

You will need to choose the type of join based on what do you want to select from your tables.

Upvotes: 1

aF.
aF.

Reputation: 66697

select b.name, c.catName, m.title, m.text
from board b
  inner join cat c on b.catId = c.id
  inner join userBoard ub on b.name = ub.boardName
  inner join msg m on b.name = m.boardName
where ub.username = "foo" and m.username = "foo"

Upvotes: 0

Related Questions