GeeCON Prague 2014*
The moose likes JDBC and the fly likes Stumped on a SQL Statement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "Stumped on a SQL Statement" Watch "Stumped on a SQL Statement" New topic
Author

Stumped on a SQL Statement

Corey McGlone
Ranch Hand

Joined: Dec 20, 2001
Posts: 3271
Hi all,

I'm trying to write a SQL statement (or series of statements) in a stored procedure and I'm having trouble working out the logic.

I have 3 tables that look something like this:



Now, the idea here is that we have multiple services that can be offered. What I want to get from the data is when the last time was each service was performed. I can do that but, in the case that a specific service was never performed (as might be the case for a new customer), we'd want to know that.

So, let's say I have data that looks like this:



So, if you look at that, Mickey Mouse has had all 3 services performed but Donald Duck (customer 2) has only had 1 service performed. Now, let's say we want to see when the last time our customers have had Service Type 3 done, I'd like to get a result set that looks like this:



It's important that we know that Customer 2 has never had this done - because we might want to contact that customer about it.

Boy I hope that made sense. I'm having a really hard time wrapping my head around this SQL statement. If anyone out there has any ideas, I'd love to hear them.

Thanks.


SCJP Tipline, etc.
Jayesh Lalwani
Ranch Hand

Joined: Nov 05, 2004
Posts: 502
Did you try using Outer Join
[ April 15, 2005: Message edited by: Jayesh Lalwani ]
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

Corey,

I think this query will give you desired result
This query is ORACLE compatible


you have posted a similar problem few days back here ,I was just wondering if you referred my reply in same post. Only a outer join need to be added

Hope this solve your problem

Shailesh
[ April 16, 2005: Message edited by: Shailesh Chandra ]

Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
Corey McGlone
Ranch Hand

Joined: Dec 20, 2001
Posts: 3271
Originally posted by Jayesh Lalwani:
Did you try using Outer Join


I thought all I needed to add was an Outer Join, but I wasn't sure if that was the case and, even if it was, I wasn't sure about the proper syntax. I'm trying to join all 3 tables at once and I seem to be having problems with that.

I was thinking that the simplest way to do this might be to perform an outer join on two tables and then a left join on the third. So, my thought was to do an outer join on Customers and Service Types, first, giving me something like this:



Once I've got that table, I should be able to do a left join between that and my Services_Rendered table. Does that make sense?

My problem is that I can't seem to work out the syntax for that outer join. As far as I can tell, the syntax for an outer join in SQL Server is:

SELECT * FROM Customers
FULL OUTER JOIN Service_Types
ON [Criteria]

However, in this case, I have no idea what to put in for criteria. The two tables have nothing at all in common. None of their columns match. I really just want every row in the first table to be matched with every row from the second table. What's the syntax for that?

Thanks.
[ April 18, 2005: Message edited by: Corey McGlone ]
Corey McGlone
Ranch Hand

Joined: Dec 20, 2001
Posts: 3271
Originally posted by Shailesh Chandra:
you have posted a similar problem few days back...


Shailesh,

Thanks for the feedback - you've got a good memory - this question stems from the exact same stored procedure. Now, the only difference is that the requirements have changed a little bit. Unfortunately, I'm using SQL Server instead of Oracle so I'm not sure how usable your query would be for me.
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

Originally posted by Corey McGlone:

Unfortunately, I'm using SQL Server instead of Oracle so I'm not sure how usable your query would be for me.


Even I was looking at previous post but I could not find database information.

Any way If you have solved previous problem only a outer join would suffice to solve problem. but you have already mentioned your problem to use outer join
That is what I can think as of now.I will get back if any other solution come in my mind

Thanks
Shailesh
[ April 18, 2005: Message edited by: Shailesh Chandra ]
Corey McGlone
Ranch Hand

Joined: Dec 20, 2001
Posts: 3271
Well, I've gotten a little further, but I'm still stuck - now it's just on a different part of the procedure. My solution to the previous problem was to use a CROSS JOIN to get the two tables linked together and then do a LEFT JOIN with the remaining table. That seems to have done the trick, but now I'm left with a little cleanup to do, at the end.

Is there a way to delete a number of rows based on the result of another query? For example, looking at my "nearly final" data, I want to do a query, which is going to give me a number of Patient IDs and Service Types, in pairs. Those are the records I want to delete. Is there a way to easily do that with a DELETE statement?

I'm not sure if I said that very clearly or not, so here's a quick example:



Now, I might do a query on this that returns this:



So, I'd like to quickly delete those rows and be left with something like this:



Anyone know how to do that?

Thanks.
Jayesh Lalwani
Ranch Hand

Joined: Nov 05, 2004
Posts: 502
There are couple of ways you can do this. I think the best way would be to use a NOT IN clause (It depends on the size of your tables. But if your second query returns only a couple of rows then NOT IN should work ok). But I don't know how to do NOT IN clause for multiple columns.

So, you might want to try using NOT EXISTS with a subquery

Not IN might work like this.



NOT exists will work like this


I think that should work. Although the subquery will run for every row in your main query
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

Hi Corey,

Jayesh has given you the suugestion, I want to add only on more option


let say you have made a query which result in data given below and assuming that you have query something like

and output is



now keeping one thing in mind that you need data with latest Date

you can make a query like





thanks
[ April 18, 2005: Message edited by: Shailesh Chandra ]
 
GeeCON Prague 2014
 
subject: Stumped on a SQL Statement