Link to home
Create AccountLog in
Avatar of Rose_Taylor
Rose_TaylorFlag for United States of America

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of Alex [***Alex140181***]
Alex [***Alex140181***]
Flag of Germany image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Rose_Taylor

ASKER

Yes , I want to join the these query using join.
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?
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.
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Please post sample data and expected results.  I'm not following what you mean by 'join'.
Forget my last post...  I think I see it.

Give me a little time.
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
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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 ;-)
>>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.
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account