Having Statement Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Having Statement Query

Author

Topic

cable_si
Starting Member

20 Posts

Posted - 2010-01-16 : 05:44:04

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

Kristen
Test

22859 Posts

Posted - 2010-01-16 : 06:00:16

This perhaps?


SELECT ID, Tel
FROM
(
    SELECT ID, Tel1 AS Tel
    FROM table1
    WHERE Tel1 IS NOT NULL
    UNION ALL
    SELECT ID, Tel2 AS Tel
    FROM table1
    WHERE Tel2 IS NOT NULL
    UNION ALL
    SELECT ID, Tel3 AS Tel
    FROM table1
    WHERE Tel3 IS NOT NULL
    UNION ALL
    SELECT ID, Tel4 AS Tel
    FROM table1
    WHERE Tel4 IS NOT NULL
) AS X
GROUP BY ID, Tel
HAVING COUNT(*) > 1

cable_si
Starting Member

20 Posts

Posted - 2010-01-16 : 06:31:32

thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources