hi, nice place this is here, the web is so big

I am developing a borad for my site and I created the following code to read out de forums index:

SELECT f.title AS ftitle,f.info AS finfo,f.topics,f.posts,t.id AS tid,t.title AS ttitle, p.uid AS lastposterid, p.time
FROM $mysql_database.$forums_table f, $mysql_database.$topics_table t, $mysql_database.$posts_table p
WHERE f.lastpostid=p.id
AND t.id=p.tid;

(this code is used in php the $ varibales are ok)

it works fine except when the topic databse or posts database is empty it doesnt give a forum if it does exist. I would like it to give out all the forums if there are posts or not. Now I read about left join en such but I cant seem to grap what it exactly does (even after reading http://www.wellho.net/mouth/158_MySQL-LEFT-JOIN-and-RIGHT-JOIN-INNER-JOIN-and-OUTER-JOIN.html ), and have been unable to implement it so far in my code

any suggestions? tnx

First comment .... have you looked at YaBB, PHPBB and simple machines - they're three very good furums that are available under open source and you may be much better off using theirs than developing your own.

Dirctley answering your question ...

if you use a LEFT JOIN, your guaranteed to have at least one record in the result set for each record in the incoming leftmost table of the join, even if there's no matching record in the right hand table of the join .... extra records generated are NULL filled.  Of course, if you use a WHERE or LIMIT clause too that may throw away the extra records ...

Hope that help you make more sense of this complex subject - th epage you referred to is one of our most popular here, but there's really no trivially easy explanation possible.

thank you for your reply

yes, I have looked arround and I actually have a phpbb forum right now, however, I think its much more fun creating your own, I have allready learned a lot of things about programming juring development, wich is something I'd like to continue .  However this left join really has me tied to the ground :S.

So basicly, the LEFT JOIN .... ON (blabla.d=blabla.e) is a substutude for the WHERE CLAUSE? except now when the search comes up negative,  it gives the left most table plus the other selected variables in the SELECT clause, fill as not null, is this the correct assumption? (of wich you put the tables on the right of the LEFT JOIN clause)

plus I wondered, there is ofcourse a different way to get the forums info:

first read in all forums, then do a loop and do a separate query for every forum,  however I recon this would take up more resources, so I came to LEFT JOIN, or is my thinking wrong here?

in any case I really like to know how this LEFT JOIN works as it feels like its a very powerfull command .

thnx again





Yes, that's a fair summary of LEFT JOIN, I think ... except that it fields in fields in the extra record with NULL rather than with NOT NULL.   I'm sure that's what you meant to say anyway, but I'm clarifying it for other readers.

yes, I'm sorry :S I ment NULL...

after you told me I had another look at the code, and I found the correct way to implement it in the code:

SELECT f.title AS ftitle,f.info AS finfo,f.topics,f.posts,t.id AS tid,t.title AS ttitle, p.uid AS lastposterid, p.time
FROM $mysql_database.$forums_table f LEFT JOIN $mysql_database.$topics_table t, $mysql_database.$posts_table p ON (f.lastpostid=p.id AND t.id=p.tid);

thanx a lot for your help Grahem

---