aspose file tools*
The moose likes Oracle/OAS and the fly likes sql  query Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "sql  query" Watch "sql  query" New topic
Author

sql query

Hari priya
Ranch Hand

Joined: Mar 11, 2004
Posts: 134
Hi -

I have a question with regards to an sql query that I am using in my java application. I hope I am posting it at the right place. I have been working on gettign the right query since 2 days. I will be happy if some one can help me with this.

I have two tables whose structure is...

USERS table
userID pid (each user is associated with certain number of pids)
------ -----
55555 111
55555 112
55555 113

66666 114
66666 115

CUSTOMERS table
pid time description
---- ------ ---------------
111 2004-09-16 blahblahblah
111 2004-09-15 blahblah
111 2004-09-14 blahblah

112 2004-09-10 blahblah
112 2004-09-11 blahblah
112 2004-09-01 blahblah

113 .. .....
113
113

114
114
114
114

My requirement is -

For a given user, get all pids from the table USERS corresponding to one user.
For the pids thus obtained, get the entire row for each id with the latest timestamp from the CUSTOMERS table.

say, for a user 55555 the output should be

111 2004-09-16 blanblah
112 2004-09-11 blahnblah
113 .... .....

Thank you for your time.
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17250
    
    6

I hope this isn't for your homework, because we really don't want to do your homework for you.

But here is the query



Mark


Perfect World Programming, LLC - Two Laptop Bag - Tube Organizer
How to Ask Questions the Smart Way FAQ
Hari priya
Ranch Hand

Joined: Mar 11, 2004
Posts: 134
Hi Mark -

Thanks for getting back to me.
Apparently, I have already written this query and the result for this query is going to be first record encountered for each pid and simply replace its time by the latest time. So I will get wrong results.

Basically, what it should do is to loop through records for each pid in the customers and display.

SELECT c.pid,
MAX(c.time),
c.description
FROM users u,
customers c
WHERE u.pid = c.pid
AND u.userid = 55555
GROUP BY c.pid,
c.description
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17250
    
    6

OK, well you can jsut add a "HAVING" clause at the end

like



Mark
Hari priya
Ranch Hand

Joined: Mar 11, 2004
Posts: 134
Hi Mark -

This is the query I wrote -

SELECT c.pid,
c.time,
c.description
FROM customers c

WHERE u.userid in (Select u.userid = 55555 FROM Users u)
GROUP BY c.pid
HAVING c.time=MAX(time)

This query returns 0 results. I did not get any syntax error so please ignore any mistakes in teh syntax as I am typing it. Please let me know where I am going wrong.
Thanks!
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17250
    
    6

You do not need the subquery in the where clause.

Take my first query I wrote, then add the one line of the "HAVING" clause.

Mark
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17250
    
    6

UserID is a number field correct?

Mark
Hari priya
Ranch Hand

Joined: Mar 11, 2004
Posts: 134
Thanks Mark for getting back.

I tried that way and I now I get errors in my query

and yes all the ids are number fields.
[ September 22, 2004: Message edited by: Hari priya ]
Hitesh R Patel
Greenhorn

Joined: Sep 22, 2004
Posts: 1
SELECT c.pid,
c.time,
c.description
FROM users u,
customers c
WHERE u.pid = c.pid
AND u.userid = 55555
AND c.time = ( select max(time) from customer where pid = c.pid );
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: sql query