Complex Joins 1

[dakone]

I've been called upon by the powers that be to fix this SQL query. Thus far no info shows up although it exists. The SQL query is odd looking because it was generated through Crystal Reports. I am at a bit of a loss with this one, any ideas?

SELECT
SOP10100."SOPNUMBE", SOP10100."DOCDATE", SOP10100."ReqShipDate", SOP10100."PYMTRMID", SOP10100."LOCNCODE", SOP10100."CUSTNMBR", SOP10100."CUSTNAME", SOP10100."CSTPONBR", SOP10100."MSTRNUMB", SOP10100."ADDRESS1", SOP10100."CITY", SOP10100."STATE", SOP10100."ZIPCODE", SOP10100."PHNUMBR1", SOP10100."SHIPMTHD", SOP10100."TRDISAMT", SOP10100."SUBTOTAL", SOP10100."FRTAMNT", SOP10100."MISCAMNT", SOP10100."TAXAMNT", SOP10100."DOCAMNT", SOP10100."DEPRECVD", SOP10100."SLPRSNID",
SOP10200."ITEMNMBR", SOP10200."ITEMDESC", SOP10200."UNITPRCE", SOP10200."XTNDPRCE", SOP10200."DISCSALE", SOP10200."QUANTITY", SOP10200."QTYTBAOR", SOP10200."QTYFULFI",
RM00101."ADDRESS2",
IV40700."ADDRESS1", IV40700."CITY", IV40700."STATE", IV40700."ZIPCODE",
RM00102."ADDRESS1", RM00102."ADDRESS2", RM00102."CITY", RM00102."STATE", RM00102."ZIP", RM00102."PHONE1",
SOP10202."CMMTTEXT"
FROM
"MTNB"."SOP10100" SOP10100 INNER JOIN "MTNB"."RM00101" RM00101 ON
SOP10100."CUSTNMBR" = RM00101."CUSTNMBR" INNER JOIN "MTNB"."IV40700" IV40700 ON
SOP10100."LOCNCODE" = IV40700."LOCNCODE" INNER JOIN "MTNB"."SOP10200" SOP10200 ON
SOP10100."SOPTYPE" = SOP10200."SOPTYPE" AND
SOP10100."SOPNUMBE" = SOP10200."SOPNUMBE" INNER JOIN "MTNB"."SOP10202" SOP10202 ON
SOP10200."SOPNUMBE" = SOP10202."SOPNUMBE" AND
SOP10200."LNITMSEQ" = SOP10202."LNITMSEQ" INNER JOIN "MTNB"."RM00102" RM00102 ON
RM00101."CUSTNMBR" = RM00102."CUSTNMBR" AND
RM00101."PRSTADCD" = RM00102."ADRSCODE"
WHERE
SOP10100."SOPNUMBE" = 'MCTNINV2000000'
ORDER BY
SOP10100."SOPNUMBE" ASC

Brutal to look at isn't it? Any and all help is greatly appreciated.

-D

 

[ClaireHCM]

I am not sure whether it works or not , but give a try


SELECT
a.SOPNUMBE, a.DOCDATE, a.ReqShipDate, a.PYMTRMID, a.LOCNCODE, a.CUSTNMBR, a.CUSTNAME, a.CSTPONBR, a.MSTRNUMB, a.ADDRESS1, a.CITY, a.STATE, a.ZIPCODE, a.PHNUMBR1, a.SHIPMTHD, a.TRDISAMT, a.SUBTOTAL, a.FRTAMNT, a.MISCAMNT, a.TAXAMNT, a.DOCAMNT, a.DEPRECVD, a.SLPRSNID,
d.ITEMNMBR, d.ITEMDESC, d.UNITPRCE, d.XTNDPRCE, d.DISCSALE, d.QUANTITY, d.QTYTBAOR, d.QTYFULFI,
b.ADDRESS2,
c.ADDRESS1, c.CITY, c.STATE, c.ZIPCODE,
f.ADDRESS1, f.ADDRESS2, f.CITY, f.STATE, f.ZIP, f.PHONE1,
e.CMMTTEXT
FROM
SOP10100 a, RM00101 b ,IV40700 c ,SOP10200 d ,SOP10202 e ,RM00102 f
where a.CUSTNMBR = b.CUSTNMBR and a.LOCNCODE = c.LOCNCODE and a.SOPTYPE = d.SOPTYPE
AND a.SOPNUMBE = d.SOPNUMBE and d.SOPNUMBE = e.SOPNUMBE AND d.LNITMSEQ = e.LNITMSEQ
and b.CUSTNMBR = f.CUSTNMBR AND b.PRSTADCD = f.ADRSCODE
and a.SOPNUMBE = 'MCTNINV2000000'
ORDER BY
a.SOPNUMBE ASC

 

[dakone]

I've narrowed it down to:

SELECT SOP10202."CMMTTEXT"

FROM INNER JOIN "MTNB"."SOP10202" SOP10202 ON SOP10200."SOPNUMBE" = SOP10202."SOPNUMBE" AND
SOP10200."LNITMSEQ" = SOP10202."LNITMSEQ"

Can anyone see a problem with this statement or suggest another way to do it?

 

[ClaireHCM]

Forgot to add "use mtbn" in the beginning,BTw is mtbn is a database name?

 

[ClaireHCM]

For your second post , it can query like this

SELECT b.CMMTTEXT FROM SOP10200 a
INNER JOIN SOP10202 b ON a.SOPNUMBE = b.SOPNUMBE AND a.LNITMSEQ = b.LNITMSEQ

 

[dakone]

"MTNB" is my ODBC connection.

 

[zhavic]

Just try to use LEFT JOIN instead of INNER JOIN.
If you have some records in your 'SOP10100' table
( FROM "MTNB"."SOP10100" ),
you must get at least this rows in the result if you use LEFT JOIN-s


Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[dakone]

Thanks to both of you for your time and help, but it's Zhavic who gets the cigar on this one!