Getting results from 2 tables?

[furious5]

Can anyone help me out, I'm stuck on a sql query.

Basically I am building a journal application which will allow a person to post a journal entry, and then allow other people to reply to this.

At the moment I have the journals in one table, and the messages relating to a journal entry in another.

My problem is that I now want to arrange it so the journal is displayed at the top of the page and then any associated messages below this.

My thinking is that I need to query the first table and then query the second.

Journal looks like:
JournalID
Poster
Date
Comment

Journal_Messages
MessageID
JournalID
Poster
Date
Comment

Can I simply join the two tables together to get the info I want?

[devinemke]

sure. lets say you want to grab everything (both the journal and all of it's associated messages) for JournalID #7:

SELECT Journal.*, Journal_Messages.* FROM Journal, Journal_Messages WHERE Journal.JournalID = Journal_Messages.JournalID AND Journal.JournalID = 7

[furious5]

I had already tried a similar query to this, but it only seems to return the records from the journal_messages table, perhaps it is something to do with how I am iterating with the recordset...

[devinemke]

I had already tried a similar query to this

well, i don't know what your "similar" query looked like but the one i posted above (barring table/field name changes) would grab everything from both tables (SELECT Journal., Journal_Messages.)

[furious5]

The only difference in the SQL is I used SELECT * FROM journal, journal_messages.

My problem is that I want to display the message from journal table first (1 record) and then display the associated records from journal_messages (6 records).

At the moment all I return is the associated records (6 records), because I am joining the tables. To be honest I don't think it is the SQL that either you or I have used.

I think really I need to rethink the table structure and just use 1 table