Home » RDBMS Server » Server Administration » Complex Query!!!!!!!!!!!!!!!!!!!!!!!!!
Complex Query!!!!!!!!!!!!!!!!!!!!!!!!! [message #374540] |
Mon, 18 June 2001 10:01 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
MK
Messages: 31 Registered: June 2001
|
Member |
|
|
I have 3 tables with the following type of data.
Table quote(quote_id)
123
456
789
689
Table quote_section_xref(quote_id,section_id,roof_system_id)
123,a,1200
123,b,1200
123,c,2200
123,d,1400
456,a,1600
456,b,1400
789,a,2200
689,a,1200
Table roof_system(roof_system_id, long_name, roof_system_type)
1200,'ADH roof',1
1400,'BIT roof',2
1600,'CIT roof',3
2200,'AMT roof',4
Need an sql query which will fetch the following when we select a particular roof_system_type.
Eg if I select roof_system_type = 1 (which has roof_sytem_id = 1200 - Get all records with roof_system_id = 1200
and other records of the quote 123 since it has one occurance of the
roof_syetm_id = 1200)
quote_id, section_id,long_name
123, a, ADH roof
123, c, AMT roof
123, d, BIT roof
689, a, ADH Roof
There are 4 other joins that i am performing to the above 3 tables. I am clear about them.
Urgent Help required !!!!
|
|
|
|
|
Re: Complex Query!!!!!!!!!!!!!!!!!!!!!!!!! [message #374546 is a reply to message #374540] |
Mon, 18 June 2001 10:43 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
Brent Stayer
Messages: 6 Registered: October 2000
|
Junior Member |
|
|
I would try something like:
select quote_section_xref.quote_id,quote_section_xref.section_id,roo_system.long_name
from
(select distinct quote_section_xref.quote_id as quote from quote_section_xref
where roof_system.roof_system_type = '1' and
roof_system.roof_system_id=quote_section_xref.roof_system_id)quote_list
where quote_section_xref.quote_id=quote_list.quote and
quote_section_xref.roof_system_id=roof_system.roof_system_id;
|
|
|
Goto Forum:
Current Time: Thu Sep 12 04:05:26 CDT 2024
|