Query question, OINV, OCRD, CRD1

[JeffWill]

When I try to create a query with the 3 tables above when I add the CRD1 the invoice numbers begin to duplicate. Is this a problem with the JOIN statements automatically created by the Generator?

Thanks, Jeff

SELECT *

FROM OINV T0 INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode

INNER JOIN CRD1 T2 ON T1.CardCode = T2.CardCode

[former_member187989]

hi,

Query will be

SELECT *
FROM OINV T0 INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode
INNER JOIN CRD1 T2 ON T1.CardCode = T2.CardCode and T2.AdresType = 'B'

Jeyakanthan

[Former Member]

That is because you have more than one records in CRD1. You should not join this table unconditionally.

Thanks,

Gordon

[JeffWill]

Gordon,

Thank you, I understand that there may be more than one record in CRD1. Is there a JOIN statement possibility I could use to just show the one invoice number from the OINV? I want my query to produce just one line based on the DocNum in OINV, I don't need to duplicate them because of the CRD1 having more than one record.

Thanks, Jeff

[Former Member]

There is no link between these two tables. When your invoice was created, the ship to address will be compressed to one field called address2

[JeffWill]

Thank you, that makes sense. I've found a way to make the query work with just the OCRD.

Jeff