Post reply

Having query

  • January 16, 2010 at 3:47 am

    #140713

    Hi

    Can anyone help me with a query i have please?

    I have a table which contains telephone numbers like

    Table1

    ID Tel1 Tel2 Tel3 Tel4

    1 07790123456 07790654321 07790111111 07974222222

    2 01233838383

    3 01238383838 03838382727

    4 07790123456

    i am trying to write a query which selects all the records where a telephone number occurs more than once

    so if i queried the above table the result set would be

    ID Tel1 Tel2 Tel3 Tel4

    1 07790123456 07790654321 07790111111 07974222222

    4 07790123456

    i have tried to wrtie the query using having statement

    select Tel1, Tel2, Tel3, Tel4 from table1 group by Tel1, Tel2, Tel3, Tel4 Having count(*) > 1

    but this will only work if the two rows are identical

    Can anyone help at all

    thanks

    Si

  • January 16, 2010 at 3:55 am

    #1104575

    Untested, you may have issues with NULLs in your data

    select t1.ID, t1.Tel1, t1.Tel2, t1.Tel3, t1.Tel4

    from table1 t1

    where exists (select * from table1 t2

    where t1.ID<>t2.ID and (

    t1.Tel1 in (t2.Tel1, t2.Tel2, t2.Tel3, t2.Tel4) or

    t1.Tel2 in (t2.Tel1, t2.Tel2, t2.Tel3, t2.Tel4) or

    t1.Tel3 in (t2.Tel1, t2.Tel2, t2.Tel3, t2.Tel4) or

    t1.Tel4 in (t2.Tel1, t2.Tel2, t2.Tel3, t2.Tel4)))

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

  • January 16, 2010 at 4:31 am

    #1104577

    thanks

  • January 16, 2010 at 5:20 pm

    #1104647

    If you have a constraint on the columns saying that there has to be at least 11 characters in each, you could do:

    SELECT col1,col2,col3,col4 FROM Table1 WHERE LEN(tel1 + tel2 + tel3 + tel4) > 11

Viewing 4 posts - 1 through 3 (of 3 total)

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