Easy Join Troubles ?!

I'm kinda new to SQL...probably someone can give me hand to a Join statement...


I'd like to join those statements...so I can use the selected topic_id from the first statement for retrieving the value from the image field...

Do both tables have a topicID? i.e., is topic_id the primary key in tblTopics and the foreign key in tblNews? if so, how about:

select tbltopics.topic_id, section_id, title, the_time, the_text, tbltopics.image FROM tblNews INNER JOIN tbltopics on tblNews.topic_id = tblTopics.topic_id
where tbltopics.topic_id = 'topic_id'

I haven't tried it myself, so let me know if you run into any problems, but it should work.

Thanks a lot..have it working now..

SELECT n.topic_id as topic_id, n.title as title, n.the_time as the_time, n.the_text as the_text, t.image as image_topic FROM tblNews n, tblTopics t WHERE n.topic_id = t.topic_id ORDER BY the_time DESC LIMIT 4

it's a little different...but it does the same...don't know which one is better...

good for you. code is pretty much the same.

I was thinking (and I could have been wrong on this front) that there was only one item per topic and only one record would be returned, so I didn't include the order by clause.

Thanks...there can be more items for every topic...
so item - topic : many - one

I wonder..is there some tool which can easily create these kind of statements?? Statements with Inner / Left / Right joins...that would make life a lot easier

MS Access can write SQL statements for you. When you create a Query (by dragging and dropping fields from the table you require), you can click on the SQL view to see the SQL. However, it's usually possible to write more succinct statements yourself.

It would be great if there was another tool! Anybody else out there have any recommendations?