Ask the Expert

Using a query without a subselect

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?

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: