Solved

How to join two Oracle Query ?

Posted on 2013-05-23
11
527 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
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 13

Accepted Solution

by:
Alexander Eßer [Alex140181] earned 100 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
 

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 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39192749
Please post sample data and expected results.  I'm not following what you mean by 'join'.
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 76

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 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 200 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 76

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 48

Assisted Solution

by:PortletPaul
PortletPaul earned 200 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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
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 video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

708 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

19 Experts available now in Live!

Get 1:1 Help Now