Path: utzoo!attcan!uunet!seismo!dimacs.rutgers.edu!mips!decwrl!ucbvax!mtxinu!sybase!shalom!ben
From: ben@shalom.sybase.com (ben ullrich)
Newsgroups: comp.databases
Subject: Re: Multiple selects
Message-ID: <11231@sybase.sybase.com>
Date: 9 Oct 90 21:06:10 GMT
References: <1990Oct4.072314.27476@monu6.cc.monash.edu.au>
Sender: news@Sybase.COM
Organization: sybase, inc., emeryville, ca
Lines: 40

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