| Re: COMPLEX SQL QUERY - HELP REQUIRED [message #374572] |
Tue, 19 June 2001 09:29 |
Jon
Messages: 483
Registered: May 2001
|
Senior Member |
|
|
I don't know why you wouldn't also want to retrieve 123,b,ADH roof. Didn't you say you wanted everything from 123? Anyway, here is my solution:
drop table quote;
create table quote (quote_id number);
insert into quote values(123);
insert into quote values(456);
insert into quote values(789);
insert into quote values(689);
--
drop table quote_section_xref;
create table quote_section_xref (quote_id number, section_id varchar2(1), roof_system_id number);
insert into quote_section_xref values(123,'a',1200);
insert into quote_section_xref values(123,'b',1200);
insert into quote_section_xref values(123,'c',2200);
insert into quote_section_xref values(123,'d',1400);
insert into quote_section_xref values(456,'a',1600);
insert into quote_section_xref values(456,'b',1400);
insert into quote_section_xref values(789,'a',2200);
insert into quote_section_xref values(689,'a',1200);
--
drop table roof_system;
create table roof_system (roof_system_id number, long_name varchar2(60),roof_system_type number);
insert into roof_system values (1200,'ADH roof',1);
insert into roof_system values (1400,'BIT roof',2);
insert into roof_system values (1600,'CIT roof',3);
insert into roof_system values (2200,'AMT roof',4);
--
select quote_id, section_id, long_name
from quote_section_xref qsx, roof_system rs
where qsx.roof_system_id = rs.roof_system_id
and quote_id in
(select distinct quote_id from quote_section_xref
where roof_system_id
in
(select roof_system_id from roof_system
where roof_system_type = 1));
QUOTE_ID S LONG_NAME
--------- - -----------
123 a ADH roof
123 b ADH roof
123 c AMT roof
123 d BIT roof
689 a ADH roof
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
