?
Solved

How to join two Oracle Query ?

Posted on 2013-05-23
11
Medium Priority
?
543 Views
Last Modified: 2013-05-25
Hi ,

I want to join two tables. Each individual query return correct values , but when i'm joining the two queryies return more values.Please find the attached two queries and help me to join two queries.
Query.doc
0
Comment
Question by:Rose_Taylor
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 13

Accepted Solution

by:
Alexander Eßer [Alex140181] earned 300 total points
ID: 39192640
Just to clarify in case other experts want to participate: I formatted these 2 SQL statements. Please correct me if I'm wrong.

select prod.mat_nbr as productNbr,
       part.prod_full_descr as productDescr,
       part.part_id as partID,
       part.prod_full_descr as productDescr,
       c.part_id as prereqs_part_id,
       c.parent_part_id as prereq_parent_part_id,
       prepart.prod_full_descr as prereq_prod_full_descr
  from Product prod,
       Publish pub,
       Part part,
       component c,
       part prepart
 where pub.product_Key = prod.product_Key
   and part.part_nbr = prod.mat_nbr
   and (part.part_id = c.parent_part_id(+) and part.fiscal_quarter = c.fiscal_quarter(+))
   and (prepart.fiscal_quarter = '2014-2' and prepart.part_id = c.part_id)
   and part.fiscal_quarter = '2014-2'
   and part.part_nbr = '212210';

select prod.mat_nbr as productNbr,
       part.prod_full_descr as productDescr,
       part.part_id as partID,
       part.prod_full_descr as productDescr,
       c.contact_function as prereqs_part_id,
       c.contact_name as prereq_parent_part_id
  from Product prod,
       Publish pub,
       Part part,
       contact c,
       part prepart
 where pub.product_Key = prod.product_Key
   and part.part_nbr = prod.mat_nbr
   and (part.part_id = c.part_id(+) and part.fiscal_quarter = c.fiscal_quarter(+))
   and (prepart.fiscal_quarter = '2014-2' and prepart.part_id = c.part_id)
   and part.fiscal_quarter = '2014-2'
   and part.part_nbr = '212210';

Open in new window


So you want to join these 2 SQLs using a join?!
Maybe some (test) data or at least the table definitions would be fine ;-)
0
 

Author Comment

by:Rose_Taylor
ID: 39192670
Yes , I want to join the these query using join.
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39192703
You cannot do the join using UNION since the columns do not match. What exactly do you want to do? And what about the table defs, maybe with some test data?
0
What is a Denial of Service (DoS)?

A DoS is a malicious attempt to prevent the normal operation of a computer system. You may frequently see the terms 'DDoS' (Distributed Denial of Service) and 'DoS' used interchangeably, but there are some subtle differences.

 

Author Comment

by:Rose_Taylor
ID: 39192717
I'm trying to join two tables like this...

select
part.prod_full_descr,
part.part_id,
part.prod_full_descr,
c.part_id,
c.parent_part_id,
prepart.prod_full_descr ,
cont.contact_function ,
cont.contact_name
FROM
Part_T part ,
component_t c ,
part_t prepart ,
contact_t cont ,
part_t cont_part ,
part_t pre_cont_part
WHERE
 part.part_id = c.parent_part_id (+)
AND part.fiscal_quarter = c.fiscal_quarter (+)
AND prepart.fiscal_quarter = '2014-2'
AND prepart.part_id = c.part_id
AND part.part_id = cont.part_id (+)
AND part.fiscal_quarter = '2014-2'
and part.part_nbr = '12224'

This above query returning more that 500 rows. but its not correct.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39192749
Please post sample data and expected results.  I'm not following what you mean by 'join'.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39192759
Forget my last post...  I think I see it.

Give me a little time.
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39192768
Agreed ;-)
In order to solve (or at least to understand) your problem, we need:
- table definitions (create table ....)
- expected results (as mentioned by slightwv)
- some test data would be nice
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 600 total points
ID: 39192782
It's hard to imagine tables and relationships w/o being able to set things up and play but here's what I have.

Honestly, I would be shocked if it works but I'm posting it anyway.

I think the issue with the join query you had is you are missing some relationships between component_t, contact_t and the rest of the tables.

select
	part.prod_full_descr,
	part.part_id,
	part.prod_full_descr,
	c.part_id,
	c.parent_part_id,
	prepart.prod_full_descr ,
	cont.contact_function ,
	cont.contact_name
FROM
	Part_T part ,
	component_t c ,
	part_t prepart ,
	contact_t cont ,
	part_t cont_part ,
	part_t pre_cont_part
WHERE 
	pub.product_Key = prod.product_Key
	AND part.part_nbr = prod.mat_nbr
	AND part.fiscal_quarter = '2014-2'
	and part.part_nbr = '212210'
	and (
		prepart.part_id = cont.part_id or
		prepart.part_id = c.part_id
	)
	AND 
	(
		(
			part.part_id = cont.parent_part_id (+) and
			part.fiscal_quarter = cont.fiscal_quarter (+)
		) 
		or 
		(
			part.part_id = c.part_id (+) and
			part.fiscal_quarter = c.fiscal_quarter (+)
		) 
	)

Open in new window

0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39192845
Sorry slightwv, but this statement will fail to execute: e.g. see table alias 'pub' or 'prod' (where are their corresponding tables)...
Nevertheless this SQL is quite confusing whether we have the table defs w/o data or not. I don't see a clear and structured statement here at all (e.g. where is the "base table" / start?). We/I use to build SQLs upon this pattern (which makes a lot of things a lot easier, even for the Optimizer):

select col1,
       col2,
       col3
  from table_3 c,
       table_2 b,
       table_1 a
 where a.prime_key = 'whatever'
   and b.fk_on_a = a.prime_key
   and c.fk_on_b = b.prime_key;

Open in new window


Maybe you should consider to redesign your statement in order to get a clear view on the purpose ;-)
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39192853
>>Sorry slightwv, but this statement will fail to execute: e.g. see table alias 'pub' or 'prod' (where are their corresponding tables)...

I used the askers first attempt as the starting point and just merged the other SQL into it.

I guess I missed some table names and aliases.  Hopefully the asker can get the idea of where I was going and correct the table names.
0
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 600 total points
ID: 39193052
There appears to be just one difference in the intended joins
upper: part_id to component parent_part_id
lower:  part_id to component part_id
so, nothing ventured - nothing gained:
SELECT
      prod.mat_nbr             AS productNbr
    , part.prod_full_descr     AS productDescr
    , part.part_id             AS partID
    , c1.part_id               AS prereqs_part_id
    , c1.parent_part_id        AS prereq_parent_part_id
    , prepart1.prod_full_descr AS prereq_prod_full_descr
    
    , c2.part_id               AS prereqs_part_id_2
    , c2.parent_part_id        AS prereq_parent_part_id_2
    , prepart2.prod_full_descr AS prereq_prod_full_descr_2
    
FROM Product prod
INNER JOIN Publish pub  ON prod.product_Key = pub.product_Key
INNER JOIN Part part    ON prod.mat_nbr = part.part_nbr

LEFT JOIN component c1  ON (part.part_id = c1.parent_part_id AND part.fiscal_quarter = c1.fiscal_quarter)
LEFT JOIN part prepart1 ON (prepart1.part_id = c1.part_id AND prepart1.fiscal_quarter = '2014-2')

LEFT JOIN component c2  ON (part.part_id = c2.part_id AND part.fiscal_quarter = c2.fiscal_quarter)
LEFT JOIN part prepart2 ON (prepart2.part_id = c2.part_id AND prepart2.fiscal_quarter = '2014-2')

WHERE part.fiscal_quarter = '2014-2' 
AND part.part_nbr = '212210';

Open in new window

0

Featured Post

Quick Start: DOCKER

Sometimes you just need a Quick Start on a topic in order to begin using it.. this is just what you need to know to get up and running with Docker!

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

752 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