Problem with SQL statement in CR8.5

[bb25]

Hi all..

I have a scenario like this.
I have 3 tables in my report.. and all 3 tables are supposed to each other using DebtID.

I have the following SQL statement:

SELECT
sales_detail1.`DEBT_ID`, sales_detail1.`SELL_DATE`, sales_detail1.`CODE`,
out_product1.`DEBT_ID`, out_product1.`PRODUCT_CODE`, out_product1.`PRODUCT_TYPE`, out_product1.`QTY`, out_product1.`PER_UNIT_SP`,
receipt1.`DEBT_ID`, receipt1.`PAYMENT_MODE`, receipt1.`TOTAL_AMOUNT`, receipt1.`GST_AMOUNT`, receipt1.`PAID_AMOUNT`, receipt1.`Receipt_id`
FROM
`sales_detail` sales_detail1, `out_product` out_product1, `receipt` receipt1
WHERE
sales_detail1.`DEBT_ID` = 'SC19' AND
(receipt1.`DEBT_ID` = 'SC19' OR
out_product1.`DEBT_ID` = 'SC19')
ORDER BY
sales_detail1.`DEBT_ID` ASC
'==============================================

I'm gussing my problems lies with my WHERE clause?
I need to 1st condition Sales_detail1.debt_id and another one of the conditions in () to be true.

But now my problem is such that for the 1st 2 conditions, everything works fine. But when it comes to the 3rd condition: out_product1.`DEBT_ID` = 'SC19', it prints out all the records in the table, even if there is no such debt_id in the out_product table.

And if there is such a debt_id, it repeats the out_product.product_code over and over and over again... from a 1 sentence report... I can get up tp 10 pages....
all with the same product codes.... repeating.....

anyone can help me with this?
Been stuck for a couple of days.. but still can't figure out what is wrong here..
Thanks.

 

[Katy44]

I'm not sure what is wrong with the SQL but Crystal 8.5 in my opinion didn't always read the SQL statements you pass it in the way you'd expect. Have you tried putting the conditions in the select expert? (And the order by in the sort expert?)

 

[IanWaterman]

BB25

You certainly do have a problem with your Where clause, you have not related the tables.

Its not surprising that you are getting pages and pages, you are bring back all permutations of the data in all 3 tables.

Ian Waterman
UK Crystal Consultant

 

[kskid]

I agree. Looks like you wrote this in Crystal SQL Designer rather than Crystal Report. What is in your Show SQL Query Statement?