Query for unique records - SQL Server Forums

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

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.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources