Select more than one table

Website Building
#1

Hello everyone! I am calling data from two different tables this way:

$sql = "SELECT
topics.topic_id,
topics.topic_subject,
topics.topic_date,
topics.topic_by,
posts.post_topic,
posts.post_content,
posts.post_img
FROM
topics
LEFT JOIN
posts
ON
posts.post_topic = topics.topic_id

	ORDER BY
		topics.topic_id DESC";

But for some weird reason I am not getting anything from the table posts. No errors in the mysql_query either :-/ Any ideas??

#2

Hi,
It could be that you’re assigning a wrong post_topic ID. That way you won’t get anything from the post table, unless you RIGHT JOIN.

Cya.

#3

I really though about that, but exporting the two tables to a spreadsheet made me see more clearly that I am not wrong :frowning:
http://imageshack.us/photo/my-images/695/topicpost.jpg

#4

I don’t know how to feel now :-/ RIGHT JOIN works fine, but I don’t know why :-? I never studied right join…
Thanks Passionless!!

What I found is that I am storing the date in tho different tables. Should I erase one of them?

#5

Hi,
Given your schema you should delete the ‘topic_date’ row and may be also ‘topic_com’, which I suppose hold the number of comments on a given topic (that info is already on the tables, just COUNT(*) it) and ‘topic_by’.

I think there’s at least a post row for each topic row (the topic’s subject on the topic table and the topic’s content on the post table) so just JOINing should work, since there is no topic without a post (LEFT JOIN) neither a post without a topic it belongs to (RIGHT JOIN).
I sincerely don’t know why it behaves this way.

http://img268.imageshack.us/img268/3922/unledzdt.png

#6

Left join is used to find rows that don’t match. According to the image in your link, the data in topics.topic_id
and posts.post_topic columns are exactly same, thus matching one by one. That’s why you could not get any
result. As Passionless pointed out, you can get the result by just joining two tables as follows:

$sql = “SELECT
topics.topic_id,
topics.topic_subject,
topics.topic_date,
topics.topic_by,
from topics, posts,
posts.post_topic,
posts.post_content,
posts.post_img
where posts.post_topic=topics.topic_id //This might not be needed if the data in two columns are exactly same.
order by topics.topic_id DESC”;

#7

you guys are amazing!!! THANKS.
BTW I’m really happy the way my page is coming up. You can see it (almost done) but there is still stuff to be done so i’m gonna be bothering for a while more :slight_smile: