Right and left outer join

[pnad]

Hello all,

I have a Crystal Report v 10 that goes against a FoxPro DB.
The DB has the following three tables:

Table Name: Employee
Columns: emp_pk, emp_first, emp_last

Table Name: SchedBook
Columns: sbk_empfk, sbk_hours, sbk_date

Table Name: VacTime
Columns: vac_empfk, vac_date, vac_hours

I need the number of hours worked(sched_hours), number of vacation hours(vac_hours), employee last name(emp_last) for a given employee for a given duration (sched_date, vac_date between ?startDate and ?EndDate)

I am using the following query to get the data:

SELECT `employee`.`emp_last`, `schedbook`.`sbk_date`, `schedbook`.`sbk_hours`, `vactime`.`vat_date`, `vactime`.`vat_hours`
FROM (`employee` `employee` RIGHT OUTER JOIN `schedbook` `schedbook` ON `employee`.`emp_pk`=`schedbook`.`sbk_empfk`) RIGHT OUTER JOIN `vactime` `vactime` ON `schedbook`.`sbk_empfk`=`vactime`.`vat_empfk`
WHERE `employee`.`emp_last`='Iacobucci' AND (`schedbook`.`sbk_date`>={d '2006-06-11'} AND `schedbook`.`sbk_date`<={d '2006-06-17'})
ORDER BY `employee`.`emp_last`, `schedbook`.`sbk_date`


But when the data comes on the report, everything is triplicated. I have tried all possible combinations of joins but it still doesnt work.

Any help will be highly appreciated.

 

[synapsevampire]

Do you mean that you've created the SQL yourself in an Add Command, or that this is the SQL that Crystal developed. Stating specifics helps.

If they are truly dupes, and you've written the SQL, add a DISTINCT after the SELECT.

If not, use Database->Select Distinct Records.

If you still get additional rows, then you are mistaken, they are not EXACT dupes, what you're experiencing is row inflation, and you need to understand the database to wrok around it in SQL, or perhaps use grouping on the unique fields and place the fields in the group footer and suppress the details to eliminate some of the extra rows.

-k

 

[pnad]

Thank you synapsevampire for your quick response.

The SQL that I had posted is the one that Crystal generated. I tried using Database->Select Distinct Records but it still gives me wrong/triplicate values back.

I have a feeling that it is something to do with the joins and the way that they tables are linked in Crystal. As you can tell from the query, they are right now using right outer joins.

 

[synapsevampire]

It does NOT give you wrong nor triplicate rows, you don't understand your data.

If you placed all fields that are returned alongside each other you'd see where the differences exist.

And if you check the Database->Show SQL Query you'll see that the SELECT will be followed by a DISTINCT, which means that the database is performing a dedupe for you.

This is a common thing, and it speaks only to the fact that you are unfamiliar with databases and what is returned by queries.

If you have an orders table joined to an orders detail table and you select the order number from the orders table and a product from the details table, but the orders details table has numerous details for the one product, you'll get multiple rows for it. Thsi is how SQL works, and for good reason.

Please don't post again that SQL doesn't work, read up on the topic, or do as I originally suggested and create groups in the Crystal Report for the level of uniqueness you seek and display in the group section, suppressing the details.

It may be that you can write advanced SQL to eliminate the rows, but we'd need to know all of the tables involed, and what you are mistakenly claiming to be dupes.

Since the notion of row inflation is new to you, and the complexities of which exceed the scope of assisting with Crystal in this forum, I suggest that you study a bit or just use the solution provided.

btw, if you have a dba, ask them to create a View for you or supply the SQL to eleiminate it and instead of using the Crystal GUI to get data, use the Add Command listed under yoru data source and paste in the supplied SQL.

-k

 

[pnad]

I am already doing what you suggested and it still doesnt work. Just to clarify, I do understand how databases work. And I still say that the problem lies in the way that I am joining the tables in Crystal.