SQL WHERE
Hello I have a SELECT statement that involves two tables. message_control and message. The file message has an entry date and I want to join these two files and select the record with the newest entry date in "message". Order By is not what I am looking for.
$query_message_links = "SELECT * FROM message_control, message WHERE ($username_saved = con_from_id AND con_from_delete = 0 OR $username_saved = con_to_id AND con_to_delete = 0) AND [Here is where I need to choose the newest record in message] ORDER BY mes_entry_date DESC ";
$query_message_links = "SELECT * FROM message_control, message WHERE ($username_saved = con_from_id AND con_from_delete = 0 OR $username_saved = con_to_id AND con_to_delete = 0) AND [Here is where I need to choose the newest record in message] ORDER BY mes_entry_date DESC ";
$query_message_links = "SELECT * FROM message_control, message WHERE ($username_saved = con_from_id AND con_from_delete = 0 OR $username_saved = con_to_id AND con_to_delete = 0) AND [Here is where I need to choose the newest record in message] ORDER BY mes_entry_date DESC ";ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>> I just wish I understood what is happening
Looking at your original post, if you remove the " AND [Here is where I need to choose the newest record in message]" part, then you are left with:
"SELECT * FROM message_control, message WHERE ($username_saved = con_from_id AND con_from_delete = 0 OR $username_saved = con_to_id AND con_to_delete = 0) ORDER BY mes_entry_date DESC "
THAT specific query tells ME that the remaining you already have the results you want. For the sake of clarity, let's call that resultset R1.
So your next step is to narrow down R1 even more. From the description of your post, I figured that you have a field named date in you message table. That means that in R1 there must the an date field/column.
In your WHERE clause I am basically doing:
WHERE R1.date=(SOMETHING)
Let's call that SOMETHING R2, which is to be another resultset. If you look closer, R2 is a "duplicate" of R1, with one minor difference. R1 does SELECT *; by contrast, R2 only does SELECT MAX(`M1`.`date`), limiting the number of rows in R2 to just one - the one with the maximum date.
In the subquery, I used an alias (M1 and M2) for each of the tables (message_control, message ) since the tables used for R1 and R2 are the same. So for the query in R2 to not get "confused" with "message_control" table used in R1, you need to alias it when doing your R2 query.
Looking at your original post, if you remove the " AND [Here is where I need to choose the newest record in message]" part, then you are left with:
"SELECT * FROM message_control, message WHERE ($username_saved = con_from_id AND con_from_delete = 0 OR $username_saved = con_to_id AND con_to_delete = 0) ORDER BY mes_entry_date DESC "
THAT specific query tells ME that the remaining you already have the results you want. For the sake of clarity, let's call that resultset R1.
So your next step is to narrow down R1 even more. From the description of your post, I figured that you have a field named date in you message table. That means that in R1 there must the an date field/column.
In your WHERE clause I am basically doing:
WHERE R1.date=(SOMETHING)
Let's call that SOMETHING R2, which is to be another resultset. If you look closer, R2 is a "duplicate" of R1, with one minor difference. R1 does SELECT *; by contrast, R2 only does SELECT MAX(`M1`.`date`), limiting the number of rows in R2 to just one - the one with the maximum date.
In the subquery, I used an alias (M1 and M2) for each of the tables (message_control, message ) since the tables used for R1 and R2 are the same. So for the query in R2 to not get "confused" with "message_control" table used in R1, you need to alias it when doing your R2 query.
ASKER
Thanks for taking the time to explain. I understand now and it will help me a great deal.
Glad to help.
Regards,
Hielo
Regards,
Hielo
ASKER
For anyone who reads all this I found out later that it did not work as I had intended. It still only chose one record and that was not what I was looking for. I ended up putting a select inside my do while and it worked perfect.
ASKER
Thanks