Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 556
  • Last Modified:

How to join two Oracle Query ?

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
Rose_Taylor
Asked:
Rose_Taylor
  • 4
  • 4
  • 2
  • +1
3 Solutions
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
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
 
Rose_TaylorAuthor Commented:
Yes , I want to join the these query using join.
0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Rose_TaylorAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
Please post sample data and expected results.  I'm not following what you mean by 'join'.
0
 
slightwv (䄆 Netminder) Commented:
Forget my last post...  I think I see it.

Give me a little time.
0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
PortletPaulCommented:
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now