×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

mysql syntax

mysql syntax

mysql syntax

(OP)
I seem to have a problem with my queries

I have 2 tables with 2 columns each as you can see below.


auto            moto                                                                                 
code  time        code   time
a1    20001201    b1   20001203
a2    20001204    b2   20001206
a3    20001209    b3   20001210


now i want to make a query that searches both tables and returns me the code of the most recent date.That is , it must return in this case b3.
Here is what i write but it keeps coming out wrong:
$query = "SELECT  auto.code,moto.code FROM  auto,moto WHERE   auto.time>moto.offer   ";     

RE: mysql syntax

This one requires some trickery.  But here it is.  You need to use a left join to combine the tables, substring the codes to compare the last digits, then recieve a new column with a boolean value to see if one is greater than the other.

Let's just call the a1,a2,a3,b1,b2,b3 column "code" and the time column "mytime".  The tables will be named "a" and "b".  The new column with the boolean value, we'll call "test".  The statement looks like this:

select substring(a.code,2) as code,a.mytime as amytime,b.mytime as bmytime,a.mytime > b.mytime as test from a left join b on substring(a.code,2) = substring(b.code,2);

For the results, you'll get the code (without the a/b prefix), both times (amytime and bmytime) and a test value set to zero if b.mytime is larger and one if a.mytime is larger.

From here, you could use your script to attach the prefix to the returned code based on the test value and retrieve the remaining columns out of the respective table.

Hope this made sense.  Best of luck to you.

brendanc@icehouse.net

RE: mysql syntax

Actually.. here's an even better version that simply returns the code and time (like you initially wanted) of the most recent (NOTE:  It returns all the rows, the most recent will simply reside in the first result returned).  This uses a control flow structure (if/else) on the times, then takes the substring of the code (the digits) and concatenates them with the appropriate letter (a or b) depending on which is newer and does the same operation to find which time to return.  The results are then ordered by the returned time.  The field column definitions are the same as the example I used above, the codes are returned in a new column "result" and the time is returned in "newtime".  

select if(a.mytime > b.mytime,concat('a',substring(a.code,2)),concat('b',substring(a.code,2))) as result,if(a.mytime > b.mytime,a.mytime,b.mytime) as newtime from a left join b on substring(a.code,2) = substring(b.code,2) order by newtime desc;

Like I said.. a little tricky, but there you have it.  It's all fun in the end.

Take care,

brendanc@icehouse.net

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close