inner join in nested way on MySQL

Question

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`

Mahmoud Gamal · Accepted Answer · 2012-02-02 10:04:09Z

1

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.

edited Feb 2, 2012 at 10:04

answered Feb 2, 2012 at 9:38

Mahmoud Gamal

80.2k18 gold badges143 silver badges168 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Mr. Over a year ago

this is problematic, it can create a huge table. i wish to visit the table in the order of my logical condition. i.e., firstly retrieve all the userBoard's, then combine it with the catId, etc..

Mahmoud Gamal Over a year ago

@MrRoth, this is the way your logical condition is written, and it won't create a huge table, it will give you all the data that conform to your logical condition and why it is problematic just try it,

Mr. Over a year ago

it should not create the table where one of the condition breaks. here it firstly creates the table, then checks the conditions. and i think that you've forgot on on the 4th row

Mahmoud Gamal Over a year ago

@MrRoth, What do you mean by, create table, it won't create a table then filter it, the selection is the last thing to perform.

aF. · Accepted Answer · 2012-02-02 09:39:57Z

0

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"

answered Feb 2, 2012 at 9:39

aF.

67k45 gold badges141 silver badges201 bronze badges

Collectives™ on Stack Overflow

inner join in nested way on MySQL

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related