inner join returns cartesian product why?

• Michael Tocik

  SSCertifiable

  Points: 7099

  More actions

  March 7, 2011 at 11:22 pm

  #236236

  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?

• tommyh

  SSCertifiable

  Points: 6302

  More actions

  March 7, 2011 at 11:54 pm

  #1295703

  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

• Michael Tocik

  SSCertifiable

  Points: 7099

  More actions

  March 8, 2011 at 4:47 am

  #1295770

  dope!:blush:

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

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  March 8, 2011 at 4:59 am

  #1295775

  Michael Tocik (3/8/2011)

  ---

  dope!:blush:

  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