[comp.databases] Multiple selects

edd842c@monu6.cc.monash.edu.au (j.e. 8842141 osborn) (10/04/90)

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.

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

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

I'd be happy for some advice .....                  


/////////////////////////////////////////////////////////////
Jamie Osborn                  edd842c@monu6.cc.monash.edu.au
/////////////////////////////////////////////////////////////

peterg@murphy.com (Peter Gutmann) (10/09/90)

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..."

jeffb@blia.BLI.COM (Jeff Beard) (10/09/90)

>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
>

You may try

  Select * from supp 
  where exists  select supp_nbr from quote 
		where exists select quote_nbr from quote_line
				  where quote_line.stk_no = p_stk_no

as 'where exists' allows a result SET to be greater than one tuple.

Sub-select querys get very inefficient so it would be better to
use a single WHERE EXISTS and a sub-select that is colapsed from the
above.

ben@shalom.sybase.com (ben ullrich) (10/10/90)

In article <1990Oct4.072314.27476@monu6.cc.monash.edu.au> edd842c@monu6.cc.monash.edu.au (j.e. 8842141 osborn) writes:
>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.

joins, man, joins!  you need joins!!  try this:

select supp.*
from supp, quote, quote_line
where supp.supp_nbr = quote.supp_nbr
and quote.quote_nbr = quote_line.quote_nbr
and quote_line.stk_no = p_stk_no

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

The above query should only return dups in as much as there are dup part
numbers in the quote_line table.  if you REALLY want to use subqueries
(which tend to perform worse than joins), some database vendors support a
qualifier like DISTINCT or UNIQUE which you can add to the SELECT
list to produce only unique rows in the result table.  Sybase uses DISTINCT:
	SELECT DISTINCT quote_num FROM quotes ...



..ben
----
ben ullrich			only i do the talking here -- not my employer.
sybase, inc., emeryville, ca		``jail neil bush, not the homeless.''
ben@sybase.com			       {pyramid,pacbell,sun,lll-tis}!sybase!ben