Help! - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Help!

Author

Topic

steve_joecool
Starting Member

21 Posts

Posted - 2015-02-02 : 20:05:10

Guys,

I'm having a hard time figuring this out, I have 2 queries:

USE M2MDATA01

SELECT SOMAST.fsono
,SOITEM.fpartno
,SOITEM.fdesc
,SOITEM.fquantity
,SOITEM.fduedate
FROM SOITEM
JOIN SOMAST on SOMAST.fsono = SOITEM.fsono
WHERE SOMAST.fsono = 'S30631'

which returns 3 rows of information (the order only has 3 items)
fsono fpartno fdesc fquantity fduedate
S30631 1044858 SLEEVE FLEX VB800 WHITE 1.00000 2014-10-09 00:00:00.000
S30631 3-MISC-08973 CLAMP BAND SS 12.25"-11.5"ID 1.00000 2014-08-12 00:00:00.000
S30631 3-PUMP-01843 FILTER OIL RC-0160/165/250/RA255 3.00000 2014-08-12 00:00:00.000

The second query, which shows the shipping records for the above mentioned sales order:

SELECT SHMAST.fcsono
,SHMAST.fshipdate
,SHITEM.fpartno
,SHITEM.fshipqty
,SHMAST.fmtrckno
from SHMAST
JOIN SHITEM on SHMAST.fshipno = SHITEM.fshipno
where fcsono = 'S30631'

which returns the same 3 records:

fcsono fshipdate fpartno fshipqty fmtrckno
S30631 2014-08-12 00:00:00.000 3-MISC-08973 1.00000 1Z3195170349008112
S30631 2014-08-12 00:00:00.000 3-PUMP-01843 3.00000 1Z3195170349008112
S30631 2014-10-10 00:00:00.000 1044858 1.00000 1Z3195170350389922

Now, I want to combine these 2 queries into only one report, so I can compare the fduedate of the first query vs fshipdate on the second query...

So, when I try to join all 5 tables, I get incorrect data, here's what I'm trying:

SELECT SOMAST.fsono
,SOITEM.fpartno
,SOITEM.fdesc
,SOITEM.fquantity
,SOITEM.fduedate
,SHMAST.fshipdate
,SHITEM.fshipqty
,SHMAST.fmtrckno
FROM SHMAST
JOIN SHITEM on SHMAST.fshipno = SHITEM.fshipno
JOIN SOMAST on SOMAST.fsono = SHMAST.fcsono
JOIN SOITEM on SOITEM.fsono = SHMAST.fcsono
WHERE SHMAST.fcsono = 'S30631'

And it should only return the same 3 rows of info... but I'm getting this:

fsono fpartno fdesc fquantity fduedate fshipdate fshipqty fmtrckno
S30631 1044858 SLEEVE FLEX VB800 WHITE 1.00000 2014-10-09 00:00:00.000 2014-08-12 00:00:00.000 1.00000 1Z3195170349008112
S30631 3-MISC-08973 CLAMP BAND SS 12.25"-11.5"ID 1.00000 2014-08-12 00:00:00.000 2014-08-12 00:00:00.000 1.00000 1Z3195170349008112
S30631 3-PUMP-01843 FILTER OIL RC-0160/165/250/RA255 3.00000 2014-08-12 00:00:00.000 2014-08-12 00:00:00.000 1.00000 1Z3195170349008112
S30631 1044858 SLEEVE FLEX VB800 WHITE 1.00000 2014-10-09 00:00:00.000 2014-08-12 00:00:00.000 3.00000 1Z3195170349008112
S30631 3-MISC-08973 CLAMP BAND SS 12.25"-11.5"ID 1.00000 2014-08-12 00:00:00.000 2014-08-12 00:00:00.000 3.00000 1Z3195170349008112
S30631 3-PUMP-01843 FILTER OIL RC-0160/165/250/RA255 3.00000 2014-08-12 00:00:00.000 2014-08-12 00:00:00.000 3.00000 1Z3195170349008112
S30631 1044858 SLEEVE FLEX VB800 WHITE 1.00000 2014-10-09 00:00:00.000 2014-10-10 00:00:00.000 1.00000 1Z3195170350389922
S30631 3-MISC-08973 CLAMP BAND SS 12.25"-11.5"ID 1.00000 2014-08-12 00:00:00.000 2014-10-10 00:00:00.000 1.00000 1Z3195170350389922
S30631 3-PUMP-01843 FILTER OIL RC-0160/165/250/RA255 3.00000 2014-08-12 00:00:00.000 2014-10-10 00:00:00.000 1.00000 1Z3195170350389922

Help? maybe I'm missing a GROUP BY somewhere?

Thanks!

Ifor
Aged Yak Warrior

700 Posts

Posted - 2015-02-02 : 22:17:48

Your FROM clause should probably be:


FROM SHMAST
	JOIN SHITEM on SHMAST.fshipno = SHITEM.fshipno
	JOIN SOMAST on SOMAST.fsono = SHMAST.fcsono 
	JOIN SOITEM on SOITEM.fsono = SHITEM.fcsono
		-- Add this bit!!!
		AND SOITEM.fpartno = SHITEM.fpartno

If that does not work, you can always resort to:


WITH Query1
AS
(
	SELECT SOMAST.fsono
		,SOITEM.fpartno
		,SOITEM.fdesc
		,SOITEM.fquantity
		,SOITEM.fduedate
	FROM SOITEM
	JOIN SOMAST on SOMAST.fsono = SOITEM.fsono
	WHERE SOMAST.fsono = 'S30631'
)
,Query2
AS
(
	SELECT SHMAST.fcsono
	,SHMAST.fshipdate 
	,SHITEM.fpartno
	,SHITEM.fshipqty
	,SHMAST.fmtrckno
	from SHMAST 
	JOIN SHITEM on SHMAST.fshipno = SHITEM.fshipno
	where fcsono = 'S30631'
)
SELECT *
FROM Query1 Q1
	LEFT JOIN Query2 Q2
		ON Q1.fsono = Q2.fsono
			AND Q1.fpartno = Q2.fpartno

steve_joecool
Starting Member

21 Posts

Posted - 2015-02-03 : 10:36:10

THANK YOU!!! I didn't know about the AND statement on the JOIN... that worked, and actually, I got to join a couple of other tables as well using the same thing ;-)

Again, Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources