sql query

[Hari priya]

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]

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

 
  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

[Hari priya]

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]

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

like

HAVING c.time = max(c.time)

Mark

[Hari priya]

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]

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]

UserID is a number field correct?

Mark

[Hari priya]

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]

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