Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Query for unique records

Author  Topic 

pdeluca
Starting Member

2 Posts

Posted - 2013-11-26 : 15:04:05
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
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-11-26 : 15:22:30
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

2 Posts

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

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-11-26 : 16:16:42
Cool. Glad you got something out of it.

Be One with the Optimizer
TG
Go to Top of Page
   

- Advertisement -