Home » SQL & PL/SQL » SQL & PL/SQL » Table Joining (Oracle 9i)
Table Joining [message #334047] Tue, 15 July 2008 05:43 Go to next message
freelanxer
Messages: 41
Registered: March 2005
Location: Philippines
Member
Hi everyone!

I have a problem in joining my results from my table.

Let's say that my data is like:
REPORT_TBL								
	COMP_ID	SECTION_CD	HALF_YEAR_TYPE	YEAR	MONTH	PROJ_CD	INDEX_CD	HRS
	0010	0410		2008K		2008	1	\SG035	1		5
	0010	0410		2008K		2008	1	\SG035	1		3
	0010	0410		2008K		2008	2	\SG035	1		6
	0010	0410		2008K		2008	2	\SG035	1		4
	0010	0410		2008K		2008	2	\SG035	1		9
	0010	0410		2008K		2008	2	\SG035	1		9
	0010	0410		2008K		2008	2	\SG035	1		6
	0010	0410		2008K		2008	3	\SG035	1		5
	0010	0410		2008K		2008	3	\SG035	1		5
	0010	0410		2008K		2008	3	\SG035	1		6
	0010	0410		2008K		2008	3	\SG035	1		4


And my expected result should be:
	MONTH	HRS	MONTH	HRS	MONTH	HRS
	1	5	2	6	3	5
	1	3	2	4	3	5
			2	9	3	6
			2	9	3	4
			2	6		



I tried to solve it and come up with this SQL:

SELECT *
FROM   (SELECT COMP_ID,
               SECTION_CD,
               HALF_YEAR_TYPE,
               YEAR,
               PROJ_CD,
               INDEX_CD,
               HRS
        FROM   REPORT_TBL
        WHERE  COMP_ID = '0010'
               AND SECTION_CD = '0410'
               AND HALF_YEAR_TYPE = '2008K'
               AND MONTH = '1') JAN,
       (SELECT COMP_ID,
               SECTION_CD,
               HALF_YEAR_TYPE,
               YEAR,
               PROJ_CD,
               INDEX_CD,
               HRS
        FROM   REPORT_TBL
        WHERE  COMP_ID = '0010'
               AND SECTION_CD = '0410'
               AND HALF_YEAR_TYPE = '2008K'
               AND MONTH = '2') FEB,
       (SELECT COMP_ID,
               SECTION_CD,
               HALF_YEAR_TYPE,
               YEAR,
               PROJ_CD,
               INDEX_CD,
               HRS
        FROM   REPORT_TBL
        WHERE  COMP_ID = '0010'
               AND SECTION_CD = '0410'
               AND HALF_YEAR_TYPE = '2008K'
               AND MONTH = '3') MAR
WHERE  JAN.COMP_ID = FEB.COMP_ID (+) 
       AND JAN.SECTION_CD = FEB.SECTION_CD (+) 
       AND JAN.HALF_YEAR_TYPE = FEB.HALF_YEAR_TYPE (+) 
       AND JAN.YEAR = FEB.YEAR (+) 
       AND JAN.PROJ_CD = FEB.PROJ_CD (+) 
       AND JAN.INDEX_CD = FEB.INDEX_CD (+) 
       AND JAN.COMP_ID = MAR.COMP_ID (+) 
       AND JAN.SECTION_CD = MAR.SECTION_CD (+) 
       AND JAN.HALF_YEAR_TYPE = MAR.HALF_YEAR_TYPE (+) 
       AND JAN.YEAR = MAR.YEAR (+) 
       AND JAN.PROJ_CD = MAR.PROJ_CD (+) 
       AND JAN.INDEX_CD = MAR.INDEX_CD (+) 


But the problem is that the result for each "MONTH" varies from one another.
For example, the "MONTH = 1" can have no data but "MONTH = 2" and "MONTH = 3" will acquire records and vice versa.

Having this condition, I cannot determine what "sub select" table that I will use as a Main Table.

Can someone help me? Sad
Re: Table Joining [message #334049 is a reply to message #334047] Tue, 15 July 2008 05:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Search for "calendar" and join with it.

Regards
Michel
Re: Table Joining [message #334061 is a reply to message #334049] Tue, 15 July 2008 06:25 Go to previous messageGo to next message
freelanxer
Messages: 41
Registered: March 2005
Location: Philippines
Member
Thank's Michel for the quick reply! Smile

I am not familiar with the "calendar" concept but from what I've search, I think that you are trying to tell me to create a "calendar query" then outer join it to our existing query, right?

But the problem is that the total number of results still depends on my query.

The query depends on the subquery with the most number of results.

Please correct me if my assumption is wrong.
Thanks! Smile
Re: Table Joining [message #334094 is a reply to message #334061] Tue, 15 July 2008 07:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
But the problem is that the total number of results still depends on my query.

The problem is that I don't understand the problem.

Quote:
The query depends on the subquery with the most number of results.

Question

I don't see the relation between your query and your result.

Regards
Michel
Re: Table Joining [message #334124 is a reply to message #334094] Tue, 15 July 2008 08:37 Go to previous messageGo to next message
freelanxer
Messages: 41
Registered: March 2005
Location: Philippines
Member

I see. I'm very sorry if my question has been vague.


From this record:

REPORT_TBL								
	COMP_ID	SECTION_CD	HALF_YEAR_TYPE	YEAR	MONTH	PROJ_CD	INDEX_CD	HRS
	0010	0410		2008K		2008	1	\SG035	1		5
	0010	0410		2008K		2008	1	\SG035	1		3
	0010	0410		2008K		2008	2	\SG035	1		6
	0010	0410		2008K		2008	2	\SG035	1		4
	0010	0410		2008K		2008	2	\SG035	1		9
	0010	0410		2008K		2008	2	\SG035	1		9
	0010	0410		2008K		2008	2	\SG035	1		6
	0010	0410		2008K		2008	3	\SG035	1		5
	0010	0410		2008K		2008	3	\SG035	1		5
	0010	0410		2008K		2008	3	\SG035	1		6
	0010	0410		2008K		2008	3	\SG035	1		4


I need to extract the hrs for each "MONTH"

For january:
        SELECT MONTH MONTH_1, HRS HRS_1
        FROM   REPORT_TBL
        WHERE  COMP_ID = '0010'
               AND SECTION_CD = '0410'
               AND HALF_YEAR_TYPE = '2008K'
               AND MONTH = '1'

	MONTH_1	HRS_1
	1	5	
	1	3	


For february:
        SELECT MONTH MONTH_2, HRS HRS_2
        FROM   REPORT_TBL
        WHERE  COMP_ID = '0010'
               AND SECTION_CD = '0410'
               AND HALF_YEAR_TYPE = '2008K'
               AND MONTH = '2'

	MONTH_2	HRS_2
	2	6	
	2	4	
	2	9	
	2	9	
	2	6		


For march:
        SELECT MONTH MONTH_3, HRS HRS_3
        FROM   REPORT_TBL
        WHERE  COMP_ID = '0010'
               AND SECTION_CD = '0410'
               AND HALF_YEAR_TYPE = '2008K'
               AND MONTH = '3'

	MONTH_3	HRS_3
	3	5
	3	5
	3	6
	3	4


Then Join them all:

Expected Result:

	MONTH_1	HRS_1	MONTH_2	HRS_2	MONTH_3	HRS_3
	1	5	2	6	3	5
	1	3	2	4	3	5
			2	9	3	6
			2	9	3	4
			2	6		


The problem is that there is no Main Table.
The total number of rows depends on the select which have the most number of rows.

In our case, february(MONTH = '2'), is our main table here, and the other tables will be "OUTER JOIN".
This is also applicable with january(MONTH = '1') and march(MONTH = '3')


Please tell me if my question is still unclear.
Thanks in advance!
Re: Table Joining [message #334139 is a reply to message #334124] Tue, 15 July 2008 09:11 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Add rownum into each (sub)query and join on rownum with a full outer join.
Re: Table Joining [message #334218 is a reply to message #334139] Tue, 15 July 2008 22:17 Go to previous messageGo to next message
freelanxer
Messages: 41
Registered: March 2005
Location: Philippines
Member


Yes, it works!
Thanks pablolee for the tip! Smile
Re: Table Joining [message #334279 is a reply to message #334218] Wed, 16 July 2008 01:20 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
To assist others who may be following (or may read this thread in the future) how about you post your query.
Re: Table Joining [message #334330 is a reply to message #334047] Wed, 16 July 2008 03:58 Go to previous messageGo to next message
freelanxer
Messages: 41
Registered: March 2005
Location: Philippines
Member
Ok. Cool

Here is my query:

SELECT *
FROM   (SELECT COMP_ID,
               SECTION_CD,
               HALF_YEAR_TYPE,
               YEAR,
               PROJ_CD,
               INDEX_CD,
               HRS
        FROM   REPORT_TBL
        WHERE  COMP_ID = '0010'
               AND SECTION_CD = '0410'
               AND HALF_YEAR_TYPE = '2008K'
               AND MONTH = '1') JAN
       FULL OUTER JOIN (SELECT COMP_ID,
                               SECTION_CD,
                               HALF_YEAR_TYPE,
                               YEAR,
                               PROJ_CD,
                               INDEX_CD,
                               HRS
                        FROM   REPORT_TBL
                        WHERE  COMP_ID = '0010'
                               AND SECTION_CD = '0410'
                               AND HALF_YEAR_TYPE = '2008K'
                               AND MONTH = '2') FEB
         ON JAN.COMP_ID = FEB.COMP_ID
            AND JAN.SECTION_CD = FEB.SECTION_CD
            AND JAN.HALF_YEAR_TYPE = FEB.HALF_YEAR_TYPE
            AND JAN.YEAR = FEB.YEAR
            AND JAN.PROJ_CD = FEB.PROJ_CD
            AND JAN.INDEX_CD = FEB.INDEX_CD
       FULL OUTER JOIN (SELECT COMP_ID,
                               SECTION_CD,
                               HALF_YEAR_TYPE,
                               YEAR,
                               PROJ_CD,
                               INDEX_CD,
                               HRS
                        FROM   REPORT_TBL
                        WHERE  COMP_ID = '0010'
                               AND SECTION_CD = '0410'
                               AND HALF_YEAR_TYPE = '2008K'
                               AND MONTH = '3') MAR
         ON JAN.COMP_ID = MAR.COMP_ID
            AND JAN.SECTION_CD = MAR.SECTION_CD
            AND JAN.HALF_YEAR_TYPE = MAR.HALF_YEAR_TYPE
            AND JAN.YEAR = MAR.YEAR
            AND JAN.PROJ_CD = MAR.PROJ_CD
            AND JAN.INDEX_CD = MAR.INDEX_CD




I got the info from:
http://www.oreillynet.com/pub/a/network/2002/04/23/fulljoin.html

I hope that this could help.
Thanks pablolee! Smile
Re: Table Joining [message #334336 is a reply to message #334330] Wed, 16 July 2008 04:15 Go to previous message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Hmmm, that query would appear to provide results (based on your sample data) thus:
MONTH  HRS    MONTH    HRS    MONTH    HRS     
------------------------------------------
1       5       2       6       3       5       
1       3       2       6       3       5       
1       5       2       9       3       5       
1       3       2       9       3       5       
1       5       2       9       3       5       
1       3       2       9       3       5       
1       5       2       4       3       5       
1       3       2       4       3       5       
1       5       2       6       3       5       
1       3       2       6       3       5       
1       5       2       6       3       5       
1       3       2       6       3       5       
1       5       2       9       3       5       
1       3       2       9       3       5       
1       5       2       9       3       5       
1       3       2       9       3       5       
1       5       2       4       3       5       
1       3       2       4       3       5       
1       5       2       6       3       5       
1       3       2       6       3       5       
1       5       2       6       3       6       
1       3       2       6       3       6       
1       5       2       9       3       6       
1       3       2       9       3       6       
1       5       2       9       3       6       
1       3       2       9       3       6       
1       5       2       4       3       6       
1       3       2       4       3       6       
1       5       2       6       3       6       
1       3       2       6       3       6       
1       5       2       6       3       4       
1       3       2       6       3       4       
1       5       2       9       3       4       
1       3       2       9       3       4       
1       5       2       9       3       4       
1       3       2       9       3       4       
1       5       2       4       3       4       
1       3       2       4       3       4       
1       5       2       6       3       4       
1       3       2       6       3       4       

i.e. not what you asked for. Care to elaborate?
Previous Topic: how to find nth maximum salary from a table
Next Topic: parameter Package IF THEN Else
Goto Forum:
  


Current Time: Sat Dec 10 14:42:03 CST 2016

Total time taken to generate the page: 0.04675 seconds