Selecting from multiple tables

[luckydexte]

Hello All,

I am not an SQL expert by any means and I am getting stuck when trying to select multiple fields from two different tables. I understand why I am not able to do what I want to do but I am not sure how to get around it. Here is the SQL statement I have working so far:

SELECT RFT_PERMIT_TYPE, RFT_PERMIT_NO,
SUM(TOTAL_FEE_CHG) AS TOTAL_FEE_CHG,
SUM(TOTAL_FEE_PD) AS TOTAL_FEE_PD, SUM(TOTAL_FEE_BAL) AS TOTAL_FEE_BAL
FROM
(SELECT A.RFT_PERMIT_TYPE, A.RFT_PERMIT_NO, A.RFT_FEE_TYPE,
(CASE WHEN A.RFT_TRANS_TYPE = '2' THEN (A.RFT_TRANS_AMT * -1)
WHEN A.RFT_TRANS_TYPE = '5' THEN (A.RFT_TRANS_AMT * -1)
WHEN A.RFT_TRANS_TYPE = '7' THEN (A.RFT_TRANS_AMT * -1)
ELSE (A.RFT_TRANS_AMT * 1) END) AS TOTAL_FEE_BAL,
(CASE WHEN A.RFT_TRANS_TYPE = '2' THEN (A.RFT_TRANS_AMT * -1)
WHEN A.RFT_TRANS_TYPE = '0' THEN (A.RFT_TRANS_AMT * 1)
WHEN A.RFT_TRANS_TYPE = '1' THEN (A.RFT_TRANS_AMT * 1)
WHEN A.RFT_TRANS_TYPE = '9' THEN (A.RFT_TRANS_AMT * 1)
ELSE 0 END) AS TOTAL_FEE_CHG,
(CASE WHEN A.RFT_TRANS_TYPE = '6' THEN (A.RFT_TRANS_AMT * -1)
WHEN A.RFT_TRANS_TYPE = '5' THEN (A.RFT_TRANS_AMT * 1)
WHEN A.RFT_TRANS_TYPE = '7' THEN (A.RFT_TRANS_AMT * 1)
ELSE 0 END) AS TOTAL_FEE_PD
From ROW_PERMIT.ROW_PERMIT_IND B Inner Join
ROW_PERMIT.ROW_FEE_TRANSACTION A On B.PERMIT_TYPE = A.RFT_PERMIT_TYPE
And B.PERMIT_NO = A.RFT_PERMIT_NO
Where (A.RFT_DATE > '01-JAN-08'))
GROUP BY RFT_PERMIT_TYPE, RFT_PERMIT_NO
ORDER BY RFT_PERMIT_NO

This gives me a result set that I can use but I also need fields from the table ROW_PERMIT.ROW_PERMIT_IND. Because I am selecting these values using a subselect from the ROW_FEE_TRANSACTION table I cannot access values from the other table. Any ideas?

Thanks in advance,

Brandon

 

[bborissov]

Could you please poset some example data and what you want from that data as a result?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[luckydexte]

Here is some example data. Let me say that the table design is really, really poor which is why I am having to code around this. A result set from the SQL statement above would be:

INDIVIDUAL 2336 -40 -40 0
INDIVIDUAL 7425 46 0 46
INDIVIDUAL 20130 177 177 0
INDIVIDUAL 20342 469.44 469.44 0
INDIVIDUAL 20814 223 223 0

These are the five fields I am getting back from the sql command. I would like to add a field called PURPOSE to the result set. The field is in the table joined (ROW_PERMIT.ROW_PERMIT_IND) to ROW_FEE_TRANSACTION. I need a few fields from the table that I am joining. Make sense?

Thanks,

Brandon

 

[bborissov]

[COLOR=blue]SELECT[/color] RowTrans.RFT_PERMIT_TYPE, 
       RowTrans.RFT_PERMIT_NO,
       SUM(RowTrans.RFT_TRANS_AMT * [COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] RowTrans IN([COLOR=red]'2'[/color],[COLOR=red]'5'[/color],[COLOR=red]'7'[/color]) [COLOR=blue]THEN[/color] -1 [COLOR=blue]ELSE[/color] 1 [COLOR=blue]END[/color]) [COLOR=blue]AS[/color] TOTAL_FEE_BAL,
       SUM(RowTrans.RFT_TRANS_AMT * [COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] RowTrans = [COLOR=red]'2'[/color] 
                                              [COLOR=blue]THEN[/color] -1
                                         [COLOR=blue]WHEN[/color] RowTrans IN ([COLOR=red]'0'[/color],[COLOR=red]'1'[/color],[COLOR=red]'9'[/color]) 
                                              [COLOR=blue]THEN[/color] 1
                                    [COLOR=blue]ELSE[/color] 0 [COLOR=blue]END[/color]) [COLOR=blue]AS[/color] TOTAL_FEE_CHG,
       SUM(RowTrans.RFT_TRANS_AMT * [COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] RowTrans = [COLOR=red]'6'[/color] 
                                              [COLOR=blue]THEN[/color] -1
                                         [COLOR=blue]WHEN[/color] RowTrans IN ([COLOR=red]'5'[/color],[COLOR=red]'7'[/color]) 
                                              [COLOR=blue]THEN[/color] 1
                                    [COLOR=blue]ELSE[/color] 0 [COLOR=blue]END[/color]) [COLOR=blue]AS[/color] TOTAL_FEE_PD,
        [COLOR=blue]FROM[/color]
[COLOR=blue]From[/color] ROW_PERMIT.ROW_PERMIT_IND RowInd
     [COLOR=blue]Inner[/color] [COLOR=blue]Join[/color] ROW_PERMIT.ROW_FEE_TRANSACTION RowTrans. [COLOR=blue]On[/color] RowInd.PERMIT_TYPE = RowTrans.RFT_PERMIT_TYPE
            And RowInd.PERMIT_NO = RowTrans.RFT_PERMIT_NO
[COLOR=blue]Where[/color] (RowTrans.RFT_DATE > [COLOR=red]'20080101'[/color])
[COLOR=blue]GROUP[/color] [COLOR=blue]BY[/color] RFT_PERMIT_TYPE, RFT_PERMIT_NO
[COLOR=blue]ORDER[/color] [COLOR=blue]BY[/color] RFT_PERMIT_NO

(not tested)

You could add whatever fields you like from ROW_PERMIT_IND, just don't forget to add than in a GROUP BY clause if they didn't involved in some aggregate function.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[luckydexte]

Thank you for your help. I cannot try this now because they took our servers down for maintenance. I will run it in the morning and let you know. Again, I really appreciate the help.

Thanks,

Brandon

 

[luckydexte]

I was able to connect to the test database and it worked great. Thanks for your help.

Brandon