December 9, 2008 at 11:16 am
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
December 9, 2008 at 11:30 am
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
December 9, 2008 at 11:47 am
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
ID#45612 committee/ts150
ID#54874 committee/jc150
Please tell me what else I need to provide.
December 9, 2008 at 12:02 pm
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.
December 9, 2008 at 12:24 pm
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
December 9, 2008 at 12:25 pm
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?
December 9, 2008 at 12:58 pm
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.
December 9, 2008 at 1:07 pm
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.
December 9, 2008 at 1:33 pm
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
December 9, 2008 at 1:36 pm
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
December 9, 2008 at 1:42 pm
Yes I do get it now. Thank you. It'll be much easier for both sides next time!
December 9, 2008 at 1:44 pm
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
December 9, 2008 at 1:49 pm
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
December 9, 2008 at 1:50 pm
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, )
December 9, 2008 at 1:59 pm
engintoksoz (12/9/2008)
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.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply