Query Syntax

I am looking to find records that share the same Policy_Desc but have
different Policy_ids. I know there are records that fit this criteria but fo
r
some reason this query returns zero rows.
SELECT Policy_Desc, Policy_id
FROM Policy_Table AS Outside
WHERE EXISTS
(
SELECT 1 FROM Policy_Table AS Inside
WHERE Inside.Policy_Desc = Outside.Policy_Desc
AND Inside.Policy_id <> Outside.Policy_id
)Maybe this will do it
SELECT t1.Policy_Desc, t1.Policy_id
FROM Policy_Table as t1 INNER JOIN Policy_Table as t2
ON t1.Policy_Desc = t2.Policy_Desc AND t1.Policy_ID=t2.Policy_id <>
t1.Policy_id
Adi|||A few things:
Check if the database collation is case sensitive. If so, then convert the
desc to upper case in both sides and check.
If there are leading blank spaces, you may want to use LTRIM.
If none of the above works, then we can break our heads.|||Also post ddl and sample data.|||Can Policy_Desc be NULL? If so try this
SELECT Policy_Desc, Policy_id
FROM Policy_Table AS Outside
WHERE EXISTS
(
SELECT 1 FROM Policy_Table AS Inside
WHERE (Inside.Policy_Desc = Outside.Policy_Desc
OR (Inside.Policy_Desc is null and Outside.Policy_Desc is
null))
AND Inside.Policy_id <> Outside.Policy_id
)
ORDER BY Policy_Desc, Policy_id|||And try if you are getting any result for this.
SELECT Policy_Desc, count(*)
FROM Policy_Table
group by policy_desc
having count(*) > 1|||> I know there are records that fit this criteria but for
> some reason this query returns zero rows
There is nothing wrong with your query, barring the NULLs and case
differences mentioned by the others. I would expect the query below to
return data if you actually have duplicates.
SELECT Policy_Desc, COUNT(*)
FROM Policy_Table
GROUP BY Policy_Desc
HAVING COUNT(*) > 1
Hope this helps.
Dan Guzman
SQL Server MVP
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:B0A11403-3790-44FD-9C7F-976F72846942@.microsoft.com...
>I am looking to find records that share the same Policy_Desc but have
> different Policy_ids. I know there are records that fit this criteria but
> for
> some reason this query returns zero rows.
> SELECT Policy_Desc, Policy_id
> FROM Policy_Table AS Outside
> WHERE EXISTS
> (
> SELECT 1 FROM Policy_Table AS Inside
> WHERE Inside.Policy_Desc = Outside.Policy_Desc
> AND Inside.Policy_id <> Outside.Policy_id
> )