Join returns dual results

[JSHoltsIT]

I have a join (I took the language from a crystal report "show query" window) that returns double records. The views joined

v_Combinetable1
v_Combinetable3
v_Combinetable2

are each unions of two identical tables in differing databases.

Can anyone shed light onto the below query returning duplicate (redundant) identical record sets?

Jason

SELECT
table2.dbname,table3.SOPTYPE, table3.GLPOSTDT, table3.VOIDSTTS,
table2.SOPTYPE, table2.SOPNUMBE, table2.ITEMNMBR, table2.ITEMDESC, table2.XTNDPRCE, table2.EXTDCOST,
table2.QUANTITY, table1.ITEMNMBR, table1.USCATVLS_5
FROM
{ oj (v_Combinetable3 table3 LEFT JOIN v_Combinetable2 table2 ON
table3.SOPNUMBE = table2.SOPNUMBE)
LEFT JOIN v_Combinetable1 table1 ON
table2.ITEMNMBR = table1.ITEMNMBR}
WHERE
table3.SOPTYPE >= 3 AND
table3.SOPTYPE <= 4 AND
table3.VOIDSTTS = 0 AND
table1.ITEMNMBR = 'CSS898P' AND
table1.USCATVLS_5 = 'D13Class' AND
table3.GLPOSTDT >= '01/11/2008' AND
table3.GLPOSTDT < '02/12/2008'
ORDER BY
table2.sopnumbe, table2.itemnmbr

 

[gmmastros]

I don't know if this will solve your problem, but I do notice that you are using a LEFT JOIN with the 'left joined' table in your where clause. As such, I suggest you try this....

SELECT
    table2.dbname,table3.SOPTYPE, table3.GLPOSTDT, table3.VOIDSTTS,
    table2.SOPTYPE, table2.SOPNUMBE, table2.ITEMNMBR, table2.ITEMDESC, table2.XTNDPRCE, table2.EXTDCOST, 
    table2.QUANTITY, table1.ITEMNMBR, table1.USCATVLS_5
FROM
    { oj (v_Combinetable3 table3 LEFT JOIN v_Combinetable2 table2 ON
        table3.SOPNUMBE = table2.SOPNUMBE
        And table3.SOPTYPE >= 3 AND
        And table3.SOPTYPE <= 4 AND
        And table3.VOIDSTTS = 0 AND
        And table3.GLPOSTDT >= '01/11/2008' AND
        And table3.GLPOSTDT < '02/12/2008')
     LEFT JOIN v_Combinetable1 table1 ON
        table2.ITEMNMBR = table1.ITEMNMBR
        And table1.ITEMNMBR = 'CSS898P' AND
        And table1.USCATVLS_5 = 'D13Class' AND
     }
ORDER BY
    table2.sopnumbe, table2.itemnmbr

This may not solve your problem. However, if it works for you, and you'd like me to explain, let me know.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

OOppppsss....

SELECT
    table2.dbname,table3.SOPTYPE, table3.GLPOSTDT, table3.VOIDSTTS,
    table2.SOPTYPE, table2.SOPNUMBE, table2.ITEMNMBR, table2.ITEMDESC, table2.XTNDPRCE, table2.EXTDCOST, 
    table2.QUANTITY, table1.ITEMNMBR, table1.USCATVLS_5
FROM
    { oj (v_Combinetable3 table3 LEFT JOIN v_Combinetable2 table2 ON
        table3.SOPNUMBE = table2.SOPNUMBE AND
        table3.SOPTYPE >= 3 AND
        table3.SOPTYPE <= 4 AND
        table3.VOIDSTTS = 0 AND
        table3.GLPOSTDT >= '01/11/2008' AND
        table3.GLPOSTDT < '02/12/2008')
     LEFT JOIN v_Combinetable1 table1 ON
        table2.ITEMNMBR = table1.ITEMNMBR AND
        table1.ITEMNMBR = 'CSS898P' AND
        table1.USCATVLS_5 = 'D13Class' AND
     }
ORDER BY
    table2.sopnumbe, table2.itemnmbr

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[JSHoltsIT]

The original query runs in a couple of seconds, the revised query goes well over 1 min (I stopped the query at 70 seconds).

Jason

 

[JSHoltsIT]

I ran the revised query again (81 seconds), all fields return NULL except

SOPTYPE
VOIDSTTS
GLPOSTDT

Jason

 

[gmmastros]

SELECT
    table2.dbname,table3.SOPTYPE, table3.GLPOSTDT, table3.VOIDSTTS,
    table2.SOPTYPE, table2.SOPNUMBE, table2.ITEMNMBR, table2.ITEMDESC, table2.XTNDPRCE, table2.EXTDCOST, 
    table2.QUANTITY, table1.ITEMNMBR, table1.USCATVLS_5
FROM
    { oj (v_Combinetable3 table3 INNER JOIN v_Combinetable2 table2 ON
        table3.SOPNUMBE = table2.SOPNUMBE AND
        table2.ITEMNMBR = 'CSS898P' AND        
        table3.SOPTYPE >= 3 AND
        table3.SOPTYPE <= 4 AND
        table3.VOIDSTTS = 0 AND
        table3.GLPOSTDT >= '01/11/2008' AND
        table3.GLPOSTDT < '02/12/2008')
     INNER JOIN v_Combinetable1 table1 ON
        table2.ITEMNMBR = table1.ITEMNMBR AND
        table1.ITEMNMBR = 'CSS898P' AND
        table1.USCATVLS_5 = 'D13Class' AND
     }
ORDER BY
    table2.sopnumbe, table2.itemnmbr

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[JSHoltsIT]

still returns duplicates.

JS

 

[JSHoltsIT]

If I take the first line

SELECT
table2.dbname,table3.SOPTYPE, table3.GLPOSTDT, table3.VOIDSTTS,
table2.SOPTYPE, table2.SOPNUMBE, table2.ITEMNMBR, table2.ITEMDESC, table2.XTNDPRCE, table2.EXTDCOST,

and change to

SELECT DISTINCT
table2.dbname,table3.SOPTYPE, table3.GLPOSTDT, table3.VOIDSTTS,
table2.SOPTYPE, table2.SOPNUMBE, table2.ITEMNMBR, table2.ITEMDESC, table2.XTNDPRCE, table2.EXTDCOST,

Then my duplicates go away. I'd prefer NOT to use select DISTINCT though (I'm not certain that I can with a crystal report calling these views).

JS