join help

xwin

Hi... can anyone help me put together the SQL code for this?

Ok so I have 2 tables
TOPIC and USER

important elements in TOPIC are

(PRIMARY) ID = id of topic
pID = parent forum
lastUID = users id of last user to post in topic

USER looks like

(PRIMARY) ID = id of user
USERNAME = users name

OK so I am trying to grab all topics from TOPIC which have a certain parent id (WHERE TOPIC.pID = 3 or whatever) but also match each returned row to the USER table to get the users name for the last post in that topic (match TOPIC.lastUID with USER.ID)

can I do this all in one sql statement?

Any help much appreciated!
Thanks! 🙂

Piranha

Yes, it is possible to do all in one query. You have already solved the parent part, so I won't talk about that.

The other part is the user. Look into JOIN (including left and right join) and you will see how to do it.

xwin

Piranha wrote:
Yes, it is possible to do all in one query. You have already solved the parent part, so I won't talk about that.

The other part is the user. Look into JOIN (including left and right join) and you will see how to do it.

Hi, thanks for the reply
I'm really having trouble getting it to return more then 1 result
I'm forced to put the WHERE statement at the end resulting it in it checking the users id first (i'm assuming) as the determining variable (if its a different user, it doesn't seem to return the row at all)

"SELECT TOPIC.ID, TOPIC.pID, TOPIC.lastUID, TOPIC.TITLE FROM TOPIC INNER JOIN USER ON TOPIC.lastUID = USER.ID WHERE CP.pID = '$FORUM_id'";

Piranha

Why do you use an inner join and not a left join? With a inner join it will only return results where there is a last user, not if a user is missing.

I don't understand why it is not working, can you show some php code as well (how you loop the result and show it).