Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL ignore dupe records

Posted on 2014-03-11
9
Medium Priority
?
238 Views
Last Modified: 2014-03-17
Hi,

I'm writing a query that will return people based on the office they exist in. The data below is a made sample.
I need the result set of a query for everyone in office 'nyc', except for 'mike smith' because he is already in the office 'la'. I only want people returned for a given office if they don't already exist in another office.

So based on the data below, for the result set I would like
john doe and john lennon.

It seems like it should be simple but I'm having a hard time.
Thanks!
Nacht


Table: employees

fname: mike
lname: smith
office: nyc

fname: mike
lname: smith
office: la

fname: john
lname: doe
office: nyc

fname: john
lname: lennon
office: nyc
0
Comment
Question by:nachtmsk
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 10

Expert Comment

by:aboo_s
ID: 39921130
SELECT fname,lname FROM employees WHERE office = 'nyc' AND COUNT(SELECT * FROM employees WHERE fname = fname AND lname=lname AND office = 'la') = 0 ;
0
 
LVL 40

Expert Comment

by:lcohan
ID: 39921140
select fname,lname,office from employees
group by office
having COUNT(distinct fname) = 1
0
 
LVL 10

Expert Comment

by:aboo_s
ID: 39921142
or SELECT fname as fname1,lname as lname1 FROM employees WHERE office = 'nyc' AND COUNT(SELECT * FROM employees WHERE fname = fname1 AND lname=lname1 AND office = 'la') = 0 ;
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 40

Expert Comment

by:lcohan
ID: 39921150
OR:

select fname,lname,office from employees
group by office
having COUNT(fname+lname) = 1
0
 
LVL 1

Author Comment

by:nachtmsk
ID: 39921199
Icohan,
What your suggesting seems exactly what I need.
However, when I'm running it I'm getting a syntax error. I'm using SQL Server 2005

Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near ')'.


Ideas?
Nacht
0
 
LVL 1

Author Comment

by:nachtmsk
ID: 39921222
I tried this one: select fname,lname,office from employeeList
group by office
having COUNT(fname+lname) = 1

and got this error message
Msg 8120, Level 16, State 1, Line 1
Column 'employeeList.fname' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
0
 
LVL 40

Expert Comment

by:lcohan
ID: 39921255
Sorry I added those to the count and miss them from group by...this should work:

select fname,lname,office from employeeList
group by office,fname,lname
having COUNT(fname+lname) = 1

For "proof" you could add the "count" column and try the reverse like

select fname,lname,office,COUNT(fname+lname) as numb from employeeList
group by office,fname,lname
having COUNT(fname+lname) > 1
0
 
LVL 1

Author Comment

by:nachtmsk
ID: 39921302
Thanks.
that worked, but it returns too much. I don't want EITHER record returned if there is a dupe of it. The query you gave me returns once instance of the duped record. I want everyone except all instances of the dupe record. Possible?
0
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 total points
ID: 39921399
try this query
select fname,lname
  from employees
 group by fname,lname
having count(distinct office) = 1
  and max(office) = 'nyc'
  and min(office) = 'nyc'

Open in new window

http://sqlfiddle.com/#!3/4216a/1
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question