Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 352
  • Last Modified:

Query by Combining 2 tables.

Hi,

I have 2 tables:

1. Table2008.
2. Table2009.

Both of them has structure below:

1. AccNo.
2. Month01
3. Month02
...

13. Month12

Now, the user wants to select data from 8/2008 to 7/2009.

The result should be:

1. AccNo.
2. Month01   --- > Month08 of Table2008
3. Month02
...

13. Month12 -----> Month07 of Table2009.

How could I query it?

This is a very challenging query.

Thank you.
0
emi_sastra
Asked:
emi_sastra
  • 20
  • 12
  • 7
  • +1
1 Solution
 
razorBladesCommented:
what is the data type of Month1...Month12 on the table? is it a datetime field?
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Hope this helps:
Kindly fill in the gaps for the rest of the months.

SELECT t1.AccNo, t1.Month08 as Month01, t1.Month09 as Month02, ..., t1.Month12 as Month05, t2.Month1 as Month06, ..., t2.Month07 as Month12
FROM Table2008 t1, Table2009 t2
WHERE t1.AccNo = t2.AccNo

Open in new window

0
 
emi_sastraAuthor Commented:
Hi rrjegan17.

Month01 to Month12 are Money Type.

Thank you.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
emi_sastraAuthor Commented:
One more.

Data on Table2008 may not be the same with Table2009.

Thank you.
0
 
razorBladesCommented:
hi emi,

is rrjegan17's query satisfactory? If not can you give us an example with the data in the table?
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Can you provide some sample records for existing table and your desired result set so that it would be helpful to understand better.
0
 
emi_sastraAuthor Commented:
Hi All,

I am sorry, just back again.

Please see my tables, I export it into excel.

Thank you.

TABLE2007.xls
TABLE2008.xls
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Just saw your sample records.

Kindly provide the expected result set to help you out better.
0
 
emi_sastraAuthor Commented:
Ok.

Please the screen shot. It just could server the same year.
That's why I ask for help here.

Forget about those fields in excel Debet01, Kredit01 ---> Month01, Debet02, Kredit02 ---> Month02, to make it easy to make a query sample.

Table2007 of Data (Key AccNo) may not be the same as in Table2008.
Some AccNo Data exist in Table2007 but not in Table2008, and vice versa.

Thank you.
SAMPLE-RESULT.PNG
0
 
awking00Commented:
If you need all records from both tables, then use full outer join
SELECT (t1.kredit08 - t1.debet08) as month01, ...
FROM Table2007 t1 FULL OUTER JOIN Table2008 t2
ON t1.AccNo = t2.AccNo;
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
My query as per comment 24783224 would provide you exactly what you need.
have you tried that one out.
0
 
emi_sastraAuthor Commented:
Hi awking00,

Could the AccNo has the unique rows with all the month required?

Thank you.
0
 
awking00Commented:
rrjegan17,
Your query would only provide records where the acctno existed in both tables, which is why I said a full outer join is needed. Other than that, it also looks like some math needs to be done on the debet and kredit fields in the two tables to combine them.
0
 
emi_sastraAuthor Commented:
Hi rrjegan17,

I doubt below WHERE t1.AccNo = t2.AccNo, why using "where", since they may not have the same data.

Thank you.
0
 
awking00Commented:
emi_sastra,
I'm not sure I understand your last question. The AccNo will be unique assuming it is unique in the two source tables. If the AccNo exists in both tables, then there will be a value for all of the months. If it exists in one table and not the other, then the values will be null for the periods in which it doesn't exist.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
But as per the sample records provided and the results shown, I dont think it as a Full outer join.
Kindly refer the attached sample records.
0
 
awking00Commented:
From your attached sample records, in table2008, there is AccNo 156001 that doesn't exist in table2007, and in table2007 there is AccNo 610203 that doesn't exist in table2008. In order to include these records in the results table, you need to do the full outer join.
0
 
emi_sastraAuthor Commented:
Hi All,

Please see my code below.
It just using one  year table.

How to use two year table?
If could make it simpler is appreciated.

Thank you.
WITH CTE AS (SELECT     A.AccNo, 
                                                      A.Debet01 + A.Debet02 + A.Debet03 + A.Debet04 + A.Debet05 + A.Debet06 + A.Debet07 + A.Debet08 + A.Debet09 + A.Debet10 + A.Debet11
                                                       + A.Debet12 AS TotalDebet, 
                                                      A.Kredit01 + A.Kredit02 + A.Kredit03 + A.Kredit04 + A.Kredit05 + A.Kredit06 + A.Kredit07 + A.Kredit08 + A.Kredit09 + A.Kredit10 + A.Kredit11
                                                       + A.Kredit12 AS TotalKredit, CASE WHEN Saldo = 'D' THEN COALESCE (Debet09, 0) - COALESCE (Kredit09, 0) ELSE COALESCE (Kredit09,
                                                       0) - COALESCE (Debet09, 0) END AS Bln01, CASE WHEN Saldo = 'D' THEN COALESCE (Debet10, 0) - COALESCE (Kredit10, 0) 
                                                      ELSE COALESCE (Kredit10, 0) - COALESCE (Debet10, 0) END AS Bln02, CASE WHEN Saldo = 'D' THEN COALESCE (Debet11, 0) 
                                                      - COALESCE (Kredit11, 0) ELSE COALESCE (Kredit11, 0) - COALESCE (Debet11, 0) END AS Bln03, 
                                                      CASE WHEN Saldo = 'D' THEN COALESCE (Debet12, 0) - COALESCE (Kredit12, 0) ELSE COALESCE (Kredit12, 0) - COALESCE (Debet12, 0) 
                                                      END AS Bln04, B.GlobalAcc
                               FROM          THLEDGER2007 AS A LEFT OUTER JOIN
                                                      TMGLACCNO AS B ON A.AccNo = B.AccNo LEFT OUTER JOIN
                                                      TMGLTYPE AS C ON C.GLTypeCode = B.GLTypeCode
                               WHERE      (B.Global <> 'Y') AND (C.Tipe = 'L'))
    SELECT     A.GlobalAcc AS AccNo, B.Name, CASE WHEN Saldo = 'D' THEN SUM(A.TotalDebet) - SUM(A.TotalKredit) ELSE SUM(A.TotalKredit) - SUM(A.TotalDebet) 
                            END AS Total, SUM(A.Bln01) AS Bln01, SUM(A.Bln02) AS Bln02, SUM(A.Bln03) AS Bln03, SUM(A.Bln04) AS Bln04, B.GlobalAcc, C.Saldo
     FROM         CTE AS A LEFT OUTER JOIN
                            TMGLACCNO AS B ON A.GlobalAcc = B.AccNo LEFT OUTER JOIN
                            TMGLTYPE AS C ON C.GLTypeCode = B.GLTypeCode
     WHERE     (A.GlobalAcc <> '') AND (A.TotalDebet <> 0) OR
                            (A.GlobalAcc <> '') AND (A.TotalKredit <> 0)
     GROUP BY A.GlobalAcc, B.Name, B.GlobalAcc, C.Saldo

Open in new window

0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
awking00,
   Oops.. Yes it needs to be a FULL OUTER JOIN instead of INNER JOIN.
0
 
emi_sastraAuthor Commented:
Hi awking00,

Any suggestion from my code ?

Thank you.
0
 
emi_sastraAuthor Commented:
Please my code and the result.

Why it produces so many null?

Thank you.
SELECT     t1.AccNo, t1.Debet08, t1.Kredit08
FROM         THLEDGER2007 AS t1 FULL OUTER JOIN
                      THLEDGER2008 AS t2 ON t1.AccNo = t2.AccNo
ORDER BY t1.AccNo

Open in new window

COMBINE-RESULT.xls
0
 
emi_sastraAuthor Commented:
Please also see the comparison.

The query below has missing some AccNo.

SELECT     t1.AccNo, t1.Debet08, t1.Kredit08
FROM         THLEDGER2007 AS t1 FULL OUTER JOIN
                      THLEDGER2008 AS t2 ON t1.AccNo = t2.AccNo
ORDER BY t1.AccNo

Thank you.
COMPARISON-RESULT.xls
0
 
emi_sastraAuthor Commented:
Sorry, should be:

SELECT     t1.AccNo
FROM         THLEDGER2007 AS t1 FULL OUTER JOIN
                      THLEDGER2008 AS t2 ON t1.AccNo = t2.AccNo
WHERE     (t1.AccNo IS NOT NULL)

Thank you.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
That's how full outer join works..

Full outer join brings the following records in your result set

1. Common records between both tables.
2. Records present in table1 and not in table2 will have Null values for the second table columns.
3. Records present in table2 and not in table1 will have Null values for the first table columns.

Instead of this,

SELECT     t1.AccNo
FROM         THLEDGER2007 AS t1 FULL OUTER JOIN
                      THLEDGER2008 AS t2 ON t1.AccNo = t2.AccNo
WHERE     (t1.AccNo IS NOT NULL)

you can use

SELECT     t1.AccNo
FROM         THLEDGER2007 AS t1 LEFT OUTER JOIN
                      THLEDGER2008 AS t2 ON t1.AccNo = t2.AccNo

Hope this helps
0
 
emi_sastraAuthor Commented:
Hi rrjegan17,

Using LEFT JOIN will not solve the problem.

Thank you.
0
 
emi_sastraAuthor Commented:
Please see the "COMPARISON RESULT".

Thank you.
0
 
awking00Commented:
See attached.
comments.txt
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
emi_sastra,
     Can you confirm what are the records you require:

1. Records present in both 2007 and 2008.
2. Records present in 2007 and not in 2008. (If required kindly tell me how those records to be presented from query).
3. Records present in 2008 and not in 2007. (If required kindly tell me how those records to be presented from query).

Based on your confirmation, I can provide you the appropriate query.
0
 
awking00Commented:
I just realized in my comments that I gave you Oracle syntax, but I'm not sure which dbms you're actually using. Most dbms have a function like nvl that says if the first expression equates to null then take the second expression.
0
 
emi_sastraAuthor Commented:
Hi rrjegan17,

Point 1 base on user selection  :

From Month, To Month, From Year, To Year.

Thank you.

0
 
emi_sastraAuthor Commented:
HI awking00,

I use Ms SQL, if you notice the tags.

Thank you.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> Point 1 base on user selection  :

Then INNER JOIN in my earlier comment 24783224 should help you out.
Kindly fill in for the rest of the columns.
0
 
emi_sastraAuthor Commented:
Hi rrjegan17,

I have tried it, miss below AccNo.

1311010105
1311010105
1311010106
1311010106

610203     
610412

Thank you.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Kindly confirm whether you have records are in proper manner for these values. I suspect there's something wrong with data like additional space like that in these values.

1311010105
1311010105
1311010106
1311010106

and the below records are not found in both table as in your attached sheet.

610203    
610412
0
 
emi_sastraAuthor Commented:
1311010105 (ROWS 14)  AND 1311010106  (ROWS 15) ON TABLE2008 ONLY.

610203 (ROWS 24)  AND 610412 (ROWS 34) ON TABLE2007 ONLY.

Please see it again on the sheet.

Thank you.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
As we use INNER JOIN , it will fetch records that are present in both tables only.
And that's why I asked this option earlier.

>> 1. Records present in both 2007 and 2008.

Hence it is correct right.
0
 
emi_sastraAuthor Commented:
I think I miss understood your questioned.

I want all of them appear on the query result.

For example :

Table Structure for Both Table:

1. AccNo.
2. Month01
3. Month02
4. ..
5. Month12

TableA :

AccNo   Month01 to Month10    Month11   Month12
A                          0                   1               2

TableB:

AccNo   Month01   Month02
A          3               4
B          1               2

Result Query:

I just want to query AccNo, Month11, Month12 from TableA and
AccNo, Month02, Month02 From TableB.

AccNo  Month01   Month02   Month03   Month04
A         1               2               3               4
B         0               0               1               2

Hope this is clear enough.

As I mentioned in my question description, this is a very challenging query if not possible.

Thank you.
0
 
awking00Commented:
Now that I know the equivalent for MS SQL, see attached.
query.txt
0
 
emi_sastraAuthor Commented:
Hi awking00,

It is so simple.
I never thought it could be so simple.

I have not tried it yet on my real table, but I am sure it will work.

Thank you very much for your help.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
emi_sastra,
    Saw your comment no 24816507 just now.
If this was provided earlier, I would have been more clear in my understanding about your requirement and glad to have your problem resolved by now.
0
 
emi_sastraAuthor Commented:
Yes, it is hard enough to solve the problem even though the solution could be simple.
How many attempt to solve this problem.
The question is quite obvious, that's why awking00 provide FULL JOIN NOT LEFT JOIN.

The first try produce null. You didn't notice that the null actually should have value on it.
But awking00 noticed it, then the last code awking00 provided using another select to get the result.

select isnull(t7acc,t8acc) as AccNo --> Avoid null just get null value from one of those tables.

t7acc is AccNo From THLEDGER2007
t8acc is AccNo From THLEDGER2008

select t7.accno as t7acc
       ,t8.accno as t8acc

The above code solve the null problem.

Anyway, I should thank you for your kindly help trying to solve my problem.

Thank you.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 20
  • 12
  • 7
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now