MySQL joins

[Hostultrix]

hi,

Alright I have two tables.

Table 1: messages
has the following columns:
ts: datetime stamp
username: username of who sent the message
message: Text of the message itself

Table 2: users
Has these columns:
username: obvious
joined: What time they joined
status: enumerated, either active, away, or kicked

I need to do the following:

Select all messages which were sent no earlier than when the user joined. So the timestamp of the message must be greater than or equal to the time at which the user joined.

I tried this, but it doesn't work at all:

SELECT TIME(ts), messages.username, message FROM messages LEFT JOIN users ON ts >= joined

Not sure why it doesn't work, but I'd like to know :)

Does anyone have any ideas?

Thanks,
Brandon

[raf]

first off; your db-design could be better.
inside your users-table (i'd choose another name for that table if i were you...) you hould have a primary key column like userID. Inside the messagetable, you shoudl then store this PK-value instead of the username.
then about your actual question: i don't understand your timestampcondition. How could a user store a comment before registering? what username would you then store?

if it would make sense, then your query should be

SELECT TIME(messages.ts), messages.username, messages.message FROM messages INNER JOIN users ON messages.username = users.username WHERE messages.ts >= users.joined

mind you, TIME requires that you have at least MySQL version 4.1.1

[Hostultrix]

hi,

Thanks for the reply. I did figure this out; I just wasn't thinking clearly apparently.

SELECT TIME(ts), messages.username, message from messages left join users on users.username = 'someusername' where ts >= joined;

Actually it is a chat application, not a members area as I think you thought. But either way I figured it out :)

Thanks again.

Brandon