This week's book giveaways are in the Refactoring and Agile forums.
We're giving away four copies each of Re-engineering Legacy Software and Docker in Action and have the authors on-line!
See this thread and this one for details.
Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

sql query

 
Hari priya
Ranch Hand
Posts: 134
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Hari priya
Ranch Hand
Posts: 134
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
OK, well you can jsut add a "HAVING" clause at the end

like



Mark
 
Hari priya
Ranch Hand
Posts: 134
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
UserID is a number field correct?

Mark
 
Hari priya
Ranch Hand
Posts: 134
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 );
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic