Solved

Query by Combining 2 tables.

Posted on 2009-07-06
41
312 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
Comment Utility
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
Comment Utility
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
Comment Utility
Hi rrjegan17.

Month01 to Month12 are Money Type.

Thank you.
0
 
LVL 1

Author Comment

by:emi_sastra
Comment Utility
One more.

Data on Table2008 may not be the same with Table2009.

Thank you.
0
 

Expert Comment

by:razorBlades
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Just saw your sample records.

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

Author Comment

by:emi_sastra
Comment Utility
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 31

Expert Comment

by:awking00
Comment Utility
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
Comment Utility
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
Comment Utility
Hi awking00,

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

Thank you.
0
 
LVL 31

Expert Comment

by:awking00
Comment Utility
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
Comment Utility
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 31

Expert Comment

by:awking00
Comment Utility
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
Comment Utility
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 31

Expert Comment

by:awking00
Comment Utility
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
Comment Utility
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
Comment Utility
awking00,
   Oops.. Yes it needs to be a FULL OUTER JOIN instead of INNER JOIN.
0
 
LVL 1

Author Comment

by:emi_sastra
Comment Utility
Hi awking00,

Any suggestion from my code ?

Thank you.
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 1

Author Comment

by:emi_sastra
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Hi rrjegan17,

Using LEFT JOIN will not solve the problem.

Thank you.
0
 
LVL 1

Author Comment

by:emi_sastra
Comment Utility
Please see the "COMPARISON RESULT".

Thank you.
0
 
LVL 31

Expert Comment

by:awking00
Comment Utility
See attached.
comments.txt
0
 
LVL 57

Expert Comment

by:Raja Jegan R
Comment Utility
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 31

Expert Comment

by:awking00
Comment Utility
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
Comment Utility
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
Comment Utility
HI awking00,

I use Ms SQL, if you notice the tags.

Thank you.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
Comment Utility
>> 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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 31

Accepted Solution

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

Author Comment

by:emi_sastra
Comment Utility
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
Comment Utility
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
Comment Utility
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

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 …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now