SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Query for unique records
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pdeluca
Starting Member

USA
2 Posts

Posted - 11/26/2013 :  15:04:05  Show Profile  Reply with Quote
I have a database for course completion results that contains data such as:

Smith John Course1 Completed
Smith John Course2 Started
Jones Bill Course1 Started
Jones Bill Course2 Started
White Don Course2 Completed

I need a query that will return only the Jones records. The names don't matter, the courses don't matter. What matters is that I return only those records where a user has only started any course. If they have completed one and started another, that records needs to be excluded. They can start and complete multiple courses--in which case the record is entered as Completed--and start multiple courses without completing them.

Thanks!

TG
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 11/26/2013 :  15:22:30  Show Profile  Reply with Quote
said another way, you only want rows for users that have not completed ANY courses?
so maybe this? (assumes a table call [myTable] with columns: (name, course, status))

select t.name, t.course, t.status
from   myTable t
left outer join
       (
       select name
       from   myTable
       where  status = 'completed'
       group by name
       ) d
              on d.name = t.name
where  d.name is null


Be One with the Optimizer
TG
Go to Top of Page

pdeluca
Starting Member

USA
2 Posts

Posted - 11/26/2013 :  16:09:23  Show Profile  Reply with Quote
Thanks, TG. That was enough to get me where I needed to go.
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 11/26/2013 :  16:16:42  Show Profile  Reply with Quote
Cool. Glad you got something out of it.

Be One with the Optimizer
TG
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000