This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes JDBC and the fly likes problem framing subquery... Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "problem framing subquery..." Watch "problem framing subquery..." New topic
Author

problem framing subquery...

Madhu Chandra
Greenhorn

Joined: Jan 14, 2003
Posts: 2
Hi there!
I wrote the below query to give me results from a table which contains:
The most recent time (max(sessionDate)) from the visits made by the user(tom) to the learningObject(lo_1,...lo_n) in a package(SingleCourseEx).
//query - 1
String query = SELECT test.STUDENT_ID,test.LEARNING_OBJECT_NAME,MAX(test.SESSION_DATE) FROM SESSION AS test WHERE test.PACKAGE_ID='SingleCourseEx' And test.STUDENT_ID='tom' GROUP BY test.LEARNING_OBJECT_NAME,test.STUDENT_ID
this query gives me proper results:
tom: Conduct of Vessels in Sight of One Another : 2003-01-07 12:00:36
tom: Exam : 2003-01-07 12:10:33
tom: Lights & Shapes : 2003-01-07 12:12:45
---------------------------------------------------------
Now I want to get the other details(sessionID) of that rows which are unique (that means I cant ask for them in my above query as they are aggregated)
So I wrote the below query(using my query-1 as a subquery)and it doesn't work the way I wanted..
results are:
tom:4:Conduct of Vessels in Sight of One Another : 2003-01-07 12:00:36
tom:24:Conduct of Vessels in Sight of One Another : 2003-01-04 10:00:36
tom:2:Exam : 2003-01-07 12:10:33
tom:26:Lights & Shapes : 2003-01-07 12:12:45
tom:22:Exam : 2003-01-09 14:10:33
.....
....
That means it's picking up all rows

Query - 2 with the above subquery
SELECT original.STUDENT_ID,original.SESSION_ID,original.LEARNING_OBJECT_NAME,
original.SESSION_DATE FROM SESSION AS original WHERE EXISTS (SELECT test.STUDENT_ID,test.LEARNING_OBJECT_NAME,MAX(test.SESSION_DATE) FROM SESSION AS test WHERE test.PACKAGE_ID='SingleCourseEx' And test.STUDENT_ID='tom' GROUP BY test.LEARNING_OBJECT_NAME,test.STUDENT_ID)
All I wnated to do here is
Select "all/rest of the columns"
from session
where student_id, loName, session_date matches
the results of my sub query.
I could use "IN" operator if the matching is only for one field, but don't know how to match multiple fields.
I am using Access as my database.
Any ideas on this?? Any suggestions would be of great help.
-M
Michael Matola
whippersnapper
Ranch Hand

Joined: Mar 25, 2001
Posts: 1746
    
    2
Several things
1. Are you sure of the results you provided from the first query? I would think the query would return the following.
tom: Exam : 2003-01-09 14:10:33
and not
tom: Exam : 2003-01-07 12:10:33
Are you by any chance storing your dates as text fields?
2. For questions like these, in general it's really helpful if you give the key structure of the table(s) in question.
3. That said, based on some assumptions I've made about your table, I think the query you're looking for is something along the lines of the following. (If not, you'll have to provide some more details about what you're trying to do.)

Adjusting fieldnames, of course. This query rests on the assumption that your table has 3 key fields: studentId, learningObjectName, and sessionDate. The correlated subquery returns the maximum sessionDate for what is essentially a grouping by studentId and learningObjectName, only the "grouping" is done not with the "group by" clause, but with a join to the enclosing table.

4. Please take another look at the JavaRanch Naming Policy. We'd like to have you continue posting here but "nmhb 23" doesn't comply with the naming policy, so please update your display name. You can do so here.
Happy 'ranching.
Michael Matola
whippersnapper
Ranch Hand

Joined: Mar 25, 2001
Posts: 1746
    
    2
If this indeed solves your problem, you might want to take a look at this other post which goes into a little more detail on this kind of querying.
[ January 14, 2003: Message edited by: Michael Matola ]
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: problem framing subquery...
 
Similar Threads
SQL How to not select the record
java sql help
Query problem with MySQL 5
need 2 rows rather than 5 from this query
How to identify Auth method NTLMv1 or NTLMv2