Q

Using a query without a subselect

MySQL expert Scott Noyes explains how to use a query without a subselect for a user working with MySQL 4.0.24.

I have a database which I need to get into from two tables on. Let's say that they are called 'scan' and 'detail.'

Scan
id int
computer varchar
scandate date
Detail
id int
scanid int
isinstalled varchar

Ok, so some data:
scan
1,test1,2006-08-01
1,test1,2006-08-15
2,test2,2006-08-15
3,test3,2006-08-01
3,test3,2006-08-15
detail
1,1,false
2,1,true
3,2,false
4,3,false
5,3,true

I need to return a reset set that has only the most recent result. For example:

test1,2006-08-15,true test3,2006-08-15,true

I have tried to do this with SQL and cannot figure it out. I cannot use a subselect since I am using MySQL 4.0.24 and cannot upgrade at this time. I need a query that will work without the subselect. I have tried to do an inner join and cannot get that to work properly because I do get the right date and computer, but the installed info does not match the date.

Here is what I have tried:

select distinct s.computer, d.isinstalled, max(s.scandate) from scan s inner
join scan s2 using(scandate), detail d where s.id = d.scanid group by
s.computer;

Can you help me?

In the example given, there is no unique join condition between the "scan" and "detail" tables. I assume that the "scan" row with the latest date corresponds to the `detail` row with the highest auto-incremented "id" for the correlated "scanId." For this particular query, if that assumption is valid, we can still get the desired results. For other queries, it would be helpful to have some field(s) to absolutely match a row in "detail" to a row in "scan."

We're going to use the method mentioned by user Csaba Gabor in the comments section below 3.6.4. The Rows Holding the Group-wise Maximum of a Certain Field.

By LEFT JOINing the "scan" table to itself with the left "scandate" less than the right one, and checking for a right value of NULL, we'll end up with just the rows that have no greater "scandate" for that "id." We'll do the same with the "detail" table, using "id" in the inequality:

SELECT
 scan.*,
 detail.*
FROM
 scan
 LEFT JOIN scan s2 ON (scan.computer = s2.computer AND scan.scandate 
  
This was first published in September 2006

Dig deeper on Open source databases

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataCenter

SearchServerVirtualization

SearchCloudComputing

SearchEnterpriseDesktop

Close