Q:

SQL query for crystal reports produces duplicate results

I created 3 tables TstInvoice, TstProd, TstPersons and added some data:
INVOICE_NBR CLIENT_NR   VK_CONTACT
A10304      003145      AT  
A10305      000079      EA  
A10306      004458      AT  
A10307      003331      JDJ 

PROD_NR INVOICE_NBR
P29366  A10304
P29367  A10304
P29368  A10305
P29369  A10306
P29370  A10306
P29371  A10307

PERS_NR INITIALEN   STATUS  PERSOON
0001    AT          7       Alice Thompson           
0002    EA          1       Edgar Allen              
0003    JDJ         1       John Doe Joe             
0004    AT          1       Arthur Twins  

The parameter that is passed to the crystal report is the INVOICE_NBR.
On my crystal report I put some fields from the databases and one sql expression:
(
SELECT "TstPersons"."PERSOON" FROM "TstPersons"
WHERE "TstPersons"."INITIALEN" = "TstInvoice"."VK_CONTACT" AND "TstPersons"."STATUS" = 1
)

The full query that is generated:
 SELECT "TstInvoice"."INVOICE_NBR", "TstInvoice"."CLIENT_NR", "TstPersons"."STATUS", "TstPersons"."PERSOON", "TstProd"."PROD_NR", "TstProd"."INVOICE_NBR", (
SELECT "TstPersons"."PERSOON" FROM "TstPersons"
WHERE "TstPersons"."INITIALEN" = "TstInvoice"."VK_CONTACT" AND "TstPersons"."STATUS" = 1
)
 FROM   ("GCCTEST"."dbo"."TstInvoice" "TstInvoice" INNER JOIN "GCCTEST"."dbo"."TstProd" "TstProd" ON "TstInvoice"."INVOICE_NBR"="TstProd"."INVOICE_NBR") INNER JOIN "GCCTEST"."dbo"."TstPersons" "TstPersons" ON "TstInvoice"."VK_CONTACT"="TstPersons"."INITIALEN"
 WHERE  "TstInvoice"."INVOICE_NBR"='A10304'

The result is as shown in the screenshot:

As you can see the TstPersons.PERSOON field is populated with Alice Thompson and the sql expression field is correctly populated with Arthur Twins. However, I would like only to see the prod_nr once. With this query it produces the prod numbers twice because of the double entry for "AT" despite the fact that I ask for only status 1. I could just delete the old entry but I want to know if it's possible this way.
* edit * I added the status = 1 to the "record selection formula editor" and that seems to work. Not need the sql expression field at all. Not sure if this is the correct way to go though.
So now it looks like this:
 SELECT "TstInvoice"."INVOICE_NBR", "TstInvoice"."CLIENT_NR", "TstPersons"."STATUS", "TstPersons"."PERSOON", "TstProd"."PROD_NR", "TstProd"."INVOICE_NBR"
 FROM   ("GCCTEST"."dbo"."TstInvoice" "TstInvoice" INNER JOIN "GCCTEST"."dbo"."TstProd" "TstProd" ON "TstInvoice"."INVOICE_NBR"="TstProd"."INVOICE_NBR") INNER JOIN "GCCTEST"."dbo"."TstPersons" "TstPersons" ON "TstInvoice"."VK_CONTACT"="TstPersons"."INITIALEN"
 WHERE  "TstInvoice"."INVOICE_NBR"='A10304' AND "TstPersons"."STATUS"=1

A:

You have a very weak join in your query due to the duplicate values found in the INITIALEN column.  Using the STATUS = 1 criteria is a work-around more than a solution because if you ever need to report on an invoice where the contact has a status other than 1, you will need to modify the report's design to allow your join to work because the STATUS value is not found on the invoice to allow a proper join to occur.
You are also running a risk of this work-around breaking down completely should you have another contact with both the same initials and status values as another.
The correct way to solve this problem would be to join TstInvoice to TstPersons through a field that has unique values.  The PERS_NR column appears to be a good choice for this.
This is also going to require a redesign of the TstInvoice table to include the PERS_NR column as a Foreign Key.
A stronger join between invoices and persons would also remove the need for that sub-query in you selection statement.  This would simplify your query down to the following:
SELECT "TstInvoice"."INVOICE_NBR", "TstInvoice"."CLIENT_NR", "TstPersons"."STATUS", "TstPersons"."PERSOON", "TstProd"."PROD_NR", "TstProd"."INVOICE_NBR"
FROM    "GCCTEST"."dbo"."TstInvoice" "TstInvoice" 
            INNER JOIN "GCCTEST"."dbo"."TstProd" "TstProd" 
                ON "TstInvoice"."INVOICE_NBR"="TstProd"."INVOICE_NBR"
            INNER JOIN "GCCTEST"."dbo"."TstPersons" "TstPersons" 
                ON "TstInvoice"."PERS_NR"="TstPersons"."PERS_NR"
WHERE  "TstInvoice"."INVOICE_NBR"='A10304'

