Problems with simple joins :(

[fragglemoo]

I'm a novice to Crystal Reports/SQL and I am trying to create a simple report in CR2008 that brings up the following info:

Reference Custom Field.

There are 16 records and 2 of these have an item in the custom field, but however I change the joins I can only get 2 records on the report. I’ve had various database “experts” changing the joins but nothing we do brings up all 16 records.

I have no SQL knowledge so the details below are cut and pasted from CR.

SELECT "DEFECTS"."ProjectID", "DEFECTS"."Reference", "CSTDEVTVAL"."idCustRec", "CSTDEVTVAL"."CustValue"
FROM "TTPPROJECTS"."dbo"."DEFECTS" "DEFECTS" INNER JOIN ("TTPPROJECTS"."dbo"."CSTDEVTVAL" "CSTDEVTVAL" INNER JOIN "TTPPROJECTS"."dbo"."DEFECTEVTS" "DEFECTEVTS" ON "CSTDEVTVAL"."idDefEvt"="DEFECTEVTS"."idRecord") ON ("DEFECTS"."idRecord"="DEFECTEVTS"."ParentID") AND ("DEFECTS"."ProjectID"="DEFECTEVTS"."ProjectID")
WHERE "DEFECTS"."ProjectID"=1 AND "CSTDEVTVAL"."idCustRec"=504

If anyone can help it would be appreciated and I will try and provide more info.

 

[IanWaterman]

YOur condition "CSTDEVTVAL"."idCustRec"=504
overides any change you do to the joins.

I assume this condition is set in select expert.

In Database expert - Links

Change join to left outer - double click join and select lo button.

In you select expert change filter

(isnull({CSTDEVTVAL.idCustRec}) or {CSTDEVTVAL.idCustRec}=504)

Ian

 

[fragglemoo]

Hi Ian

Thank you for responding. The database I'm working with is Test Track Pro - the CSTDEVTVAL table contains a field that has 600 custom fields 504 being the one I was looking at.

Please bear in mind I've only been using CR for 2 days. I've not had any other problems with fields or joins etc just this pesky lot. I need to do a lot of reports based on these custom fields.

I've changed all the joins as you suggested but I'm not sure where or how to do the expert change filter.

Thanks

 

[fragglemoo]

I've changed the filter but its still only returning 2 records

Any further suggestions greatly received as I need to get this bit sorted asap so I can add all the other tables etc to the report.

Thanks

 

[IanWaterman]

go into Database - Show SQL copy and paste that here together with your select statement.

I can then try and see what is going on.

Ian

 

[fragglemoo]

Thanks Ian

SELECT "DEFECTS"."ProjectID", "DEFECTS"."Reference", "CSTDEVTVAL"."idCustRec", "CSTDEVTVAL"."CustValue"
FROM "TTPPROJECTS"."dbo"."CSTDEVTVAL" "CSTDEVTVAL" LEFT OUTER JOIN ("TTPPROJECTS"."dbo"."DEFECTS" "DEFECTS" LEFT OUTER JOIN "TTPPROJECTS"."dbo"."DEFECTEVTS" "DEFECTEVTS" ON ("DEFECTS"."idRecord"="DEFECTEVTS"."ParentID") AND ("DEFECTS"."ProjectID"="DEFECTEVTS"."ProjectID")) ON "CSTDEVTVAL"."idDefEvt"="DEFECTEVTS"."idRecord"
WHERE "DEFECTS"."ProjectID"=1 AND ("CSTDEVTVAL"."idCustRec" IS NULL OR "CSTDEVTVAL"."idCustRec"=504)

 

[lbass]

Do you have the option of using a command as your datasource?

Note that you have the left outer join backwards--it should be FROM the table that has all the records TO the table that doesn't, and there should be NO selection on the right hand table. It may be possible to select on the right table if you are able to write the SQL query directly in a command, however.

-LB

 

[IanWaterman]

sorry misread your earlier posting.

Your links are

CSTDEVTVAl -lo-> DeFECTS - lo-> DEFECTEVTS

Thus your select statement should now include

(isnull({DEFECTS.ProjectID}) or {DEFECTS.ProjectID}=1)

Not the statement I sent you earlier. I originally thought your joins were the other way round.

Ian

 

[fragglemoo]

Thanks for your help. its all worknig now.