Returning Too Many Records

[goduke]

I have a report that is very simple but when it runs, it seems to be filtering through more records than what it should. I have 4 tables, a users table, a points_history table, a location table, and a region table. They are linked as follows:

SELECT users.uid, users.name, points.award_date, points.points, points.type, points.payable, location.desc, region.desc
FROM users
INNER JOIN points
on users.uid = points.uid
LEFT JOIN location
on users.locationid = location.locationid
LEFT JOIN region
on users.regionid = region.regionid
WHERE user.status = 1
AND points.payable = 1
AND region.regionid <> 2

Simple query, but when I generate the report, in the bottom right corner, it displays the number of records "out of" another number of records. In the end, I get 1,434 of 35,000. Why is crystal filtering through the extra records when I have an inner join on the history table. I only want the records from the history table for active users (users.status = 1).

The other two tables are a left join in case the users locationid is null.

Any help would be appreciated. Thanks.

 

[kray4660]

Just had a thought on this. Do you have anything in the selection criteria?

 

[goduke]

I hope I didnt confuse you. I am using the actual tables and linking them through crystal - I just put the SQL Query to show how my tables are linked.

That said, yes, I do have something in the selection criteria. I have:
user.status = 1
AND points.payable = 1
AND region.regionid <> 2
AND points.awarddate in {?Date_Range}

I forget that parameters dont show when you view your SQL query in Crystal. Hope I didnt confuse you.

 

[lbass]

First, you should be using {users.regionid} in your selection formula instead of the region.regionid, since selecting on that effectively undoes your left join.

Second, you SHOULD see the date range in the SQL query, and if you don't, it means it is not passing to the SQL--and could account for all the records. Are you using a command as your datasource? If so, you should be creating parameters (start and end) within the command, and adding it to the where clause in the command, NOT doing it body of the report.

-LB

 

[goduke]

My apologies on my typo for my SQL statement. I have it correct in the report, just typed it wrong. The last left join should be LEFT JOIN region on location.regionid = region.regionid.

SELECT users.uid, users.name, points.award_date, points.points, points.type, points.payable, location.desc, region.desc
FROM users
INNER JOIN points
on users.uid = points.uid
LEFT JOIN location
on users.locationid = location.locationid
LEFT JOIN region
on location.regionid = region.regionid
WHERE user.status = 1
AND points.payable = 1
AND region.regionid <> 2

I am not using any commands. I do have a parameter on the awarddate from the points table, but it is not showing up when I view my SQL Query. Here is what it shows in Crystal:

SELECT "users"."name", "points"."awarddate", "points"."points", "points"."reason", "points"."type", "points"."payable", "points"."prospectid", "region"."description", "region"."regionid", "users"."status"
FROM {oj (("fsbhome"."users" "users" LEFT OUTER JOIN "fsbhome"."location" "location" ON "users"."locationid"="location"."locationid") INNER JOIN "fsbhome"."points" "points" ON "users"."uid"="points"."uid") LEFT OUTER JOIN "fsbhome"."region" "region" ON "location"."regionid"="region"."regionid"}
WHERE "points"."payable"=1 AND "region"."regionid"<>2 AND "users"."status"=1

I just noticed the "oj" around the whole thing. Is that the problem?

 

[lbass]

The left joins are doing no good if you add the selection criteria on region.regionId. You will only get records back that meet that criterion--any records with nulls in the two location tables will be omitted.

Please go to report->selection formula->record and copy what you see there into the thread.

-LB

 

[goduke]

Here is my record selection formula:
{users.status} = 1 and
{region.regionid} <> 2 and
{points.payable} = 1 and
{points.awarddate} in {?Date_Range}

Here is a screenshot of how my tables look.

http://img685.imageshack.us/img685/263/tablesm.jpg