Query Help Please

  • 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

  • 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
  • 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.

  • 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]

  • 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 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?

  • 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]

  • 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.

  • 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

  • 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
  • Yes I do get it now. Thank you. It'll be much easier for both sides next time!

  • 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
  • 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

  • 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, )

  • 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.

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

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