Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: 2 table problem Post Reply Post New Topic
Author Message
kevincloud999
Newbie
Newbie


Joined: 09 May 2008
Location: Japan
Online Status: Offline
Posts: 6
Quote kevincloud999 Replybullet Topic: 2 table problem
    Posted: 15 May 2008 at 9:42am
Dead i have 2 table ARCASH and ARMAST
i select from arcash  -  invno,custno, paidamt
i select from armast  -  invno,custno,invamt

when i drop from the arcash table invno custno paidamt it work nice
now when i drop from the armast table invno i get that nice but it mess up the arcash invno custno paidamt one value get repeat over and over the first record value of the arcash

i have no clue what to do hereCry

kevin.

ok but b4 i go i tell what i am trying to do
in the arcash it has record for customer that wont be in the armast table and the same way around for the armast and arcash so i am trying to get custno to display all armast and arcash





Edited by kevincloud999 - 15 May 2008 at 9:43am
IP IP Logged
Lugh
Senior Member
Senior Member
Avatar

Joined: 14 Nov 2007
Online Status: Offline
Posts: 377
Quote Lugh Replybullet Posted: 16 May 2008 at 5:48am
You want to use a full outer join between your tables.

Be very careful when doing this, though, as navigating the selection criteria becomes tricky.  Be sure to include tests for null values. 
IP IP Logged
kevincloud999
Newbie
Newbie


Joined: 09 May 2008
Location: Japan
Online Status: Offline
Posts: 6
Quote kevincloud999 Replybullet Posted: 16 May 2008 at 1:05pm
hey lugh i did what you said samething one record over and over
what do you mean by tests for null?


Edited by kevincloud999 - 16 May 2008 at 1:12pm
IP IP Logged
kevincloud999
Newbie
Newbie


Joined: 09 May 2008
Location: Japan
Online Status: Offline
Posts: 6
Quote kevincloud999 Replybullet Posted: 17 May 2008 at 8:03am
i am going to repost my question let more detail

i have 4 table ARCASH,ARYCSH,ARMAST and ARYMST
ARCASH is a current tran.
ARYCSH is history Tran.
ARMAST is current Tran.
ARYMST is History Tran.

what i want to do is
UNION ARYCSH AND ARCASH
UNION ARYMST AND ARMAST

and make a relation link between the 2 union table on field "CUSTNO"
now the problem is when i do this
this is what i get

the custno show up and the union data from the arcash/arycsh show up and the union data from arymst/armast the first record keep repeat it self this is what i mean

CUSTNO----------PAIDAMT(ARCASH/ARYMST)------------INVAMT(ARMAST/ARYMST)
ALL001 ----------11000------------------------------------- 12500
CUS101 ----------900---------------------------------------- 12500
CAR124 ----------700---------------------------------------- 12500
GOE451 ----------4500--------------------------------------- 12500
TOL003 -----------1587 ---------------------------------------12500
ALL001 ------------25487------------------------------------- 12500
ALL002------------- 5787-------------------------------------- 12500
ALL001 -------------5465 --------------------------------------12500

as you can see armast/arymst first record over and over
how do i fix this or get it to work

thanks
kevin.

IP IP Logged
Lugh
Senior Member
Senior Member
Avatar

Joined: 14 Nov 2007
Online Status: Offline
Posts: 377
Quote Lugh Replybullet Posted: 19 May 2008 at 5:48am
I would be willing to bet that, if you look far enough down your report, the INVAMT does change.  At which point, you see all the PAIDAMT values in order again.  Basically, I think you've ended up with a very large cross-product.

One question that pops into my head is this: What role, if any, does the invno field play in this relationship?  Can you have multiple invno records per custno?  If so, I'd wager that's where your cross-product is happening.

I'm thinking you're going to need to use the SQL Command to pull this off.  Mostly because of the UNION bit.  I think the Command should look like:


SELECT Cash.*, Mast.*
FROM
    (SELECT CustNo, InvNo, PaidAmt FROM ARCASH
     UNION
     SELECT CustNo, InvNo, PaidAmt FROM ARYCSH) Cash
FULL JOIN
    (SELECT CustNo, InvNo, PaidAmt FROM ARMAST
     UNION
     SELECT CustNo, InvNo, PaidAmt FROM ARYMST) Mast
ON CASH.CustNo = Mast.CustNo AND Cash.InvNo = Mast.InvNo


Now, obviously, you're going to want to flesh that out a bit with WHERE clauses, but I think the structure of the final query should be clear.

I added a clause to the join condition for the InvNo fields to also match up.  That may not be what you actually want.  Another option is to leave the InvNo out of the equation altogether.  In order to do that, you will need to summarize the data, so that you have a single record per CustNo on each side of the join, like so:


SELECT Cash.*, Mast.*
FROM
    (SELECT CustNo, SUM(PaidAmt) AS PaidCash
     FROM
        (SELECT CustNo, PaidAmt FROM ARCASH
         UNION
         SELECT CustNo, PaidAmt FROM ARYCSH)
     GROUP BY CustNo) Cash
FULL JOIN
    (SELECT CustNo, SUM(PaidAmt) AS PaidMast
     FROM
        (SELECT CustNo, PaidAmt FROM ARMAST
         UNION
         SELECT CustNo, PaidAmt FROM ARYMST)
     GROUP BY CustNo) Mast
ON CASH.CustNo = Mast.CustNo



IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.016 seconds.