Complex result query based on different column

[gepettocat]

Here is my dilemma. I have table 'mail'. I need to select all mail rows from the mail table based on two conditions:

 

1) There is only one message. There have not yet been any replies(orig_mess = 0). This would need to select the only message in the database in this condition.

 

2) There are one or more message related to the original message. This would be indicated by orig_mess = 'oringal message id'.

 

If there are any responses to any of the original messages I need to retrieve them all and then order by their id. Here is a table example:

 

 

mail.table

id from_user to_user orig_mess message_body

1 37 54 0 Hello

2 54 37 1 Hello back

3 60 37 0 Test

 

The task is that I need to display new mail messages. It could be a new conversation, or it could be a new message based off an older conversation. In either case I need to pull the most current(highest id) message and display that first. I then need to loop through any older messages(if applicable to the original) and display them from newest to oldest as well. I have tried and failed attempts to grab it all from one query, but that is what I want to do. If I can't do it from one, I would be fine with looping through a second query to get older messages. Any ideas on how this could be accomplished? Thanks.

This is all I have at the moment:

$result = mysql_query("SELECT Mail.id FROM Mail WHERE Mail.to_user='$_SESSION[user_id]' AND Mail.orig_mess='0' AND tbl.id2!=Mail.id UNION SELECT MAX(Mail.id) FROM Mail WHERE Mail.orig_mess!='0' ORDER BY id DESC")or die(mysql_error());
while($row = mysql_fetch_array($result)){

[Barand]

I was hoping your query might clarify what you were trying to do. There is insufficient data there to clearly identify the relationships.

 

eg If user 37 replies to message 2 will the new record's orig_mess be 1 or 2 ?

[gepettocat]

It would be 2. After some thought I don't think I can get this to work this way. I think I will have to create a conversation table and link all messages from the mail table to an id in the conversation table. I could then join the tables and loop through each conversation with a population from the mail table. Any thoughts on doing it that way instead?

[gepettocat]

So I now have added a conversation table, which all mail rows reference to(via the id key in the conversation table). I have a further question with this setup. I also have a table called Member_Profile(which holds first and last names of users). I need to get the full name for both the from_user and the to_user. I have tried using an alias table and it does not return the expected results. So again, what I am asking is to retrieve all messages the current user is a part of(from the mail table), and then get the current user's full name, and the other party's full name. The query in its simplest form:

 

$result = mysql_query("SELECT tbl1.to_name,tbl2.from_name,Mail.message FROM (SELECT CONCAT(Member_Profile.first_name,Member_Profile.last_name)AS to_name FROM Member_Profile,Mail WHERE Mail.to_user='$_SESSION[user_id]' AND Member_Profile.user_id=Mail.to_user)AS tbl1,(SELECT CONCAT(Member_Profile.first_name,Member_Profile.last_name)AS from_name FROM Member_Profile,Mail WHERE Conversations.from_user='$_SESSION[user_id]' AND Member_Profile.user_id=Mail.to_user) AS tbl2")or die(mysql_error());

 

It is certainly simple, but I have a hard time with this.

Edited January 8, 2013 by gepettocat

[Barand]

The way to get the names is to join twice to the member_profile table with different aliases

 

SELECT m.id,
	    CONCAT_WS(' ', mp1.first_name, mp1.last_name) as mailFrom,
	    CONCAT_WS(' ', mp2.first_name, mp2.last_name) as mailTo,
	    m.orig_mess, m.message
    FROM mail m
    INNER JOIN member_profile mp1 ON m.from_user = mp1.id
    INNER JOIN member_profile mp2 ON m.to_user = mp2.id