Problem in Query with JOIN Function in OCRD CRD1 OCPR tables

[Jos_Dielemans]

Hello Dear Forum Users,

I want to make a query which shows me per business partner from OCRD - Addres; CRD1 - Delivery Address and from OCPR - Contactperson information

Is it possible to show it in one row per Business Partner. Now I get (the classic problem) of several rows and a duplication of the contact persons per (delivery) address.

My query is:

SELECT T0.[CardCode], T0.[CardName], T1.[Address], T1.[Street], T1.[ZipCode], T1.[City], T1.[Country], T1.[U_TelNr], T1.[U_MobNr], T1.[U_OpenTijd], T1.[U_LosIns_1], T1.[U_LosIns_2],T2.[Title], T2.[Name] as 'Voornaam', T2.[Address] as 'Achternaam', T2.[Position] as 'Functie', T2.[Tel1], T2.[Cellolar], T2.[E_MailL], T2.[BirthDate] FROM OCRD T0 LEFT OUTER JOIN CRD1 T1 ON T0.CardCode = T1.CardCode LEFT OUTER JOIN OCPR T2 ON T0.CardCode = T2.CardCode

Can you help me ?

Jos Dielemans - Maastricht

Edited by: J. Dielemans on Apr 29, 2011 4:28 PM

Changed the query with Left Outer Join

[Jos_Dielemans]

I have found the solution myself:

By using the Union All function I could combine two queries. Here is the result:

SELECT

T0.[CardCode], T0.[CardName], T1.[Address] , T1.[Street], T1.[ZipCode], T1.[City], T1.[Country], T1.[U_TelNr], T1.[U_OpenTijd] , T1.[U_LosIns_1] , T1.[U_LosIns_2]'

FROM OCRD T0 LEFT OUTER JOIN CRD1 T1 ON T0.CardCode = T1.CardCode

WHERE T0.[CardCode] >= 'D00000'

Union all

SELECT

T0.[CardCode], T0.[CardName], T2.[Position], T2.[Tel1], T2.[Title], T2.[Name], T2.[Address], T2.[Position], T2.[Tel1], T2.[Cellolar], T2.[E_MailL]

FROM OCRD T0 LEFT OUTER JOIN OCPR T2 ON T0.CardCode = T2.CardCode

WHERE T0.[CardCode] >= 'D00000'

Order by 1

Now i got the result I was looking for.