Need help with double INNER JOIN

[Aeonix]

SELECT [SNAP!]
FROM topics INNER JOIN members ON topics.author = members.id
WHERE topics.parent = [variable from PHP]
ORDER BY topics.id DESC LIMIT [variable from PHP], 10

Is what I have in pocket. I'd like to slam SELECT * FROM forums WHERE id = topics.parent. While keeping the functionality it already has.

[ebichuhamster]

First off, I'm very ignorant of the MySQL scene or it's jargon. I didn't really understand the premise, pecifically the "slam" part and the "[SNAP!]" part, or the "pocket" part.

What I interpret is "I would like to add this line (SELECT * FROM forums WHERE id = topics.parent) to the original code, while keeping the functionality". If that is what you were referring to then I have to make the observation that from what you wrote I cannot determine what is the [MISSING_TABLE]. I have the feeling it is 'forums', but I will just label it as [MISSING_TABLE], so here is one of my answers:

SELECT [SNAP!]
FROM forums,topics INNER JOIN members ON topics.author = members.id
WHERE topics.parent = [variable from PHP]
AND topics.parent = [MISSING_TABLE].id
ORDER BY topics.id DESC LIMIT [variable from PHP], 10

Another answer is not to use an explicit inner join.

SELECT [SNAP!]
FROM forums,topics, members
WHERE topics.parent = [variable from PHP]
AND topics.author = members.id
AND topics.parent = [MISSING_TABLE].id
ORDER BY topics.id DESC LIMIT [variable from PHP], 10

Maybe you have to do it through a double inner join, but sadly I don't what would happen exactly by using 2 inner joins. I do believe these answers are acceptable though, let me know your thoughts.

[Aeonix]

First off, I'm very ignorant of the MySQL scene or it's jargon. I didn't really understand the premise, pecifically the "slam" part and the "[SNAP!]" part, or the "pocket" part.

Oh no, they were my metaphores, do you really think respectable people over at MySQLi would use such low-grade terms? ;D

I do believe these answers are acceptable though, let me know your thoughts.

You'll have to wait, I'm really new to SQL, I need to make things crack and adjust to see if it works. Be right back.

[Aeonix]

Maybe this way:
I need to select all the data from:
- forums.id parentForumId, forums.name parentForumName from forums where forums.id = topics.parentForum.
- members.id authorId, members.name authorName from members where members.id = topics.authorId.
Both, in query that looks something like this:

SELECT
    forums.id parentForumId,
    forums.name parentForumName,
    members.id authorId,
    members.name authorName,
    etc. etc. etc. etc.
FROM topics INNER JOIN members ON topics.author = members.id
WHERE topics.parent = $currentForum
ORDER BY topics.id DESC LIMIT $currentIndex, 10;

[Aeonix]

I have something like this:

SELECT
    [SNAP!]
FROM (topics INNER JOIN members ON topics.author = users.id)
    (INNER JOIN forums ON forums.id = forums.parent)

But the query seems incorrect using a validator. The last line is invalid. What's wrong with it?

[pritaeas]

INNER JOIN forums ON forums.id = forums.parent

This is a self-join without a relation to either topics or members. Without knowing more it's ringing alarm bells.

Perhaps:

INNER JOIN forums ON forums.id = topics.parent

Besides that, I'm not too sure about the parenthesis. They have no function, but may trigger an invalid SQL error.

[Aeonix]

This is a self-join without a relation to either topics or members.

Oh, didn't notice xD

And yea, parenthesis is bad apparently. Thanks!

I understand INNER JOIN now (I think), that's so neato!!