Multiple Joins acting as a Inner Join

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

Multiple Joins acting as a Inner Join

Author

Topic

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-08-11 : 11:26:19

This is a SQL statement generated by a 3rd party program.

SELECT   POMAST.FORDDATE,
         POMAST.FPONO,
         POITEM.FITEMNO,
         POITEM.FUCOSTONLY,
         POITEM.FORDQTY,
         POMAST.FPOREV,
         INMAST.FPRODCL,
         INMAST.FPARTNO,
         POITEM.FRCPQTY,
         POMAST.FSTATUS,
         POITEM.FREV,
         POITEM.FORGPDATE,
         TEMPPOITEM_EXT.FCOMPANY
FROM     ((M2MDATA01.DBO.POMAST POMAST
           INNER JOIN (M2MDATA01.DBO.INMAST INMAST
                       INNER JOIN M2MDATA01.DBO.POITEM POITEM
                         ON (INMAST.FPARTNO = POITEM.FPARTNO)
                            AND (INMAST.FREV = POITEM.FREV))
             ON POMAST.FPONO = POITEM.FPONO)
          LEFT OUTER JOIN  M2MDATA01.DBO.POITEM_EXT POITEM_EXT
            ON POITEM.IDENTITY_COLUMN = POITEM_EXT.FKEY_ID)
         INNER JOIN M2MDATA01.DBO.TEMPPOITEM_EXT TEMPPOITEM_EXT
           ON POITEM_EXT.FKEY_ID = TEMPPOITEM_EXT.FKEY_ID

Why does this query return data consistant with inner joins instead of a left join here? Would an additional set of quotes around the last half of the query correct this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-11 : 11:31:44

i doubt how this worked for you. i can see an inner join without ON clause. also is this MS SQL Server?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-11 : 11:38:28

[code]SELECT POMAST.FORDDATE,
POMAST.FPONO,
POITEM.FITEMNO,
POITEM.FUCOSTONLY,
POITEM.FORDQTY,
POMAST.FPOREV,
INMAST.FPRODCL,
INMAST.FPARTNO,
POITEM.FRCPQTY,
POMAST.FSTATUS,
POITEM.FREV,
POITEM.FORGPDATE,
yak.FCOMPANY
FROM M2MDATA01.DBO.INMAST AS INMAST
INNER JOIN M2MDATA01.DBO.POITEM AS POITEM ON POITEM.FPARTNO = INMAST.FPARTNO
AND POITEM.FREV = INMAST.FREV
INNER JOIN M2MDATA01.DBO.POMAST AS POMAST ON POMAST.FPONO = POITEM.FPONO
LEFT JOIN (
SELECT POITEM_EXT.IDENTITY_COLUMN,
TEMPPOITEM_EXT.FCOMPANY
FROM M2MDATA01.DBO.POITEM_EXT AS POITEM_EXT
INNER JOIN M2MDATA01.DBO.TEMPPOITEM_EXT AS TEMPPOITEM_EXT ON TEMPPOITEM_EXT.FKEY_ID = POITEM_EXT.FKEY_ID
) AS yak ON yak.FKEY_ID = POITEM.IDENTITY_COLUMN[/code]

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

DavidChel
Constraint Violating Yak Guru

474 Posts

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

Visa, I omitted what I thought were extras in the WHERE clause as well as all the " marks. Incidentally, I am using Crystal Reports. Here is the raw SQL statement.

SELECT "pomast"."forddate", "pomast"."fpono", "poitem"."fitemno", "poitem"."fucostonly", "poitem"."fordqty", "pomast"."fporev", "inmast"."fprodcl", "inmast"."fpartno", "poitem"."frcpqty", "pomast"."fstatus", "poitem"."frev", "poitem"."forgpdate", "tempPoitem_ext"."fcompany"
 FROM   (("M2MDATA01"."dbo"."pomast" "pomast" INNER JOIN ("M2MDATA01"."dbo"."inmast" "inmast" INNER JOIN "M2MDATA01"."dbo"."poitem" "poitem" ON ("inmast"."fpartno"="poitem"."fpartno") AND ("inmast"."frev"="poitem"."frev")) ON "pomast"."fpono"="poitem"."fpono") LEFT OUTER JOIN "M2MDATA01"."dbo"."POITEM_EXT" "POITEM_EXT" ON "poitem"."identity_column"="POITEM_EXT"."FKey_ID") INNER JOIN "M2MDATA01"."dbo"."tempPoitem_ext" "tempPoitem_ext" ON "POITEM_EXT"."FKey_ID"="tempPoitem_ext"."fkey_id"
 WHERE  ("pomast"."forddate">={ts '2008-06-10 10:09:00'} AND "pomast"."forddate"<{ts '2008-08-11 10:09:31'}) AND ("pomast"."fstatus"='OPEN' OR "pomast"."fstatus"='STARTED')
 ORDER BY "inmast"."fpartno", "pomast"."fpono", "poitem"."fitemno"

Peso, so if I'm understanding correctly an additional set of () following the left outer join statement should work.

Thanks guys.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-11 : 11:54:48

quote:

Originally posted by DavidChel

Visa, I omitted what I thought were extras in the WHERE clause as well as all the " marks. Incidentally, I am using Crystal Reports. Here is the raw SQL statement.

SELECT "pomast"."forddate", "pomast"."fpono", "poitem"."fitemno", "poitem"."fucostonly", "poitem"."fordqty", "pomast"."fporev", "inmast"."fprodcl", "inmast"."fpartno", "poitem"."frcpqty", "pomast"."fstatus", "poitem"."frev", "poitem"."forgpdate", "tempPoitem_ext"."fcompany"
 FROM   (("M2MDATA01"."dbo"."pomast" "pomast" INNER JOIN ("M2MDATA01"."dbo"."inmast" "inmast" INNER JOIN "M2MDATA01"."dbo"."poitem" "poitem" ON ("inmast"."fpartno"="poitem"."fpartno") AND ("inmast"."frev"="poitem"."frev")) ON "pomast"."fpono"="poitem"."fpono") LEFT OUTER JOIN "M2MDATA01"."dbo"."POITEM_EXT" "POITEM_EXT" ON "poitem"."identity_column"="POITEM_EXT"."FKey_ID") INNER JOIN "M2MDATA01"."dbo"."tempPoitem_ext" "tempPoitem_ext" ON "POITEM_EXT"."FKey_ID"="tempPoitem_ext"."fkey_id"
 WHERE  ("pomast"."forddate">={ts '2008-06-10 10:09:00'} AND "pomast"."forddate"<{ts '2008-08-11 10:09:31'}) AND ("pomast"."fstatus"='OPEN' OR "pomast"."fstatus"='STARTED')
 ORDER BY "inmast"."fpartno", "pomast"."fpono", "poitem"."fitemno"

Peso, so if I'm understanding correctly an additional set of () following the left outer join statement should work.

Thanks guys.

ok thats fine
yup...an additional () will do the trick. you need to wrap the last inner join inside a derived table and take left join with it which is exactly what Peso has given.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources