Solved

SQL WHERE

Posted on 2011-09-12
6
330 Views
Last Modified: 2013-12-13
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 ";

Open in new window

0
Comment
Question by:Luey
  • 3
  • 3
6 Comments
 
LVL 82

Accepted Solution

by:
hielo earned 500 total points
ID: 36524259
Try the query below.

PS: It's not clear which fields belong with which table, but in the Sub-query change the "M1." prefix to "M2." where appropriate. In other words, IF  the field "con_to_delete" belongs to the "message" table, then change:
 M1.con_to_delete to  M2.con_to_delete.  The same rationale applies to the other fields in the subquery.
$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 `message`.`date`=(SELECT MAX(M2.`date`) FROM message_control M1, message M2 WHERE ($username_saved = M1.con_from_id AND M1.con_from_delete = 0 OR $username_saved = M1.con_to_id AND M1.con_to_delete = 0) ) ORDER BY mes_entry_date DESC ";

Open in new window

0
 

Author Closing Comment

by:Luey
ID: 36524357
That works great.  I just wish I understood what is happening.  Any comments would be appreciated.
Thanks
0
 
LVL 82

Expert Comment

by:hielo
ID: 36524952
>> 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.

0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:Luey
ID: 36525285
Thanks for taking the time to explain.  I understand now and it will help me a great deal.
0
 
LVL 82

Expert Comment

by:hielo
ID: 36525784
Glad to help.

Regards,
Hielo
0
 

Author Comment

by:Luey
ID: 36557011
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.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question