Path: utzoo!utgpu!news-server.csri.toronto.edu!cs.utexas.edu!uunet!murphy!peterg
From: peterg@murphy.com (Peter Gutmann)
Newsgroups: comp.databases
Subject: Re: Multiple selects
Message-ID: <1990Oct9.022119.23438@murphy.com>
Date: 9 Oct 90 02:21:19 GMT
References: <1990Oct4.072314.27476@monu6.cc.monash.edu.au>
Organization: Murphy & Durieu
Lines: 47

In article <1990Oct4.072314.27476@monu6.cc.monash.edu.au> edd842c@monu6.cc.monash.edu.au (j.e. 8842141 osborn) writes:
>
>I have a  question which I am sure is a very easy one
>to solve and is solved often - but being a student using
>INFORMIX for an industrial experience project, I haven't
>had much time to get an in-depth knowledge of SQL.
>
>Here's the problem ->
>
>I want to have a select statement such as :
>
>Select * from supp
>   where supp_nbr =
>
>      select supp_nbr from quote
>     	  where quote_nbr =
>
>			   select quote_nbr from quote_line
>				  where quote_line.stk_no = p_stk_no
>
>ie I want to return all of the suppliers who have quoted for
>a certain stock item.

From the looks of what you want you want a three way join. 

>
>The problem is that the two innermost selects can return multiple
>rows.
>

They should, because it is possible for a single vendor to
quote on more than one item. 

>Is the problem as simple as declaring cursors for the innnermost 
>select statements ????
>

I think you want somthing that looks like this;

	select supp.nbr, supp.name, ...
	from supp, quote, quote_line
	where supp.nbr = quote.supp_nbr and
		quote.quote_nbr = quote_line.quote_nbr and
		quote_line.stk_no = "Stock number requested..."

Peter Gutmann		Murphy & Durieu
peterg@murphy.com	"Home of Murphy's Law..."