Home » RDBMS Server » Server Administration » Complex Query!!!!!!!!!!!!!!!!!!!!!!!!!
Complex Query!!!!!!!!!!!!!!!!!!!!!!!!! [message #374540] Mon, 18 June 2001 10:01 Go to next message
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 !!!!

Report message to a moderator

Re: Complex Query!!!!!!!!!!!!!!!!!!!!!!!!! [message #374543 is a reply to message #374540] Mon, 18 June 2001 10:31 Go to previous messageGo to next message
Arvind L
Messages: 27
Registered: June 2001
Junior Member
Let me know if this works for u,

select x.quote_id, x.section_id, x.roof_system_id, r.long_name from quote_section_xref x, roof_system r
where x.roof_system_id=r.roof_system_id AND
r.roof_system_type='&t';

bye
Arvind

Report message to a moderator

Re: Complex Query!!!!!!!!!!!!!!!!!!!!!!!!! [message #374544 is a reply to message #374540] Mon, 18 June 2001 10:36 Go to previous messageGo to next message
Arvind L
Messages: 27
Registered: June 2001
Junior Member
Let me know if this works for u,

select x.quote_id, x.section_id, x.roof_system_id, r.long_name from quote_section_xref x, roof_system r
where x.roof_system_id=r.roof_system_id AND
r.roof_system_type='&t';

bye
Arvind

Report message to a moderator

Re: Complex Query!!!!!!!!!!!!!!!!!!!!!!!!! [message #374546 is a reply to message #374540] Mon, 18 June 2001 10:43 Go to previous message
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;

Report message to a moderator

Previous Topic: Re: complex query - Help required
Next Topic: Re: complex query - Help required
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Jul 06 08:07:39 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.