Home » SQL & PL/SQL » SQL & PL/SQL » F1!!! F1!!! F1!!!!!
F1!!! F1!!! F1!!!!! [message #35768] Sat, 13 October 2001 03:30 Go to next message
arbind
Messages: 1
Registered: October 2001
Junior Member
here is the 2 table that i created ....

1
create table project_master(
proj_code varchar2(3),
proj_desc varchar2(255));

2
create table project_detail(
proj_code varchar2(3),
proj_sno number,
proj_entry date,
ref_cost number,
proj_tax number,
total_cost number);



here proj_code of proj_master is a primary key where as proj_code in project_detail is a foreign key ...
now i need to get proj_code , max(proj_sno) of the proj_code ,total_cost of the table PROJECT_DETAIL and proj_desc of PROJECT_MASTER to the third table so that i get the last proj_sno of the proj_code and its proj_desc from PROJECT_MASTER to the third table
so that in the third table i have the proj_code its description, its last proj_sno ,and total_cost .HOw do i do that ???
i have written the code for just one table but when i try to get the proj_desc from the another table it doesnt work

select proj_code,max(proj_sno) from project_detail group by proj_code

whgat do i need to do please help me thank you !!!

----------------------------------------------------------------------
Re: F1!!! F1!!! F1!!!!! [message #35769 is a reply to message #35768] Sat, 13 October 2001 10:51 Go to previous message
Brian
Messages: 38
Registered: October 1999
Member
Try this: I am sure I have the syntax correct. If not that is the concept. Standard disclaimer: Make sure you do some testing to validate any code you write.

select pm.proj_code,
pm.proj_desc,
max(pd.sno),
sum(pd.total_cost)
from project_master pm, project_detail pd,
where pd.proj_code = pm.proj_code
group by pm.proj_code, pm.proj_desc

----------------------------------------------------------------------
Previous Topic: if then elseif block failing
Next Topic: any ideas
Goto Forum:
  


Current Time: Sat May 25 02:49:53 CDT 2024