Quering multiple tables returning too many records 1

[iconSYS1]

Hello,

I need to run a query on my SQL Database with multiple tables.. the query i am running is :

select invchea.TRACKING_NUMBER, invcdtl.price, invcdtl.qty_invoiced, invcdtl.U_FULLPART, invcdtl.detail_number, invcdtl.customer_po, invcdtl.SHIPPED_DATE
from dbo.invchea, dbo.invcdtl
where SHIPPED_DATE between '2011-11-01 00:00:00:000' and '2011-11-11 00:00:00:000'

The query SHOULD return about 50 records, instead it is returning millions of records.

When I run the query on only the INVCHEA table, I get the 50 records that I'm looking for, but I need the info from the INVCDTL table as well to make a useful spreadsheet. Can someone tell me what I am doing wrong here?

Thanks.

 

[gmmastros]

This part:

from dbo.invchea, dbo.invcdtl

You need to know how the 2 tables are related to each other. Usually, there is a column of data in both tables that represent the same thing.

For example, you should change that line to something like this:

from dbo.invchea
     Inner Join dbo.invcdtl
       On dbo.invchea[!].SomeColumn[/!] = dbo.invcdtl[!].SomeColumn[/!]

Obviously, I don't know your table layouts so I cannot tell you what column in each table to use for the join condition, but there is likely something. Could be tracking_number, PO_Number, or anything, really.

When you do not include the join condition, you get something called a "cartesian product". This essentially returns every combination of data possible. Since you have a where clause on shipped date, this is somewhat limiting the amount of data, but you should still get 50 records from invcdtl multiplied by the number of rows in invchea.

Does this make sense?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[iconSYS1]

I've been reading about joins all day and I had no idea where the authors of the tutorials were getting the columns in the ON clause, you seriously made that easy to understand. I think....

So I found a column both tables have in common and changed the query to :

select invchea.TRACKING_NUMBER, invcdtl.price, invcdtl.qty_invoiced, invcdtl.U_FULLPART, invcdtl.detail_number, invcdtl.customer_po, invcdtl.SHIPPED_DATE
from dbo.invchea
Inner Join dbo.invcdtl
On dbo.invchea.invoice_number = dbo.invcdtl.invoice_number
where SHIPPED_DATE between '2011-11-10 00:00:00:000' and '2011-11-11 00:00:00:000'

which returned 63 results, that seems about right. Is that the correct way?

 

[gmmastros]

Yes. It looks correct to me.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom