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