how to stop duplicate values? - 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)

how to stop duplicate values?

Author

Topic

maifs
Yak Posting Veteran

57 Posts

Posted - 2010-05-04 : 01:52:51

my select query return some duplicate data in some colums.
how can i stop values to duplicate.
i tried it by using distinct but not meeeting my requirements because of using join.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-04 : 01:58:56

You'll need to post a data example so that we can come up with a solution.

The solution could involve ROW_NUMBER(), maybe GROUP BY, or maybe something else. We can't provide a solution without a data example.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

maifs
Yak Posting Veteran

57 Posts

Posted - 2010-05-04 : 02:03:51

I AM USING THIS QUERY.

select distinct ISNULL(c.constraint_name,'') Constraint_name,

ISNULL(cfk.table_name ,'') FK_Table_name,

ISNULL(kcu.column_name ,'') FK_Column_name,

ISNULL(cpk.table_name ,'') PK_Table_name,

ISNULL(pkt.column_name ,'') PK_COlumn_name

from information_schema.REFERENTIAL_CONSTRAINTS c

INNER JOIN information_schema.TABLE_CONSTRAINTS cpk on c.unique_constraint_name = cpk.constraint_name

INNER JOIN information_schema.TABLE_CONSTRAINTS cfk on c.constraint_name = cfk.constraint_name

INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu ON c.constraint_name = kcu.constraint_name

INNER JOIN
(
SELECT DISTINCT tci.table_name, kcui.column_name FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tci
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcui on tci.constraint_name = kcui.constraint_name
WHERE tci.CONSTRAINT_TYPE = 'PRIMARY KEY'

) PKT ON PKT.TABLE_NAME = CPK.TABLE_NAME
WHERE cfk.table_name = 'FOREIGN KEY TABLE NAME '
ORDER BY 1,2,3,4

--------------------------------------------------------------------------------

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-04 : 02:09:48

You need to show us a data example of what it is currently returning and then what you'd like it to return.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

maifs
Yak Posting Veteran

57 Posts

Posted - 2010-05-04 : 02:24:09

this query returns this data:

R_1003 BP_BANK account_type_cde ACCOUNT_TYPE_CODE account_type_cde
R_1004 BP_BANK BP_primary_id BP_RELATIONSHIP relationship_cde
R_1004 BP_BANK BP_primary_id BP_RELATIONSHIP BP_secondary_id
R_1004 BP_BANK BP_primary_id BP_RELATIONSHIP BP_primary_id
R_1004 BP_BANK BP_secondary_id BP_RELATIONSHIP relationship_cde
R_1004 BP_BANK BP_secondary_id BP_RELATIONSHIP BP_secondary_id
R_1004 BP_BANK BP_secondary_id BP_RELATIONSHIP BP_primary_id
R_1004 BP_BANK relationship_cde BP_RELATIONSHIP relationship_cde
R_1004 BP_BANK relationship_cde BP_RELATIONSHIP BP_secondary_id
R_1004 BP_BANK relationship_cde BP_RELATIONSHIP BP_primary_id
R_1425 BP_BANK business_partner_id BP_MAIN business_partner_id

these record are duplicates:

R_1004 BP_BANK BP_primary_id BP_RELATIONSHIP BP_secondary_id
R_1004 BP_BANK BP_primary_id BP_RELATIONSHIP BP_primary_id

R_1004 BP_BANK BP_secondary_id BP_RELATIONSHIP relationship_cde

R_1004 BP_BANK BP_secondary_id BP_RELATIONSHIP BP_primary_id

R_1004 BP_BANK relationship_cde BP_RELATIONSHIP BP_secondary_id
R_1004 BP_BANK relationship_cde BP_RELATIONSHIP BP_primary_id

i dont want these.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-04 : 02:31:31

You can use GROUP BY with an aggregate function such as MAX, or you can use ROW_NUMBER function. I'm pressed for time at the moment, so hopefully someone will assist you with the exact query.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

maifs
Yak Posting Veteran

57 Posts

Posted - 2010-05-04 : 02:39:43

can you give any example from these? from group by or max or row_number?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources