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

inner join returns cartesian product why? Expand / Collapse
Author
Message
Posted Monday, March 7, 2011 11:22 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 12:52 PM
Points: 3,090, Visits: 168
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
Posted Monday, March 7, 2011 11:54 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Today @ 3:18 AM
Points: 1,484, Visits: 1,964
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
Posted Tuesday, March 8, 2011 4:47 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 12:52 PM
Points: 3,090, Visits: 168
dope!

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


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:43 AM
Points: 6,890, Visits: 14,254
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