Home » SQL & PL/SQL » SQL & PL/SQL » How to take data from two table.
How to take data from two table. [message #199516] Tue, 24 October 2006 23:28 Go to next message
starfruit
Messages: 8
Registered: October 2006
Location: Ha Noi
Junior Member
Hi All. Please help me to take data

I have two table one have colum and value (SEC)

SEC_CODE NAME
CP2B036400002 TP KBNN ghi so KH 364 ngay PH
CP2B018200002 TP KBNN ghi so KH 182
UB9B2-0500001 TP CQDP ghi so KH 05nam PH
NH1B0007 TP NHNN loai ghi so KH 7
CP2B0091 TREASURY NOTES
CP5A2-0500001 TP Ngoai te chung chi

that have some colum but i don't list here

and an orther table SEC_DETAIL the sample

SEC_CODE MEMBER_CODE SEC_VALUE
CP2B009100001 10201016 11
CP2B009100001 10202018 300000000000
CP2B009100001 10203021 -20000000000
CP2B009100002 10201016 -5000000000
CP2B009100002 10202018 -100000000

Now I want to take data from two table when i know SEC_CODE and MEMBER_CODE. But some MEMBER_CODE not occur in SEC_DETAIL so I want take all information in SEC and value SEC_VALUE = 0 (or some thing) if MEMBER_CODE not occur and take all information in two table if MEMBER_CODE occur.


Please help me soon
Re: How to take data from two table. [message #199522 is a reply to message #199516] Wed, 25 October 2006 00:26 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Its hard to read your post because you haven't formatted it. Re-open your old message and add [code] and [/code] tags around the data.

Also, it would be easier to understand your requirement if you posted and example of the data that you want returned from such a query, and base those results on the sample data above.

Ross Leishman
Re: How to take data from two table. [message #199535 is a reply to message #199522] Wed, 25 October 2006 01:13 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Maybe I'm wrong, but this seems to be a candidate for OUTER JOIN:
SELECT s.sec_code, d.member_code
FROM sec s, sec_detail d
WHERE s.sec_code = d.sec_code (+);
Re: How to take data from two table. [message #199563 is a reply to message #199535] Wed, 25 October 2006 02:02 Go to previous messageGo to next message
starfruit
Messages: 8
Registered: October 2006
Location: Ha Noi
Junior Member
Littlefoot wrote on Wed, 25 October 2006 01:13
Maybe I'm wrong, but this seems to be a candidate for OUTER JOIN:
SELECT s.sec_code, d.member_code
FROM sec s, sec_detail d
WHERE s.sec_code = d.sec_code (+);



Thanks for answer but in your anser is outer join I know that. So I can repost the requirement. I have two table sample. the SEC table

SEC_CODE NAME
CP2B036400002 TP KBNN ghi so KH 364 ngay PH
CP2B018200002 TP KBNN ghi so KH 182
UB9B2-0500001 TP CQDP ghi so KH 05nam PH

and the SEC_DETAIL table

SEC_CODE MEMBER_CODE SEC_VALUE
CP2B018200002 10203021 20000000000
CP2B018200002 10201016 5000000000
UB9B2-0500001 10202018 100000000


So I have a SQL
SELECT a.sec_code,a.name,b.sec_value
FROM sec a, sec_detail b
WHERE a.security_code = 'CP2B018200002'
AND a.security_code = b.security_code
And b.member_code = '50303016'

The member_code '50303016' not occur in SEC_DETAIL but I want the SQL return value sec_value = 0 and all value in sec table
sample CP2B018200002 TP KBNN ghi so KH 182 0
.With another member_code sample 10201016 the value return
CP2B018200002 TP KBNN ghi so KH 182 5000000000

Please help me
Re: How to take data from two table. [message #199571 is a reply to message #199516] Wed, 25 October 2006 02:20 Go to previous messageGo to next message
Alien
Messages: 245
Registered: June 1999
Senior Member
Still an outer join:

select s.sec_code
,      s.name
,      sd.member_code
,      case when sd.member_code is null then 0
       else sd.sec_value end s_value
from   sec s left outer join (select * from sec_detail  
                              where member_code=:1) sd
             on s.sec_code=sd.sec_code
where s.sec_code='CP2B018200002'
Re: How to take data from two table. [message #199573 is a reply to message #199571] Wed, 25 October 2006 02:34 Go to previous message
starfruit
Messages: 8
Registered: October 2006
Location: Ha Noi
Junior Member
Alien wrote on Wed, 25 October 2006 02:20
Still an outer join:

select s.sec_code
,      s.name
,      sd.member_code
,      case when sd.member_code is null then 0
       else sd.sec_value end s_value
from   sec s left outer join (select * from sec_detail  
                              where member_code=:1) sd
             on s.sec_code=sd.sec_code
where s.sec_code='CP2B018200002'



Thanks you very much
Previous Topic: Syntax help
Next Topic: Err LPX-00216
Goto Forum:
  


Current Time: Sun Dec 11 02:42:32 CST 2016

Total time taken to generate the page: 0.26408 seconds