Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » inner join returns cartesian product why?
Add to briefcase

inner join returns cartesian product why? Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Michael Tocik
Posted Monday, March 07, 2011 11:22 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, April 29, 2013 2:43 PM
Points: 2,533, Visits: 159
Hi,
I have two order tables that I am trying to join. Both have have 13 records in them but when I do an inner join on the order number I get 169 records. Both tables have a data type of varchar(7) Here is the join;

SELECT INVOICE_DETAIL_CONF_ALL.order_number
FROM INVOICE_DETAIL_CONF_ALL
inner join ORDER_EXTRACT_AUTO
on [Ord No] = ORDER_NUMBER


What am I missing?
Post #1074608
tommyh
Posted Monday, March 07, 2011 11:54 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Friday, May 10, 2013 3:15 AM
Points: 1,476, Visits: 1,943
Michael Tocik (3/7/2011)
Hi,
I have two order tables that I am trying to join. Both have have 13 records in them but when I do an inner join on the order number I get 169 records. Both tables have a data type of varchar(7) Here is the join;

SELECT INVOICE_DETAIL_CONF_ALL.order_number
FROM INVOICE_DETAIL_CONF_ALL
inner join ORDER_EXTRACT_AUTO
on [Ord No] = ORDER_NUMBER


What am I missing?


Apart from some DDL code to see what the tables look like?

So just guessing wild:
- All the rows in both tables actually have the same Order_number

- You joining on 2 columns from the same table. Try and add the table name before the column.

/T
Post #1074610
Michael Tocik
Posted Tuesday, March 08, 2011 4:47 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, April 29, 2013 2:43 PM
Points: 2,533, Visits: 159
dope!

My mistake....I was joining on the wrong field
Post #1074690
ChrisM@Work
Posted Tuesday, March 08, 2011 4:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:51 AM
Points: 5,608, Visits: 10,970
Michael Tocik (3/8/2011)
dope!

My mistake....I was joining on the wrong field


Easily done. Try to get into the habit of using table aliases:

SELECT i.order_number
FROM INVOICE_DETAIL_CONF_ALL i
INNER JOIN ORDER_EXTRACT_AUTO o
ON o.[Ord No] = i.ORDER_NUMBER

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1074696
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.