Forums Register Login

Stumped on a SQL Statement

+Pie Number of slices to send: Send
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.
+Pie Number of slices to send: Send
Did you try using Outer Join
[ April 15, 2005: Message edited by: Jayesh Lalwani ]
+Pie Number of slices to send: Send
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 ]
+Pie Number of slices to send: Send
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 ]
+Pie Number of slices to send: Send
 

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.
+Pie Number of slices to send: Send
 

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 ]
+Pie Number of slices to send: Send
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.
+Pie Number of slices to send: Send
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
+Pie Number of slices to send: Send
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 ]
And tomorrow is the circus! We can go to the circus! I love the circus! We can take this tiny ad:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com


reply
reply
This thread has been viewed 1285 times.
Similar Threads
Designing a Simple Java Bean
Your favorite cartoon character and cartoon?
print database values with radio button
How to give search query if we select one than one value from combo box
jsf - dataTable with external filter
More...

All times above are in ranch (not your local) time.
The current ranch time is
Mar 28, 2024 18:10:21.