Cross Join with always the same string message

Question

I am using Microsoft SQL Server 2005.

I would like to have a table in output with a column regarding duplicate values in cross join with a table containing only a column with the same string message. I think the cross join with a "string message" should be the proper way to do it.

Why the following script is not working?

DECLARE @IN_CodesTable TABLE
   (
        CodesValues           NVARCHAR(60)
   )
INSERT INTO @IN_CodesTable VALUES('CODE_BB^&')
INSERT INTO @IN_CodesTable VALUES('CODE_1234')
INSERT INTO @IN_CodesTable VALUES('CODE_BB^&')
INSERT INTO @IN_CodesTable VALUES('CODE_$%^&')
INSERT INTO @IN_CodesTable VALUES('CODE_BB^&')
INSERT INTO @IN_CodesTable VALUES('CODE_BB^&')
INSERT INTO @IN_CodesTable VALUES('CODE_1234')

SELECT *
FROM   
(
SELECT DISTINCT CodesTable
FROM @IN_CodesTable
WHERE CodesTable IN
                    ( SELECT  CodesValues
                    FROM     @IN_CodesTable
                    GROUP BY CodesTable
                    HAVING   COUNT(*) > 1
                    )
)
CROSS JOIN
( 
    SELECT 'You have duplicates!' AS DupMessage
)

score 2 · Accepted Answer · answered Aug 24 '12 at 13:58

In a couple of places, you are using CodesTable instead of CodesValues. Also, although a CROSS JOIN works, it's a bit more clear to simply select the DupMessage as an additional value, something like:

SELECT *, 'You have duplicates!' AS DupMessage
FROM (
    SELECT DISTINCT CodesValues
    FROM IN_CodesTable
    WHERE CodesValues IN (
        SELECT  CodesValues
        FROM IN_CodesTable
        GROUP BY CodesValues
        HAVING COUNT(*) > 1
    )
 ) X

SqlFiddle here

score 1 · Answer 2 · answered Aug 24 '12 at 14:06

DECLARE @IN_CodesTable TABLE ( CodesValues NVARCHAR(60) ) 
INSERT INTO @IN_CodesTable VALUES
  ('CODE_BB^&'), ('CODE_1234'), ('CODE_BB^&'), ('CODE_$%^&'),
  ('CODE_BB^&'), ('CODE_BB^&'), ('CODE_1234') 

-- Display only values with duplicate rows.
select distinct CodesValues
  from @IN_CodesTable as CT
  where ( select count(42) from @IN_CodesTable where CodesValues = CT.CodesValues ) > 1

select distinct CodesValues
  from @IN_CodesTable as CT
  group by CodesValues
  having count(42) > 1

-- Display all values with duplicates indicated.
select distinct CodesValues,
  case when ( select count(42) from @IN_CodesTable where CodesValues = CT.CodesValues ) > 1 then 'Duplicates' else '' end as Flag
  from @IN_CodesTable as CT

select distinct CodesValues,
  case when count(42) > 1 then 'Duplicates' else '' end as Flag
  from @IN_CodesTable as CT
  group by CodesValues

Cross Join with always the same string message

2 Answers2