mysql syntax

[rhodesiascout]

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 ";

 

[sophisticate]

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

 

[sophisticate]

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