Solved

How to join two Oracle Query ?

Posted on 2013-05-23
11
538 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 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
Stressed Out?

Watch some penguins on the livecam!

 

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 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 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 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

Increase Agility with Enabled Toolchains

Connect your existing build, deployment, management, monitoring, and collaboration platforms. From Puppet to Chef, HipChat to Slack, ServiceNow to JIRA, Splunk to New Relic and beyond, hand off data between systems to engage the right people.

Connect with xMatters.

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

691 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