need help writing inner join

[TechBoyJK]

I need to write an inner join for a small messaging app. In my sql experience I haven't had to do many inner joins so I'm kinda cloudy on it.

I have the following two tables and I want to select ALL columns, but I want to declare each column in the select rather than justing using "select all".

So that the same message can be sent to multiple users without having to have redundant entries for the content and subject of the message, I've split the tables into two so that there can be many entries for the messages, but if the many messages have the same content, they all point to the same content record. So (tbl_member_messages) is MANY to (tbl_member_message_content) One. The join will be on "message_content_id" as that is the key that is shared between tables.

-----------------------------------------

tbl_member_messages

message_id (int-identity-pk)
message_content_id (int-fk)
message_sender_user_name (varchar-30)
message_recipient_user_name varchar-30)
message_is_new (bit)
message_is_read (bit)
message_is_replied (bit)
message_is_flagged (bit)
message_is_urgent (bit)
message_is_sender_deleted (bit)
message_is_recipient_deleted (bit)
message_sent_date (datetime-null)
message_read_date (datetime-null)
message_referrer_message_id (int-null)
message_sender_ip (varchar-15)


tbl_member_messages_content

message_content_id (int-identity-pk)
message_subject (varchar-50)
message_body (varchar-1000)

-------------------------------


Can someone help me get the SQL statement setup for this?

 

[Pacemaker]

Originally posted by: TechBoyJK
I need to write an inner join for a small messaging app. In my sql experience I haven't had to do many inner joins so I'm kinda cloudy on it.

I have the following two tables and I want to select ALL columns, but I want to declare each column in the select rather than justing using "select all".

So that the same message can be sent to multiple users without having to have redundant entries for the content and subject of the message, I've split the tables into two so that there can be many entries for the messages, but if the many messages have the same content, they all point to the same content record. So (tbl_member_messages) is MANY to (tbl_member_message_content) One. The join will be on "message_content_id" as that is the key that is shared between tables.

-----------------------------------------

tbl_member_messages

message_id (int-identity-pk)
message_content_id (int-fk)
message_sender_user_name (varchar-30)
message_recipient_user_name varchar-30)
message_is_new (bit)
message_is_read (bit)
message_is_replied (bit)
message_is_flagged (bit)
message_is_urgent (bit)
message_is_sender_deleted (bit)
message_is_recipient_deleted (bit)
message_sent_date (datetime-null)
message_read_date (datetime-null)
message_referrer_message_id (int-null)
message_sender_ip (varchar-15)


tbl_member_messages_content

message_content_id (int-identity-pk)
message_subject (varchar-50)
message_body (varchar-1000)

-------------------------------


Can someone help me get the SQL statement setup for this?

SELECT
message_id, tbl_member_messages.message_content_id, message_sender_user_name, message_recipient_user_name, message_is_new, message_is_read, message_is_replied, message_is_flagged, message_is_urgent, message_is_sender_deleted, message_is_recipient_deleted, message_sent_date, message_read_date, message_referrer_message_id, message_sender_ip, message_subject, message_body
FROM tbl_member_messages
INNER JOIN tbl_member_messages_content
ON tbl_member_messages.message_content_id = tbl_member_messages_content.message_content_id

that is the basic sytax you are looking for I think.

edit: added your column names.
edit2: fixed the formating a bit

 

[TechBoyJK]

here is what i have so far..

SELECT *

FROM tbl_member_messages

INNER JOIN tbl_member_messages_content on tbl_member_messages.message_content_id = tbl_member_messages_content.message_content_id

;

 

[Pacemaker]

Originally posted by: TechBoyJK
here is what i have so far..

SELECT *

FROM tbl_member_messages

INNER JOIN tbl_member_messages_content on tbl_member_messages.message_content_id = tbl_member_messages_content.message_content_id

;

I added the column names above, but what you put there looks correct.

 

[TechBoyJK]

Originally posted by: Pacemaker

Originally posted by: TechBoyJK
here is what i have so far..

SELECT *

FROM tbl_member_messages

INNER JOIN tbl_member_messages_content on tbl_member_messages.message_content_id = tbl_member_messages_content.message_content_id

;

I added the column names above, but what you put there looks correct.

cool yea that worked. I just had to add a "where" clause so as to not return ALL records, just those that applied to the particular user.

thanks again!