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.
Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
Joined: Dec 20, 2001
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?
Originally posted by Shailesh Chandra: you have posted a similar problem few days back...
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.
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
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?
Joined: Nov 05, 2004
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