Werid results from Query.., HELP !!!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Werid results from Query.., HELP !!!

Author

Topic

pharoah35
Yak Posting Veteran

81 Posts

Posted - 2008-08-28 : 11:32:55

Good morning good people. Perhaps you can assist me with this weird issue. I have 2 tables [TBB] & [TBB2]

[TBB] only has a total of 17,000 records
[TBB2] only has a total of 20,000 records

Trying to get the [Shipment No.] & [Posting Date] information from the [TBB2] table, Where the dbo.TBB.[Sales Order No.] = dbo.TBB2.[Sales Order No.]

When I use the following query, I get a result of over 225,000 records which just can't be correct. Can you tell me what is wrong with this query and provide syntax corrections etc.

(Query below)

SELECT dbo.TBB.[Sales Invoice No.], dbo.TBB.[Invoice Posting Date], dbo.TBB.[Item No.], dbo.TBB.Description, dbo.TBB.Quantity,
dbo.TBB.[Unit Price], dbo.TBB.[Unit Cost], dbo.TBB.[Sales Order No.], dbo.TBB2.[Shipment No.], dbo.TBB2.[Shipment Posting Date]

FROM dbo.TBB RIGHT OUTER JOIN dbo.TBB2 ON dbo.TBB.[Sales Order No.] = dbo.vw_TBB2.[Sales Order No.]

Thanks so much in advance

E.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-28 : 11:33:45

Use an INNER JOIN, not an OUTER JOIN.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

pharoah35
Yak Posting Veteran

81 Posts

Posted - 2008-08-28 : 11:37:24

Hi Tara

Thanks for your responce,
However I tried the INNER JOIN per your suggestion and I still get a query result of over 225,000 records.

Thanks
E

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-28 : 11:39:35

You tried this?:

SELECT *
FROM dbo.TBB t1
INNER JOIN dbo.TBB2 t2
ON t1.[Sales Order No.] = t2.[Sales Order No.]

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

pharoah35
Yak Posting Veteran

81 Posts

Posted - 2008-08-28 : 11:41:49

Here is the changed query syntax, but I still get a query result of over 220,000 records
and there are obly approx 35,000 records total between the 2 tables

(changes query)
SELECT dbo.TBB.[Sales Invoice No.], dbo.TBB.[Invoice Posting Date], dbo.TBB.[Item No.], dbo.TBB.Description, dbo.TBB.Quantity,
dbo.TBB.[Unit Price], dbo.TBB.[Unit Cost], dbo.TBB.[Sales Order No.], dbo.TBB2.[Shipment No.], dbo.TBB2.[Shipment Posting Date]

FROM dbo.TBB INNER JOIN dbo.TBB2 ON dbo.TBB.[Sales Order No.] = dbo.TBB2.[Sales Order No.]

Thanks
E

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-28 : 11:43:28

Run these and post the results:

SELECT COUNT(*) FROM TBB
SELECT COUNT(*) FROM TBB2

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

pharoah35
Yak Posting Veteran

81 Posts

Posted - 2008-08-28 : 11:45:54

Hi Tara

Here are the results

SELECT COUNT(*) FROM TBB
17,224

SELECT COUNT(*) FROM TBB2
20,884

Thanks
E

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-28 : 11:47:47

SELECT COUNT(*), COUNT(DISTINCT [Sales Order No.]) FROM TBB
SELECT COUNT(*), COUNT(DISTINCT [Sales Order No.]) FROM TBB2

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-28 : 11:49:29

[code]SELECT [Sales Order No.],
COUNT(*)
FROM TBB
GROUP BY [Sales Order No.]
HAVING COUNT(*) > 1

SELECT [Sales Order No.],
COUNT(*)
FROM TBB2
GROUP BY [Sales Order No.]
HAVING COUNT(*) > 1[/code]

E 12°55'05.25"
N 56°04'39.16"

pharoah35
Yak Posting Veteran

81 Posts

Posted - 2008-08-28 : 11:52:35

Hi Tara

Here are the results

SELECT COUNT(*), COUNT(DISTINCT [Sales Order No.]) FROM TBB
17224 6456

SELECT COUNT(*), COUNT(DISTINCT [Sales Order No.]) FROM TBB2
20884 6991

Thanks
E

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-28 : 11:54:04

Oh wait... I think you have multiple records (one record for every item in an order) in table TBB!

Try this

SELECT DISTINCT	dbo.TBB2.[Shipment No.],
		dbo.TBB2.[Shipment Posting Date]
FROM		dbo.TBB
INNER JOIN	dbo.TBB2 ON dbo.TBB2.[Sales Order No.] = dbo.TBB.[Sales Order No.]

E 12°55'05.25"
N 56°04'39.16"

pharoah35
Yak Posting Veteran

81 Posts

Posted - 2008-08-28 : 12:02:38

Hi Tara

The following syntax:

SELECT DISTINCT dbo.TBB.[Sales Invoice No.],
dbo.TBB.[Invoice Posting Date],
dbo.TBB.[Item No.],
dbo.TBB.Description,
dbo.TBB.Quantity,
dbo.TBB.[Unit Price],
dbo.TBB.[Unit Cost],
dbo.TBB.[Sales Order No.],
dbo.TBB2.[Shipment No.],
dbo.TBB2.[Shipment Posting Date]
FROM dbo.TBB
INNER JOIN dbo.TBB2 ON dbo.TBB2.[Sales Order No.] = dbo.TBB.[Sales Order No.]

Generates a result of 44362 records...

This syntax

SELECT DISTINCT dbo.TBB2.[Shipment No.],
dbo.TBB2.[Shipment Posting Date]
FROM dbo.TBB
INNER JOIN dbo.TBB2 ON dbo.TBB2.[Sales Order No.] = dbo.TBB.[Sales Order No.]

Generates a result of only 9303 records

E

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-28 : 12:04:40

You are the only one to know which is correct!

SELECT		dbo.TBB2.[Shipment No.],
		MIN(dbo.TBB2.[Shipment Posting Date]),
		MAX(dbo.TBB2.[Shipment Posting Date])
FROM		dbo.TBB
INNER JOIN	dbo.TBB2 ON dbo.TBB2.[Sales Order No.] = dbo.TBB.[Sales Order No.]
GROUP BY	dbo.TBB2.[Shipment No.]
ORDER BY	dbo.TBB2.[Shipment No.]

E 12°55'05.25"
N 56°04'39.16"

pharoah35
Yak Posting Veteran

81 Posts

Posted - 2008-08-28 : 12:07:05

Hi Tara

Yes I am checking the results for acuracy now, i'll let you know my findings
Thank you again so much for your help

E

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-28 : 12:13:35

It's actually Peso/Peter helping out now.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

pharoah35
Yak Posting Veteran

81 Posts

Posted - 2008-08-28 : 12:16:31

Hi Tara, again thank you so much for your help

There is still some sort of problem. for example when running the following

SELECT DISTINCT dbo.TBB.[Sales Invoice No.],
dbo.TBB.[Invoice Posting Date],
dbo.TBB.[Item No.],
dbo.TBB.Description,
dbo.TBB.Quantity,
dbo.TBB.[Unit Price],
dbo.TBB.[Unit Cost],
dbo.TBB.[Sales Order No.],
dbo.TBB2.[Shipment No.],
dbo.TBB2.[Shipment Posting Date]
FROM dbo.TBB
INNER JOIN dbo.TBB2 ON dbo.TBB2.[Sales Order No.] = dbo.TBB.[Sales Order No.]

Which generates a result of 44362 records...

The problem is that there is a Sales Invoice for instance (example [Sales Invoice No.] SI323221) which has 38 line items.
with the query above, it only displays 3 of the line items which have the same [Item No.]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-28 : 12:21:54

As said before, you are the only one to know what you want.

SELECT		dbo.TBB.[Sales Invoice No.],
		dbo.TBB.[Invoice Posting Date],
		dbo.TBB.[Item No.],
		dbo.TBB.Description,
		dbo.TBB.Quantity,
		dbo.TBB.[Unit Price],
		dbo.TBB.[Unit Cost],
		dbo.TBB.[Sales Order No.],
		dbo.TBB2.[Shipment No.],
		dbo.TBB2.[Shipment Posting Date]
FROM		dbo.TBB
INNER JOIN	dbo.TBB2 ON dbo.TBB2.[Sales Order No.] = dbo.TBB.[Sales Order No.]
			AND dbo.TBB2.[Item No.] = dbo.TBB.[Item No.]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-28 : 12:26:33

quote:
Originally posted by tkizer

It's actually Peso/Peter helping out now.

It does day something about his concentration...

E 12°55'05.25"
N 56°04'39.16"

pharoah35
Yak Posting Veteran

81 Posts

Posted - 2008-08-28 : 12:39:16

perhaps I mis-understood and didn't make clear what my objective was.
This is what I am trying to accomplish:

I need all of the data, each line item in the following fields:

dbo.TBB.[Sales Invoice No.],
dbo.TBB.[Invoice Posting Date],
dbo.TBB.[Item No.],
dbo.TBB.Description,
dbo.TBB.Quantity,
dbo.TBB.[Unit Price],
dbo.TBB.[Unit Cost],
dbo.TBB.[Sales Order No.],

and the corresponding dbo.TBB2.[Shipment No.] & dbo.TBB2.[Shipment Posting Date] data
based on the [Sales Order No.] being the same between the 2 tables [dbo.TBB & dbo.TBB2]

Given that there is only 17,224 records in the dbo.TBB table, and 20,884 records in the dbo.TBB2 table
I didn't think I should get a query result of over 224,000 records. Thus my reason for requesting assistance

Hopefully this clears up any mis-understanding I may have cause and/or was unclear about

Thanks again
E

pharoah35
Yak Posting Veteran

81 Posts

Posted - 2008-08-28 : 13:11:17

Any idea on the correct syntax which will help me accomplish the following ?

I need all of the data, each line item in the following fields:

dbo.TBB.[Sales Invoice No.],
dbo.TBB.[Invoice Posting Date],
dbo.TBB.[Item No.],
dbo.TBB.Description,
dbo.TBB.Quantity,
dbo.TBB.[Unit Price],
dbo.TBB.[Unit Cost],
dbo.TBB.[Sales Order No.],

and the corresponding dbo.TBB2.[Shipment No.] & dbo.TBB2.[Shipment Posting Date] data
based on the [Sales Order No.] being the same between the 2 tables [dbo.TBB & dbo.TBB2]

Given that there is only 17,224 records in the dbo.TBB table, and 20,884 records in the dbo.TBB2 table
I didn't think I should get a query result of over 224,000 records. Thus my reason for requesting assistance

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-28 : 13:12:50

You'll need to read through Peso's posts again as you seem to have missed his point.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources