Multiple Join types??

[Montana751]

I am trying to display Account Data even if no items exist in an Item table. You'd think joining the Account (primary) table with a Left Out Join to the Item table would deliver the results, but its not. Not sure if its because I have Equal To Joints from the Item table to other tables, like Class, Department, etc.

However, if I change the join between the Account and Item tables to a Greater or Equal To, then the data returns. This is not acceptable because sometime the AccountId is Less Than, so these values are not displayed on the report.

I have to display Account information even if there are no items associated with the Account.

Any suggestions???

 

[beardenb]

You might be right regarding the other joins to your item table but this differs from one database to another. You should be able to see how CR is handling joins by looking at the SQL query it created. To do this, go to the Database menu, then Show SQL Query.... If that doesn't answer your question you can post the query here. Also, what database are you connecting to? What version of CR?

--Bearden

 

[Montana751]

Crystal 8.5, Oracle 9i - using ODBC not native drivers.

Here is the query; I need to return ALL items from the Account table even if no items exist in the Item table.

SELECT
ACCOUNT.ID, ACCOUNT.POOLSIDE, ACCOUNT.NAME,
ITEM.ID, ITEM.COMPANYID, ITEM.ACCOUNTID, ITEM.AMOUNT,
RECONCILESTATUS.ITEMSTATE,
CLASS.NAME,
RECONCILEAMOUNT.AMOUNT,
MATCHGROUPS.MATCHDATE, MATCHGROUPS.RESOLUTIONDATE,
ITEMTYPES.NAME, ITEMTYPES.TYPEDEBIT,
FIELDVALS.ID,
DEPARTMENT.NAME
FROM
{ oj (((((((ACCOUNT ACCOUNT LEFT OUTER JOIN ITEM ITEM ON
ACCOUNT.COMPANYID = ITEM.COMPANYID)
INNER JOIN CLASS CLASS ON
ITEM.CLASS = CLASS.ID)
LEFT OUTER JOIN RECONCILEAMOUNT RECONCILEAMOUNT ON
ITEM.ID = RECONCILEAMOUNT.ITEMID)
LEFT OUTER JOIN MATCHGROUPS MATCHGROUPS ON
ITEM.MATCHID = MATCHGROUPS.ID)
INNER JOIN ITEMTYPES ITEMTYPES ON
ITEM.DCIP = ITEMTYPES.ID)
INNER JOIN FIELDVALS FIELDVALS ON
ITEM.ITEMSTATE = FIELDVALS.VALUEX)
INNER JOIN DEPARTMENT DEPARTMENT ON
ITEM.DEPARTMENTID = DEPARTMENT.ID)
LEFT OUTER JOIN RECONCILESTATUS RECONCILESTATUS ON
ITEM.ID = RECONCILESTATUS.ITEMID}
WHERE
FIELDVALS.ID = 195
ORDER BY
ACCOUNT.NAME ASC

 

[lbass]

Try making all joins to the right of the left join, left joins also, i.e., all joins from the item to the other tables should also be left joins.

-LB

 

[fredp1]

The reson why you missed records is because of the filter of the fieldvals.id=195 By having that on the 'right hand side' of the join, it will limit the accounts that had a fieldvals=195. There is a technique to get around that I'll just test it and repost.

fred

 

[Montana751]

I beleive I already tried setting all joins from the primary table as Left Outers and that didn't work.

However, I'm now considering the possibility that the database is the problem and not the join. But then again, I tested on a SQL database and received the same type of results. But what I don't get is why it would work with a Greater/Equal To join???

In some preliminary testing, I found that if I have 1 item in a company (which a company can have a gazillion accounts), then it works. So as a work around, I may just create a dummy account and put an item in it, then block it from displaying in the report.

This requires some intense testing and I will be out of town next week.

In regards to the FieldsVals=195, I only want Account information relative to this value.

Thank you for all of your suggestions.

 

[lbass]

You say:

In regards to the FieldsVals=195, I only want Account information relative to this value."

And also say that you want ALL accounts. It is likely that not all accounts will show this value. If you want to show all accounts and then show information only for accounts with this value, then you must use left joins FROM Accounts to Items, and FROM Items to each of the remaining tables. Then you can create a formula like the following:

if isnull({FieldsVals.ID}) or
{FieldsVals.ID} <> 195 then 0 else
{table.number} //or then "" else {table.string}

This will return all accounts with information only for those records which meet your criterion. You cannot place a select on a table to the right of a left join and still return all records from the left of the join.

-LB

 

[cmmrfrds]

To add to the explanation, you need to correct what Fred pointed out first. If the final sql looks like this then the left side records will be included.

WHERE
FIELDVALS.ID = 195 OR FIELDVALS.ID IS NULL

fieldvals.id will be null when there is no record on the right side. You need to explicitly include when there is a criteria on a right side table as above. Typically, the where clause is applied after the join. So, the left join did work but the records where elminated by the where clause.

 

[lbass]

Although in cmmrfrds' solution, I believe you will lose accounts which are not null, but with FieldsVal <> 195.

-LB

 

[fredp1]

Sorry for the delay, I was looking for the solution that lbas posted.(the timezone in Austrlia doesn't help either). This way, the solution is done in Crystal.
Another way to achieve what Montana need is by creating a view with the filter applied to it. You can try editing the SQL in CR but I never recommend doing that as when you verify the db, you can loose the code. The whole SQL looks like this

e.g.

SELECT
ACCOUNT.ID, ACCOUNT.POOLSIDE, ACCOUNT.NAME,
ITEM.ID, ITEM.COMPANYID, ITEM.ACCOUNTID, ITEM.AMOUNT,
RECONCILESTATUS.ITEMSTATE,
CLASS.NAME,
RECONCILEAMOUNT.AMOUNT,
MATCHGROUPS.MATCHDATE, MATCHGROUPS.RESOLUTIONDATE,
ITEMTYPES.NAME, ITEMTYPES.TYPEDEBIT,
FIELDVALS.ID,
DEPARTMENT.NAME
FROM
{ oj (((((((ACCOUNT ACCOUNT LEFT OUTER JOIN ITEM ITEM ON
ACCOUNT.COMPANYID = ITEM.COMPANYID)
left outer JOIN CLASS CLASS ON
ITEM.CLASS = CLASS.ID)
LEFT OUTER JOIN RECONCILEAMOUNT RECONCILEAMOUNT ON
ITEM.ID = RECONCILEAMOUNT.ITEMID)
LEFT OUTER JOIN MATCHGROUPS MATCHGROUPS ON
ITEM.MATCHID = MATCHGROUPS.ID)
Left outer JOIN ITEMTYPES ITEMTYPES ON
ITEM.DCIP = ITEMTYPES.ID)
Left outer join (
Select FIELDVALS.VALUEX from
FIELDVALS FIELDVALS
where FIELDVALS.ID = 195
) T1 ON
ITEM.ITEMSTATE = T1.VALUEX)
Left outer JOIN DEPARTMENT DEPARTMENT ON
ITEM.DEPARTMENTID = DEPARTMENT.ID)
LEFT OUTER JOIN RECONCILESTATUS RECONCILESTATUS ON
ITEM.ID = RECONCILESTATUS.ITEMID}

ORDER BY
ACCOUNT.NAME ASC

I also concur with lbas last comment about cmmrfrds' solution.

Fred

 

[Montana751]

Thanks for all of the suggestions, I will try them out. I will not be able to post my results for a week or so (leaving for California for a week with no access to a computer).