Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Posts
    30

    Unanswered: Complicated Query -

    I've got a table with the following fields:
    PartySiteNum, Name, Address1, SalesRep, Company, plus other fields

    How can I get all the rows that match the following:
    Different PartySiteNum
    Different SalesRep
    Identical Name, Address1, and Company

    I only need occurances where if I group by only Name, Address1, and Company the count(*) is > 1.


    Confused? So am I....

    I'm working on building a sample table for testing. I'll post when it's done.
    Thanks

    Rob

  2. #2
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    something like this?
    The <> requirement is a bit odd - could be done without the join if you wanted either of them to be <> - just use a having max <> min for them (see below)

    select t1.Company, t1.Name, t1.Address1
    from tbl as t1
    join tbl as t2
    on t1.Company = t2.Company
    and t1.Name = t2.Name
    and t1.Address1 = t2.Address1
    and t1.PartySiteNum <> t2.PartySiteNum
    and t1.SalesRep <> t2.SalesRep
    group by t1.Company, t1.Name, t1.Address1
    having count(*) > 1

    select t1.Company, t1.Name, t1.Address1
    from tbl as t1
    group by t1.Company, t1.Name, t1.Address1
    having max(t1.PartySiteNum) <> min(t1.PartySiteNum)
    or max(t1.SalesRep) <> min(t1.SalesRep)

  3. #3
    Join Date
    Sep 2003
    Posts
    30
    Nigel - thanks for the quick reply. I'll try that now, but I just wanted to post this.

    CREATE TABLE testdata (
    PartySiteNum CHAR(50),
    Name CHAR(50),
    Address1 CHAR(50),
    SalesRep CHAR(10),
    Company CHAR(10),
    Field1 CHAR(50),
    Field2 CHAR(50)
    );

    INSERT INTO testdata values ('173896','AMBROSI &','200 W ','K00945','AMB','blah1','blah1');
    INSERT INTO testdata values ('173900','AMBROSI &','200 W ','K00945','AMB','blah2','blah2');
    INSERT INTO testdata values ('173889','AMBROSI &','200 W ','K03235','AMB','blah3','blah3');
    INSERT INTO testdata values ('173890','AMBROSI &','200 W ','K03235','AMB','blah4','blah4');
    INSERT INTO testdata values ('173895','AMBROSI &','200 W ','K03235','AMB','blah5','blah5');
    INSERT INTO testdata values ('166949','HAAS TCM','100 N ','K03191','AMB','blah6','blah6');
    INSERT INTO testdata values ('166947','HAAS TCM','11921 ','K03290','AMB','blah7','blah7');
    INSERT INTO testdata values ('166948','HAAS TCM','11921 ','K03290','AMB','blah8','blah8');
    INSERT INTO testdata values ('166950','HAAS TCM','1646 W','D02424','MCS','blah9','blah9');


    The results should be:
    173896 AMBROSI & 200 W K00945 AMB blah1 blah1
    173900 AMBROSI & 200 W K00945 AMB blah2 blah2
    173889 AMBROSI & 200 W K03235 AMB blah3 blah3
    173890 AMBROSI & 200 W K03235 AMB blah4 blah4
    173895 AMBROSI & 200 W K03235 AMB blah5 blah5
    Last edited by rbb; 08-29-07 at 13:19.

  4. #4
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    You would have to use the result as a derived to table and jointo the original table to get that result.

  5. #5
    Join Date
    Sep 2003
    Posts
    30
    Thanks nigel - your first query gave me enough to get to where I needed.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •