Duplicate records control - 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 2008 Forums

Transact-SQL (2008)

Duplicate records control

Author

Topic

learntsql

524 Posts

Posted - 2010-10-22 : 03:08:02

Hi All,

I have 3 Category master tables and one child table.

Each Master table contains different categories information.
All these categories ids are repeated in Child table (for 10 or 20 or more).
When i am joining these tables IDs are getting repeat the number of times in the child table.
Plz tell me how to write a query to get single catgoryID.
TIA.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-22 : 03:17:27

Try distinct clause in your select query.

Ex: Select distinct <columnName> from yourtable

If your requirement is something different then please provide sample data and expected output.

learntsql

524 Posts

Posted - 2010-10-22 : 03:34:12

SORRY,
HERE IS THE SAMPLE DATA...

Cat1Table

CatID Name
C11 C11
C12 C12
C13 C13
...
...

Cat2 table
-----------
CatID Name
C21 C21
C22 C22
C23 C23
...
...
...

Cat3 table
-----------
CatID Name
C31 C31
C32 C32
C33 C33
...
...
...

cHILD TABLE
ID-CATID-VALUE-AREA
1-C11-100-A1
2-C11-20-A2
3-C11-10-A3
4-C11-300-A4
5-C21-22-A1
6-C21-40-A2
...
...
...

SELECTION IS AREAS(1 OR 2 OR ALL)
OUTPUT
------
CATID-TOTALVALUE
C11-XXXX
C21-XXX
C22-XX
...
...

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-10-25 : 18:03:21

what would you do if you had to create 99 more categories. are you going to create 99 more tables? you have serious flaw in your design that will haunt you later on.
create a categories table. combine all categories and assign them their respective CategoryID. Then your query will be piece of cake

OR


SELECT CATID, SUM(VALUE)
FROM CHILDTBALE
GROUP BY CATID

If you don't have the passion to help people, you have no passion

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-10-25 : 18:55:30

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Please learn to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html

>> I have 3 Category master tables and one child table. <<

Master and child are terms from Network databases. RDBMS has referenced and referencing tables.

>> Each Master table contains different categories information. All these categories ids are repeated in Child table (for 10 or 20 or more). <<

This sounds like redundant data and a non-normalized schema. But there is no such thing as a "category_id" -- an attribute can be a "<something>_category" or a "<category>_id" but not both at once, by definition. Would you also have "category_value_type_id" or worse? See how silly that is?

>> When I am joining these tables IDs are getting repeat the number of times in the child table. <<

This makes no sense. Each auxiliary table would be named "<something>_Categories" because a category is what ISO-11179 calls an attribute property. The key column will be the encoding and there will be a description of the encoding. For example a book store uses what are called "Shop_Categories" and will have rows like ('FIC', 'Fiction'), ('MYS', 'Mysteries'), ('YA', 'Young Adults'), etc.

They have nothing in common for a join. How would you join a hat_size to a color to a shoe_size?

Post the DDL and let's see what you are doing. Maybe we can fix it.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

learntsql

524 Posts

Posted - 2010-10-26 : 00:14:27

Thanks guys....
nice and usefull sugestions...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources