Query Help Please

Question

Query Help Please

Mike Feuti

Old Hand

Points: 323
More actions
December 9, 2008 at 11:16 am

#125562

I'm working on a query that involves the Activity table and the Name table in my database. Basically I want to pull out all unique records that have duplicate email addresses and have committee activity.
For example, record #1565 would have someone@home.com as the email address and has committee activity. Record #1445 also has someone@home.com as their email address (usually meaning a duplicate record) and also has committee activity.
I've tried several different ways and can get the records from the database that have duplicate email addresses but as soon as I specify the committee activity it lists all records that have committee activity and no longer just the ones that have duplicate email addresses. Below is what I've worked up so far but it just isn't doing it. Any help is much appreciated.
Thank you in advance.
select
name.ID, activity.product_code, name.email, count(*) as number
from
Activity,Name
where Activity.id=name.id
and activity.product_code like 'committee%'
and name.email <>''
group by
name.ID, activity.product_code, name.email having count(*) >1

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Please post table definitions, sample data and desired output.

Read this to see the best way to post this to get quick responses. http://www.sqlservercentral.com/articles/Best+Practices/61537/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Mike Feuti Old Hand Points: 323 More actions · Answer 2

Sorry I read the link and I'm not fully understanding what I need to provide. There are two tables, the Activity table and the Name table. For an output I need the name.id the Activity.prodcut_code grouped where the name.email is identical.

name.email (where duplicate)

first name.id all activity.product_code

second name.id all activity.product_code

mfeuti@aami.org

ID#45612 committee/ts150

ID#54874 committee/jc150

Please tell me what else I need to provide.

Garadin One Orange Chip Points: 29613 More actions · Answer 3

Say for instance, you have a table Products with three fields, ProductIDent, ProductID and UnitPrice

You could supply the information about this table like this:

Table Products

ProductIDent (identity)

ProductID

UnitPrice

Data

ProductID UnitPrice

P1 0

P1 5

P1 10

(Which is extremely difficult to do anything with)

Or you could provide it like this, which is extremely easy to use.

DECLARE @Products TABLE(

ProductIdent INT IDENTITY(1,1),

ProductID VARCHAR(10),

UnitPrice money)

INSERT INTO @Products(ProductID, UnitPrice)

SELECT 'P1',0 UNION ALL

SELECT 'P1',5 UNION ALL

SELECT 'P1',10 UNION ALL

SELECT 'P1',20 UNION ALL

SELECT 'P2',0 UNION ALL

SELECT 'P2',50 UNION ALL

SELECT 'P2',100 UNION ALL

SELECT 'P2',200

The article is intended to help you provide data in the bottom format.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Mike Feuti Old Hand Points: 323 More actions · Answer 4

OK let's try this. Is this what you are looking for?

From name table:

SELECT '10002',Walton@email.chop.edu,'Vernathan Walton' UNION ALL

SELECT '10004',craig.engstrom@wfhc.org,'Craig W. Engstrom, CBET' UNION ALL

SELECT '10006',,'Larry R. Pallatt, CBET' UNION ALL

SELECT '10007',earhart.fay@hunterdonhealthcar,'Fay M. Earhart' UNION ALL

SELECT '10011', ,'Tom Clark' UNION ALL

SELECT '10012',,'Benjamin K. Pickering, CBET' UNION ALL

SELECT '10013',,'William H. Copestakes, CBET AA' UNION ALL

SELECT '10018',owen.raysor@us.army.mil,'Owen M. Raysor, CBET' UNION ALL

Mike Feuti Old Hand Points: 323 More actions · Answer 5

The column from the activity table I'm trying to pull, product_code will not display using the same query that I used for the name table. It is varchar but I used the appropriate + CAST(Value AS VARCHAR)+',' but it still did not display in the results?

Garadin One Orange Chip Points: 29613 More actions · Answer 6

Mike Feuti (12/9/2008)
OK let's try this. Is this what you are looking for?
From name table:
SELECT '10002',Walton@email.chop.edu,'Vernathan Walton' UNION ALL
SELECT '10004',craig.engstrom@wfhc.org,'Craig W. Engstrom, CBET' UNION ALL
SELECT '10006',,'Larry R. Pallatt, CBET' UNION ALL
SELECT '10007',earhart.fay@hunterdonhealthcar,'Fay M. Earhart' UNION ALL
SELECT '10011', ,'Tom Clark' UNION ALL
SELECT '10012',,'Benjamin K. Pickering, CBET' UNION ALL
SELECT '10013',,'William H. Copestakes, CBET AA' UNION ALL
SELECT '10018',owen.raysor@us.army.mil,'Owen M. Raysor, CBET' UNION ALL

The whole idea is to be able to simply run the statement you provide to have a table with sample data in it. Look again at my example code. I provide a CREATE TABLE (well, in my case DECLARE, because I used a table variable, which I prefer, but people can change that easily enough based on their own preferences) Statement, with column definitions, which "just so happens" to include the field types and is one of the pieces of information we often need.

Then, I have a block of sample data that is automatically inserted into that table that I just created.

You are missing the create table statement, missing the insert statement, and the syntax of your select is all messed up (double commas, missing quotes, extra union at the end).

Not trying to give you a hard time, just explaining the theory behind what we ask for. The time should be spent on the problem, not on setting up a test environment we don't care about and will delete the second your problem is solved.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Mike Feuti Old Hand Points: 323 More actions · Answer 7

Ok I'm fairly new at this but I'm trying.

Here is the data from the activity table.

SELECT 361708,000BIOT2, UNION ALL

SELECT 361865,000BIOT2, UNION ALL

SELECT 362184,000BIOT2, UNION ALL

SELECT 362215,000BIOT2, UNION ALL

SELECT 362261,000BIOT2, UNION ALL

SELECT 362319,000BIOT2, UNION ALL

SELECT 362570,000BIOT2, UNION ALL

SELECT 362764,000BIOT2, UNION ALL

SELECT 362786,000BIOT2, UNION ALL

The double commas is where emails don't exist. I'm not sure why quotes are missing. Let me work on the schema now. I really do appreciate your patience.

Mike Feuti Old Hand Points: 323 More actions · Answer 8

OK i'm learning a lot just getting help. Which really is great.

I think I have what you need now.

SET IDENTITY_INSERT #test ON

--===== Insert the test data into the test table

INSERT INTO #test

(ID, email)

SELECT '10002','Walton@email.chop.edu' UNION ALL

SELECT '10004','craig.engstrom@wfhc.org' UNION ALL

SELECT '10007','earhart.fay@hunterdonhealthcar' UNION ALL

SELECT '10018','owen.raysor@us.army.mil'

--===== Set the identity insert back to normal

SET IDENTITY_INSERT #test OFF

SET IDENTITY_INSERT #test2 ON

--===== Insert the test data into the test table

INSERT INTO #test2

(ID, product_type)

SELECT '361708','committee/150' UNION ALL

SELECT '361865','committee/170' UNION ALL

SELECT '362184','committee/190' UNION ALL

SELECT '362215','committee/150' UNION ALL

SELECT '362261','be78' UNION ALL

SELECT '362319','bf129'

--===== Set the identity insert back to normal

SET IDENTITY_INSERT #test OFF

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Mike Feuti (12/9/2008)
Ok I'm fairly new at this but I'm trying.

That's fine.

I've edited the data you posted so that it looks the way we generally want it. Do you get the gist of it now?

Insert Into Activity (< column list for activity > )

SELECT 361708,'000BIOT2' UNION ALL

SELECT 361865,'000BIOT2' UNION ALL

SELECT 362184,'000BIOT2' UNION ALL

SELECT 362215,'000BIOT2' UNION ALL

SELECT 362261,'000BIOT2' UNION ALL

SELECT 362319,'000BIOT2' UNION ALL

SELECT 362570,'000BIOT2' UNION ALL

SELECT 362764,'000BIOT2' UNION ALL

SELECT 362786,'000BIOT2'

Basically, the more work people have to do to get tables to test with, the less likely they are to spend time answering your questions. We're all volunteers here, we post in out spare time.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Mike Feuti Old Hand Points: 323 More actions · Answer 10

Yes I do get it now. Thank you. It'll be much easier for both sides next time!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

Mike Feuti (12/9/2008)
I think I have what you need now.

Almost. Just need the create statement for the two temp tables please.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

engintoksoz SSC Veteran Points: 264 More actions · Answer 12

I am not sure I understand correctly but I think having a subquery, to retrieve those records with the criteria first then apply your group by to find the duplicates, might solve your problem (assuming everything else is working).

select nid, pcd, email, count(*) as number

from

(select name.ID as nid, activity.product_code as pcd, name.email as email

from Activity,[name]

where Activity.id=[name].id

and activity.product_code like 'committee%'

and name.email <>''

) stbl

group by nid, pcd, email having count(*) >1

Mike Feuti Old Hand Points: 323 More actions · Answer 13

IF OBJECT_ID('TempDB..#test','U') IS NOT NULL

DROP TABLE #test

CREATE TABLE #test

( ID int IDENTITY(1,1) PRIMARY KEY CLUSTERED,

email varchar, )

IF OBJECT_ID('TempDB..#test2','U') IS NOT NULL

DROP TABLE #test2

CREATE TABLE #test2

( ID int IDENTITY(1,1) PRIMARY KEY CLUSTERED,

product_type varchar, )

Mike Feuti Old Hand Points: 323 More actions · Answer 14

engintoksoz (12/9/2008)

I am not sure I understand correctly but I think having a subquery, to retrieve those records with the criteria first then apply your group by to find the duplicates, might solve your problem (assuming everything else is working).

I've gotten these same results but this query gives me everyone who is in any committee and every committe they are on. I'm only looking for the records that have duplicate email addresses and committee activity. What you wrote looks like it would give exactly that but I can't figure out why it doesn't. I used a view and got the exact same results. I'm really banging my head on this one but thank you very much.