t-sql 2008 avoid a cartesian product

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » t-sql 2008 avoid a cartesian product

t-sql 2008 avoid a cartesian product

Rate Topic

Display Mode

Topic Options

Author

Message

wendy elizabeth

Posted Monday, December 24, 2012 2:55 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, February 13, 2013 7:58 AM
Points: 137, Visits: 159

For a customer, I need to load data from excel 2010 spreadsheets into a sql server 2008 r2 database for a one time set of adhoc queries. I will then run queries to obtain to look at the data based upon the user requirements.

The problem with the data is there is alot of duplicate data within different rows within the same table. When I join the various tables together, I get a cartesian product.
I am trying to determine how to run the queries I need to run without getting the cartesian product.

There are 3 tables which are: 1. a claims table by client number that has the duplicate data wtihin some of the rows. 2. a price table and 3. an authorizagtion table.

I am thinking of loading the data into temp tables in a way that the data will not be duplicated. If this is a possbility, can you tell me how to accomplish this goal?

If you have any other suggestions, can you show me code on how to accomplish this goal?

Post #1399993

Lynn Pettis

Posted Monday, December 24, 2012 3:33 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 10:35 PM
Points: 21,588, Visits: 27,383

Without being able to see what you have to deal with, or at least a reasonable facsimile of the data, it is hard to provide you much guidance. This is a case where seeing will really help.

Cool

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1399996

wendy elizabeth

Posted Tuesday, December 25, 2012 11:36 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, February 13, 2013 7:58 AM
Points: 137, Visits: 159

What would you want to see? The columns in the tables and/or the data?

Post #1400091

eklavu

Posted Tuesday, December 25, 2012 6:29 PM

SSC Rookie

Group: General Forum Members
Last Login: Monday, May 06, 2013 12:28 AM
Points: 47, Visits: 141

Hope this code will work and if it satisfied with the ff requirements.

1. You want to select distinct resultset.
2. must have primary keys and foreign keys to join the three tables

WITH CTE_DISTINCT(CLIENT_NUMBER)
AS
(SELECT DISTINCT CLIENT_NUMBER FROM CLIENT_TABLE)
SELECT * FROM CTE_DISTINCT A
INNER JOIN PRICE_TABLE B
ON A.CLIENT_NUMBER = B.CLIENT_NUMBER
INNER JOIN AUTHORIZATION_TABLE C
ON B.CLIENT_NUMBER = C.CLIENT_NUMBER
--WHERE <YOUR CONDITION HERE>

Post #1400113

eklavu

Posted Tuesday, December 25, 2012 6:31 PM

SSC Rookie

Group: General Forum Members
Last Login: Monday, May 06, 2013 12:28 AM
Points: 47, Visits: 141

If the problem is avoiding duplicate data insertion in your table, it will be a diffrent story. You need to check first the data if exists in client table before inserting the data. It the data exists already, dont insert, move to another record.

Post #1400114

vinu512

Posted Wednesday, December 26, 2012 12:14 AM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 4:11 AM
Points: 1,042, Visits: 1,438

There are 3 tables which are: 1. a claims table by client number that has the duplicate data wtihin some of the rows. 2. a price table and 3. an authorizagtion table.

I am thinking of loading the data into temp tables in a way that the data will not be duplicated. If this is a possbility, can you tell me how to accomplish this goal?

Why do you want to eliminate duplicates from the claims table??.....1 client can have multiple claims....right??.....the price and the authorization table just seem like master tables.

The price and the authorization tables will have unique values. But, the claims table may have duplicates. Isn,t that how it is supposed to be??....Multiple claims for a client???

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden Wink

Post #1400160

Greg Snidow

Posted Wednesday, December 26, 2012 12:47 PM

SSCommitted

Group: General Forum Members
Last Login: Yesterday @ 11:25 AM
Points: 1,561, Visits: 2,309

wendy elizabeth (12/24/2012)

I need to load data from excel 2010 spreadsheets...I am thinking of loading the data into temp tables in a way that the data will not be duplicated.

Wendy, if you truly want to eliminate the dupes, and I'm not suggesting this is what you *should* do, as was previously mentioned, why don't you just use Excel's de-dupe function? Click the "Data" tab on the ribbon, and look for "Remove Duplicates". Also, you said you are getting a cartesian product, so maybe you omitted some join criteria?

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

Post #1400337

« Prev Topic | Next Topic »

Permissions