Strange behaviour on Joins

[mdr]

I am using sql server 2000. I am developing a star schema for a dwh. there are two problem tables, sales and categories. sales is made up of key fields from the other tables EG... sales.categoryid = category.id

However, i have a problem

Select *
from sales

RETURNS 20,862,231 records

whereas

select *
from sales inner join category
on sales.categoryid = categories.id

RETURNS 22,514,518 records

I have verified that there are no categoryid in the sales table that do not exist in the categories table. There are no nulls in either table.

Thanks for any help

 

[rajeevnandanmishra]

Hi mdr,

It seems that while populating sales table you may have used tables other than categories table. in that case the join on id may get reduced result.

Or, the other situation is that you might have inserted distinct values in sales table, and there are a few duplicated entries found in the categories table.

Most likely you can run the select query again (which you have used to insert data in sales table initially) and check that how many record count it is giving now.


Raj

 

[mdr]

Thanks for that Raj. Will have a look and see if that solves my problem.