Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

t-sql 2008 avoid a cartesian product Expand / Collapse
Author
Message
Posted Monday, December 24, 2012 2:55 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, August 16, 2014 10:01 AM
Points: 365, Visits: 426
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
Posted Monday, December 24, 2012 3:33 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 8:19 PM
Points: 23,286, Visits: 32,012
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.



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
Posted Tuesday, December 25, 2012 11:36 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, August 16, 2014 10:01 AM
Points: 365, Visits: 426
What would you want to see? The columns in the tables and/or the data?
Post #1400091
Posted Tuesday, December 25, 2012 6:29 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 16, 2014 5:32 AM
Points: 55, Visits: 158
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
Posted Tuesday, December 25, 2012 6:31 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 16, 2014 5:32 AM
Points: 55, Visits: 158
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
Posted Wednesday, December 26, 2012 12:14 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, August 24, 2014 5:44 AM
Points: 1,125, Visits: 1,589

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
Post #1400160
Posted Wednesday, December 26, 2012 12:47 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 1:06 PM
Points: 1,566, Visits: 2,392
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 »

Add to briefcase

Permissions Expand / Collapse