Solved

Query by Combining 2 tables.

Posted on 2009-07-06
41
335 Views
Last Modified: 2012-05-07
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
Comment
Question by:emi_sastra
  • 20
  • 12
  • 7
  • +1
41 Comments
 

Expert Comment

by:razorBlades
ID: 24783223
what is the data type of Month1...Month12 on the table? is it a datetime field?
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24783224
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
 
LVL 1

Author Comment

by:emi_sastra
ID: 24783229
Hi rrjegan17.

Month01 to Month12 are Money Type.

Thank you.
0
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 
LVL 1

Author Comment

by:emi_sastra
ID: 24783236
One more.

Data on Table2008 may not be the same with Table2009.

Thank you.
0
 

Expert Comment

by:razorBlades
ID: 24783258
hi emi,

is rrjegan17's query satisfactory? If not can you give us an example with the data in the table?
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24783273
Can you provide some sample records for existing table and your desired result set so that it would be helpful to understand better.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 24784885
Hi All,

I am sorry, just back again.

Please see my tables, I export it into excel.

Thank you.

TABLE2007.xls
TABLE2008.xls
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24785015
Just saw your sample records.

Kindly provide the expected result set to help you out better.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 24785361
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
 
LVL 32

Expert Comment

by:awking00
ID: 24785480
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24785501
My query as per comment 24783224 would provide you exactly what you need.
have you tried that one out.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 24785527
Hi awking00,

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

Thank you.
0
 
LVL 32

Expert Comment

by:awking00
ID: 24785541
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
 
LVL 1

Author Comment

by:emi_sastra
ID: 24785581
Hi rrjegan17,

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

Thank you.
0
 
LVL 32

Expert Comment

by:awking00
ID: 24785582
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24785595
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
 
LVL 32

Expert Comment

by:awking00
ID: 24785702
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
 
LVL 1

Author Comment

by:emi_sastra
ID: 24785719
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24785721
awking00,
   Oops.. Yes it needs to be a FULL OUTER JOIN instead of INNER JOIN.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 24796675
Hi awking00,

Any suggestion from my code ?

Thank you.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 24796764
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
 
LVL 1

Author Comment

by:emi_sastra
ID: 24796955
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
 
LVL 1

Author Comment

by:emi_sastra
ID: 24796974
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24799771
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
 
LVL 1

Author Comment

by:emi_sastra
ID: 24800002
Hi rrjegan17,

Using LEFT JOIN will not solve the problem.

Thank you.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 24800006
Please see the "COMPARISON RESULT".

Thank you.
0
 
LVL 32

Expert Comment

by:awking00
ID: 24802704
See attached.
comments.txt
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24811988
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
 
LVL 32

Expert Comment

by:awking00
ID: 24812652
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
 
LVL 1

Author Comment

by:emi_sastra
ID: 24815026
Hi rrjegan17,

Point 1 base on user selection  :

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

Thank you.

0
 
LVL 1

Author Comment

by:emi_sastra
ID: 24815035
HI awking00,

I use Ms SQL, if you notice the tags.

Thank you.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24815837
>> 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
 
LVL 1

Author Comment

by:emi_sastra
ID: 24816021
Hi rrjegan17,

I have tried it, miss below AccNo.

1311010105
1311010105
1311010106
1311010106

610203     
610412

Thank you.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24816167
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
 
LVL 1

Author Comment

by:emi_sastra
ID: 24816298
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24816325
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
 
LVL 1

Author Comment

by:emi_sastra
ID: 24816507
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
 
LVL 32

Accepted Solution

by:
awking00 earned 500 total points
ID: 24816983
Now that I know the equivalent for MS SQL, see attached.
query.txt
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 24818783
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24819922
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
 
LVL 1

Author Comment

by:emi_sastra
ID: 24820018
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question