Home » SQL & PL/SQL » SQL & PL/SQL » join
join [message #220815] Thu, 22 February 2007 04:41 Go to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
hi
where am i going wrong this query gives me more records has it should give me in table VIEW_M_BUSINESS_ENTITY
i have
North America=5 records
North America - West=14 records
Southland=4 records
Alamitos=1 records
what join should i use
select PC.AMT,p.PK_proj_id,p.FK_OU_ID_REGION,p.FK_OU_ID_BUSINESS,p.FK_OU_ID_PLANT,p.FK_OU_ID_PRESIDENT,v.pk_ou_id,v.name,v.entity_type
from proj p,VIEW_M_BUSINESS_ENTITY v,proj_alt pa,proj_alt_cf PC
where
v.name in ('North America','North America - West','Southland','Alamitos')
and
p.FK_OU_ID_REGION=v.pk_ou_id
or p.FK_OU_ID_BUSINESS=v.pk_ou_id
or p.FK_OU_ID_PLANT=v.pk_ou_id
or p.FK_OU_ID_PRESIDENT=v.pk_ou_id
or PA.PK_PROJ_ALT_ID=PC.FK_PROJ_ALT_ID
thanxs

[Updated on: Thu, 22 February 2007 04:43]

Report message to a moderator

Re: join [message #220833 is a reply to message #220815] Thu, 22 February 2007 05:40 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Why don't you follow your previous post?
Now you join (proj with VIEW_M_BUSINESS_ENTITY) and (proj_alt with proj_alt_cf), so you should add the condition to join these two table groups.
Re: join [message #220834 is a reply to message #220815] Thu, 22 February 2007 05:40 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
In your query, you're not joing PA and PC with P and V. Furthermore, you use OR for the join between PA and PC, where I would guess this would have to be an AND.

You should explain to us what the relationship between your tables and view is, but a wild guess would be that you want:
SELECT pc.amt
      ,p.pk_proj_id
      ,p.fk_ou_id_region
      ,p.fk_ou_id_business
      ,p.fk_ou_id_plant
      ,p.fk_ou_id_president
      ,v.pk_ou_id
      ,v.NAME
      ,v.entity_type
FROM   proj                   p
      ,view_m_business_entity v
      ,proj_alt               pa
      ,proj_alt_cf            pc
WHERE  v.NAME IN
       ('North America', 'North America - West', 'Southland', 'Alamitos')
AND    (p.fk_ou_id_region = v.pk_ou_id 
       OR p.fk_ou_id_business = v.pk_ou_id 
       OR p.fk_ou_id_plant = v.pk_ou_id 
       OR p.fk_ou_id_president = v.pk_ou_id)
AND    pa.pk_proj_alt_id = pc.fk_proj_alt_id 
AND    <join PA or PC to P here >
Re: join [message #221252 is a reply to message #220834] Mon, 26 February 2007 00:20 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
SELECT pc.amt
,p.pk_proj_id
,p.fk_ou_id_region
,p.fk_ou_id_business
,p.fk_ou_id_plant
,p.fk_ou_id_president
,v.pk_ou_id
,v.NAME
,v.entity_type
FROM proj p
,view_m_business_entity v
,proj_alt pa
,proj_alt_cf pc
WHERE v.NAME IN
('North America', 'North America - West', 'Southland', 'Alamitos')
AND p.fk_ou_id_region = v.pk_ou_id
AND p.fk_ou_id_business = v.pk_ou_id
AND p.fk_ou_id_plant = v.pk_ou_id
AND p.fk_ou_id_president = v.pk_ou_id
AND pa.pk_proj_alt_id = pc.fk_proj_alt_id
AND PA.FK_PROJ_ID=P.PK_PROJ_ID

I DO HAVE RECORD FOR ('North America', 'North America - West', 'Southland', 'Alamitos') (5,14,4,1)
BUT I GET OUT PUT NO ROWS
do i need to change the join condition

[Updated on: Mon, 26 February 2007 00:21]

Report message to a moderator

Re: join [message #221254 is a reply to message #221252] Mon, 26 February 2007 00:24 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You come here and ask for help, then skooman offers you a solution. You come back with a completely different query and ask how that is possible.
Look at your where-clause:
AND p.fk_ou_id_region = v.pk_ou_id
AND p.fk_ou_id_business = v.pk_ou_id
AND p.fk_ou_id_plant = v.pk_ou_id
AND p.fk_ou_id_president = v.pk_ou_id

How can v.pk_ou_id ever be equal to all those columns? Only if all those columns contain the same value.
Previous Topic: please advice me how to apply decode for two or more column (pivot table)
Next Topic: splitting table_name and column_name from a string
Goto Forum:
  


Current Time: Mon Dec 05 19:11:58 CST 2016

Total time taken to generate the page: 0.15184 seconds