SQLTeam.com | Weblogs | Forums

Joining without a Cartesian product

I have two tables that I need to join that share several fields.

The OrderID is always the same on both tables, so when I join them, I do so on the OrderID field. The SKU is also the same, so I add a WHERE clause that the SKU of Table A must match the SKU of Table B

Table A

Table B

The problem is that the OrderItemID is unique, and the ones from one table do not match the ones from the other table. So when I run my query, I get 4 rows.

Is there a way to make it such that the rows are distinct on OrderItemID rather than producing the Cartesian product? Even though I won't explicitly be able to map one to the other?

In your example there are 2 rows in each table. Is there always the same amount of rows in each table when joining in orderid and sku?