How to combine 3 SQL statements into 1?

[MackTheKnife]

I have the following 3 SQL statements that need to be combined, if
possible. The output of one feeds the input of the next. I need to view
all of the defined output fields (the output needs to be used in a
Crystal Report).

The SQL Follows:
/* Input is ISBN (vendor_part_number) */
QUERY_1 - returns 1 record
select p.product_id, v.name, m.description, p.author, p.title,
p.revision_number, p.copyright_edition, p.vendor_part_number,
p.conforming_flag,
m.code, mp.unit_price_product, mm.quota_price
from T_PRODUCT p, T_VENDOR v, T_PRODUCT_VENDOR pv,
T_MULTILIST_PRODUCT mp, T_MULTILIST m,
T_MULTILIST_MEMBERSHIP mm where
/* p.vendor_part_number == input */
p.vendor_part_number = '0153364475' and p.medium_type =
'TEXTBOOK' and
p.product_id = pv.product_id and pv.type = 'CONTRACT' and
pv.vendor_id = v.id and
p.product_id = mp.product_id and
m.code = mp.multilist_code and m.proclamation_year =
mp.proclamation_year
and m.proclamation_seq_id = mp.proclamation_seq_id and
m.code = mm.multilist_code and m.proclamation_year =
mm.proclamation_year
and m.proclamation_seq_id = mm.proclamation_seq_id
/* The above should return a single record */

QUERY_2 - returns 2 records
select p.product_id, p.consumable, p.title, p.copyright_edition,
p.vendor_part_number, p.product_type,
p.item_type, p.hardware_required
from T_PRODUCT p, T_PRODUCT_RELATION pr where (pr.relationship_type =
'AID'
or pr.relationship_type = 'KIT') and
p.product_id = pr.child_product_id and pr.parent_product_id =
90321
/* 90321 = result from above: pr.parent_product_id = p.product_id
*/

QUERY_3 - returns 18 records
select p.product_id, p.consumable, p.title, p.copyright_edition,
p.vendor_part_number, p.product_type,
p.item_type, p.hardware_required
from T_PRODUCT p, T_PRODUCT_RELATION pr where (pr.relationship_type =
'AID'
or pr.relationship_type = 'KIT') and
p.product_id = pr.child_product_id and pr.parent_product_id in
(90322, 90323)
/* 90322, 90323 = result from QUERY_2: pr.parent_product_id =
p.product_id */
Only 21 records are returned from these combined queries. I need access
to all of them even though there are 3 different resultsets, 2 of which
contain the same fields. Is there a way to simplify this into a stored
procedure or a view that can take 1 input parameter? It needs to be
used in a Crystal Report, which is limited in its handling of these
types of complex queries.

[Kart]

Hi,

Cant you use Derived tables? I havent gone through the queries though.
But generally when I need to query on results from a query, I use a
derived table.

Its something like this....

select * from
(select * from Employee) A

Dont know whether it will work in your case....

Kart
MackTheKnife wrote:

I have the following 3 SQL statements that need to be combined, if
possible. The output of one feeds the input of the next. I need to view
all of the defined output fields (the output needs to be used in a
Crystal Report).

The SQL Follows:
/* Input is ISBN (vendor_part_number) */
QUERY_1 - returns 1 record
select p.product_id, v.name, m.description, p.author, p.title,
p.revision_number, p.copyright_edition, p.vendor_part_number,
p.conforming_flag,
m.code, mp.unit_price_product, mm.quota_price
from T_PRODUCT p, T_VENDOR v, T_PRODUCT_VENDOR pv,
T_MULTILIST_PRODUCT mp, T_MULTILIST m,
T_MULTILIST_MEMBERSHIP mm where
/* p.vendor_part_number == input */
p.vendor_part_number = '0153364475' and p.medium_type =
'TEXTBOOK' and
p.product_id = pv.product_id and pv.type = 'CONTRACT' and
pv.vendor_id = v.id and
p.product_id = mp.product_id and
m.code = mp.multilist_code and m.proclamation_year =
mp.proclamation_year
and m.proclamation_seq_id = mp.proclamation_seq_id and
m.code = mm.multilist_code and m.proclamation_year =
mm.proclamation_year
and m.proclamation_seq_id = mm.proclamation_seq_id
/* The above should return a single record */

QUERY_2 - returns 2 records
select p.product_id, p.consumable, p.title, p.copyright_edition,
p.vendor_part_number, p.product_type,
p.item_type, p.hardware_required
from T_PRODUCT p, T_PRODUCT_RELATION pr where (pr.relationship_type =
'AID'
or pr.relationship_type = 'KIT') and
p.product_id = pr.child_product_id and pr.parent_product_id =
90321
/* 90321 = result from above: pr.parent_product_id = p.product_id
*/

QUERY_3 - returns 18 records
select p.product_id, p.consumable, p.title, p.copyright_edition,
p.vendor_part_number, p.product_type,
p.item_type, p.hardware_required
from T_PRODUCT p, T_PRODUCT_RELATION pr where (pr.relationship_type =
'AID'
or pr.relationship_type = 'KIT') and
p.product_id = pr.child_product_id and pr.parent_product_id in
(90322, 90323)
/* 90322, 90323 = result from QUERY_2: pr.parent_product_id =
p.product_id */
Only 21 records are returned from these combined queries. I need access
to all of them even though there are 3 different resultsets, 2 of which
contain the same fields. Is there a way to simplify this into a stored
procedure or a view that can take 1 input parameter? It needs to be
used in a Crystal Report, which is limited in its handling of these
types of complex queries.