|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
WHERE IN - Return NULL valuesHi,
I am trying to return a resultset using the WHERE IN with a list of selection criteria, and would like to return a NULL where the value does not exist. Is this possible ? Grateful for any advice. Alex Hi Alex,
No DDL, no specific SQL Syntax for your problem, http://www.aspfaq.com/5006, but anyway: What about LEFT Join ? (More details in the BOL) Select dt.Somecolumn , mt.SomeMastercolumn FROM DetailTable dt INNER JOIN ( SELECT somecolun From masterTable Where YourCondition = SomeValue ) mt ON dt.IDColumnToJoin = mt.IDColumnToJoin HTH, jens Suessmeyer. Assuming the query to be
col in ('123', 'xyz', ...) instead use isnull(col, 'null') in ('null', '123', 'xyz', ...) - R Show quote "Redowl" wrote: > Hi, > > I am trying to return a resultset using the WHERE IN with a list of > selection criteria, and would like to return a NULL where the value does not > exist. Is this possible ? > > Grateful for any advice. > > > Alex > Redowl wrote:
> Hi, The IN predicate returns a Boolean True/False/Unknown result. Explain> > I am trying to return a resultset using the WHERE IN with a list of > selection criteria, and would like to return a NULL where the value does not > exist. Is this possible ? > > Grateful for any advice. > > > Alex what you mean by "return a null". Without a better spec any answers you get will just be guesswork. -- David Portas SQL Server MVP -- Thanks for all the responses.
To explain further the data I am trying to access is not in a strictly relational database. The data is centered around construction projects which may or not have a Project Principal, Project Leader, etc... These values are stored as relations to a particular construction project and each relation has a value. So for example the Project Principle relationship has an attribute_id of '53' and attribute_id value of 'John Jones'. What I am trying to do is search using the WHERE IN statement which includes the relations I am interested ('53', '70') and returns a NULL where this is no existing Project Principal (for example). Not sure if this will help or complicate the matter further. but grateful for any help. Show quote "David Portas" wrote: > Redowl wrote: > > Hi, > > > > I am trying to return a resultset using the WHERE IN with a list of > > selection criteria, and would like to return a NULL where the value does not > > exist. Is this possible ? > > > > Grateful for any advice. > > > > > > Alex > > The IN predicate returns a Boolean True/False/Unknown result. Explain > what you mean by "return a null". Without a better spec any answers you > get will just be guesswork. > > -- > David Portas > SQL Server MVP > -- > > not sure what your relationships are but books online has a great example for
outer joins. i tweeked it to include null relationships. USE pubs select t.title, p.pub_name from titles as t left outer join publishers p on t.pub_id = p.pub_id where (t.pub_id is null or t.pub_id in (1389, 0877)) and <any other where conditions> you can test the null showing up if you change one of the row in the titles table to contain a null value in pub_id. hope that helps, Joe Show quote "Redowl" wrote: > Thanks for all the responses. > > To explain further the data I am trying to access is not in a strictly > relational database. The data is centered around construction projects which > may or not have a Project Principal, Project Leader, etc... These values are > stored as relations to a particular construction project and each relation > has a value. So for example the Project Principle relationship has an > attribute_id of '53' and attribute_id value of 'John Jones'. > > What I am trying to do is search using the WHERE IN statement which includes > the relations I am interested ('53', '70') and returns a NULL where this is > no existing Project Principal (for example). > > Not sure if this will help or complicate the matter further. but grateful > for any help. > > > > > > > > > "David Portas" wrote: > > > Redowl wrote: > > > Hi, > > > > > > I am trying to return a resultset using the WHERE IN with a list of > > > selection criteria, and would like to return a NULL where the value does not > > > exist. Is this possible ? > > > > > > Grateful for any advice. > > > > > > > > > Alex > > > > The IN predicate returns a Boolean True/False/Unknown result. Explain > > what you mean by "return a null". Without a better spec any answers you > > get will just be guesswork. > > > > -- > > David Portas > > SQL Server MVP > > -- > > > >
Show quote
"Redowl" <Red***@discussions.microsoft.com> wrote in message where this isnews:80293851-1D6E-424C-AAAC-4592FABEC7F4@microsoft.com... > Thanks for all the responses. > > To explain further the data I am trying to access is not in a strictly > relational database. The data is centered around construction projects which > may or not have a Project Principal, Project Leader, etc... These values are > stored as relations to a particular construction project and each relation > has a value. So for example the Project Principle relationship has an > attribute_id of '53' and attribute_id value of 'John Jones'. > > What I am trying to do is search using the WHERE IN statement which includes > the relations I am interested ('53', '70') and returns a NULL > no existing Project Principal (for example). Redowl,> > Not sure if this will help or complicate the matter further. but grateful > for any help. > Someone already pointed you at: http://www.aspfaq.com/5006, please read over this. Also, I must admit to not following some of what you mentioned. You say the data is not in a strictly relational database. If the database is not in an RDBMS, then what kind of database is it in? This newsgroup is about SQL Server, and (despite purist claims otherwise), it is an RDBMS database product. It may also be helpful to know that "relation" and "table" are synonymous in the RDBMS world (meaning exactly the same thing). I think you may be referring to primary and foreign key referential integrity constraints, but I cannot be sure. Sincerely, Chris O. Thanks for the responses.
Sorry my early email might have been slightly confusing. The data is held within a SQL Server database but the data does not use any referential integrity nor are the tables normalised. All I am trying to do is similar to trying to return 2 publishers from the publisher table of the Pubs database. If the publisher exists then return the Publishers name and if not I want to return a NULL. However, an easy way to hopefully explain what I am trying to return is, Show quote "Chris2" wrote: > > "Redowl" <Red***@discussions.microsoft.com> wrote in message > news:80293851-1D6E-424C-AAAC-4592FABEC7F4@microsoft.com... > > Thanks for all the responses. > > > > To explain further the data I am trying to access is not in a > strictly > > relational database. The data is centered around construction > projects which > > may or not have a Project Principal, Project Leader, etc... These > values are > > stored as relations to a particular construction project and each > relation > > has a value. So for example the Project Principle relationship > has an > > attribute_id of '53' and attribute_id value of 'John Jones'. > > > > What I am trying to do is search using the WHERE IN statement > which includes > > the relations I am interested ('53', '70') and returns a NULL > where this is > > no existing Project Principal (for example). > > > > Not sure if this will help or complicate the matter further. but > grateful > > for any help. > > > > Redowl, > > Someone already pointed you at: http://www.aspfaq.com/5006, please > read over this. > > Also, I must admit to not following some of what you mentioned. > > You say the data is not in a strictly relational database. If the > database is not in an RDBMS, then what kind of database is it in? > This newsgroup is about SQL Server, and (despite purist claims > otherwise), it is an RDBMS database product. > > It may also be helpful to know that "relation" and "table" are > synonymous in the RDBMS world (meaning exactly the same thing). I > think you may be referring to primary and foreign key referential > integrity constraints, but I cannot be sure. > > > Sincerely, > > Chris O. > > > > All I am trying to do is similar to trying to return 2 publishers from the One method:> publisher table of the Pubs database. If the publisher exists then return > the Publishers name and if not I want to return a NULL. SELECT pub_name FROM publishers RIGHT JOIN (SELECT '53' AS pub_id UNION ALL SELECT '70') AS pubList ON pubList.pub_id = publishers.pub_id -- Show quoteHope this helps. Dan Guzman SQL Server MVP "Alex" <A***@discussions.microsoft.com> wrote in message news:0A043487-5129-4BC5-842B-647E0633AFB5@microsoft.com... > Thanks for the responses. > > Sorry my early email might have been slightly confusing. The data is held > within a SQL Server database but the data does not use any referential > integrity nor are the tables normalised. > > All I am trying to do is similar to trying to return 2 publishers from the > publisher table of the Pubs database. If the publisher exists then return > the Publishers name and if not I want to return a NULL. > > > > > > > > However, an easy way to hopefully explain what I am trying to return is, > > "Chris2" wrote: > >> >> "Redowl" <Red***@discussions.microsoft.com> wrote in message >> news:80293851-1D6E-424C-AAAC-4592FABEC7F4@microsoft.com... >> > Thanks for all the responses. >> > >> > To explain further the data I am trying to access is not in a >> strictly >> > relational database. The data is centered around construction >> projects which >> > may or not have a Project Principal, Project Leader, etc... These >> values are >> > stored as relations to a particular construction project and each >> relation >> > has a value. So for example the Project Principle relationship >> has an >> > attribute_id of '53' and attribute_id value of 'John Jones'. >> > >> > What I am trying to do is search using the WHERE IN statement >> which includes >> > the relations I am interested ('53', '70') and returns a NULL >> where this is >> > no existing Project Principal (for example). >> > >> > Not sure if this will help or complicate the matter further. but >> grateful >> > for any help. >> > >> >> Redowl, >> >> Someone already pointed you at: http://www.aspfaq.com/5006, please >> read over this. >> >> Also, I must admit to not following some of what you mentioned. >> >> You say the data is not in a strictly relational database. If the >> database is not in an RDBMS, then what kind of database is it in? >> This newsgroup is about SQL Server, and (despite purist claims >> otherwise), it is an RDBMS database product. >> >> It may also be helpful to know that "relation" and "table" are >> synonymous in the RDBMS world (meaning exactly the same thing). I >> think you may be referring to primary and foreign key referential >> integrity constraints, but I cannot be sure. >> >> >> Sincerely, >> >> Chris O. >> >> >> Dan,
Thanks very much. That works great for 2 values, could you help me out with the syntax for 5 values ? Once again, thanks. Show quote "Dan Guzman" wrote: > > All I am trying to do is similar to trying to return 2 publishers from the > > publisher table of the Pubs database. If the publisher exists then return > > the Publishers name and if not I want to return a NULL. > > One method: > > SELECT pub_name > FROM publishers > RIGHT JOIN (SELECT '53' AS pub_id > UNION ALL SELECT '70') AS pubList ON > pubList.pub_id = publishers.pub_id > > -- > Hope this helps. > > Dan Guzman > SQL Server MVP > > "Alex" <A***@discussions.microsoft.com> wrote in message > news:0A043487-5129-4BC5-842B-647E0633AFB5@microsoft.com... > > Thanks for the responses. > > > > Sorry my early email might have been slightly confusing. The data is held > > within a SQL Server database but the data does not use any referential > > integrity nor are the tables normalised. > > > > All I am trying to do is similar to trying to return 2 publishers from the > > publisher table of the Pubs database. If the publisher exists then return > > the Publishers name and if not I want to return a NULL. > > > > > > > > > > > > > > > > However, an easy way to hopefully explain what I am trying to return is, > > > > "Chris2" wrote: > > > >> > >> "Redowl" <Red***@discussions.microsoft.com> wrote in message > >> news:80293851-1D6E-424C-AAAC-4592FABEC7F4@microsoft.com... > >> > Thanks for all the responses. > >> > > >> > To explain further the data I am trying to access is not in a > >> strictly > >> > relational database. The data is centered around construction > >> projects which > >> > may or not have a Project Principal, Project Leader, etc... These > >> values are > >> > stored as relations to a particular construction project and each > >> relation > >> > has a value. So for example the Project Principle relationship > >> has an > >> > attribute_id of '53' and attribute_id value of 'John Jones'. > >> > > >> > What I am trying to do is search using the WHERE IN statement > >> which includes > >> > the relations I am interested ('53', '70') and returns a NULL > >> where this is > >> > no existing Project Principal (for example). > >> > > >> > Not sure if this will help or complicate the matter further. but > >> grateful > >> > for any help. > >> > > >> > >> Redowl, > >> > >> Someone already pointed you at: http://www.aspfaq.com/5006, please > >> read over this. > >> > >> Also, I must admit to not following some of what you mentioned. > >> > >> You say the data is not in a strictly relational database. If the > >> database is not in an RDBMS, then what kind of database is it in? > >> This newsgroup is about SQL Server, and (despite purist claims > >> otherwise), it is an RDBMS database product. > >> > >> It may also be helpful to know that "relation" and "table" are > >> synonymous in the RDBMS world (meaning exactly the same thing). I > >> think you may be referring to primary and foreign key referential > >> integrity constraints, but I cannot be sure. > >> > >> > >> Sincerely, > >> > >> Chris O. > >> > >> > >> > > > Dan,
Not too worry I have stopped being lazy and worked it out for myself. Thanks to everybody for their advice and suggestions. Show quote "Alex" wrote: > Dan, > > Thanks very much. That works great for 2 values, could you help me out with > the syntax for 5 values ? > > Once again, thanks. > > > > "Dan Guzman" wrote: > > > > All I am trying to do is similar to trying to return 2 publishers from the > > > publisher table of the Pubs database. If the publisher exists then return > > > the Publishers name and if not I want to return a NULL. > > > > One method: > > > > SELECT pub_name > > FROM publishers > > RIGHT JOIN (SELECT '53' AS pub_id > > UNION ALL SELECT '70') AS pubList ON > > pubList.pub_id = publishers.pub_id > > > > -- > > Hope this helps. > > > > Dan Guzman > > SQL Server MVP > > > > "Alex" <A***@discussions.microsoft.com> wrote in message > > news:0A043487-5129-4BC5-842B-647E0633AFB5@microsoft.com... > > > Thanks for the responses. > > > > > > Sorry my early email might have been slightly confusing. The data is held > > > within a SQL Server database but the data does not use any referential > > > integrity nor are the tables normalised. > > > > > > All I am trying to do is similar to trying to return 2 publishers from the > > > publisher table of the Pubs database. If the publisher exists then return > > > the Publishers name and if not I want to return a NULL. > > > > > > > > > > > > > > > > > > > > > > > > However, an easy way to hopefully explain what I am trying to return is, > > > > > > "Chris2" wrote: > > > > > >> > > >> "Redowl" <Red***@discussions.microsoft.com> wrote in message > > >> news:80293851-1D6E-424C-AAAC-4592FABEC7F4@microsoft.com... > > >> > Thanks for all the responses. > > >> > > > >> > To explain further the data I am trying to access is not in a > > >> strictly > > >> > relational database. The data is centered around construction > > >> projects which > > >> > may or not have a Project Principal, Project Leader, etc... These > > >> values are > > >> > stored as relations to a particular construction project and each > > >> relation > > >> > has a value. So for example the Project Principle relationship > > >> has an > > >> > attribute_id of '53' and attribute_id value of 'John Jones'. > > >> > > > >> > What I am trying to do is search using the WHERE IN statement > > >> which includes > > >> > the relations I am interested ('53', '70') and returns a NULL > > >> where this is > > >> > no existing Project Principal (for example). > > >> > > > >> > Not sure if this will help or complicate the matter further. but > > >> grateful > > >> > for any help. > > >> > > > >> > > >> Redowl, > > >> > > >> Someone already pointed you at: http://www.aspfaq.com/5006, please > > >> read over this. > > >> > > >> Also, I must admit to not following some of what you mentioned. > > >> > > >> You say the data is not in a strictly relational database. If the > > >> database is not in an RDBMS, then what kind of database is it in? > > >> This newsgroup is about SQL Server, and (despite purist claims > > >> otherwise), it is an RDBMS database product. > > >> > > >> It may also be helpful to know that "relation" and "table" are > > >> synonymous in the RDBMS world (meaning exactly the same thing). I > > >> think you may be referring to primary and foreign key referential > > >> integrity constraints, but I cannot be sure. > > >> > > >> > > >> Sincerely, > > >> > > >> Chris O. > > >> > > >> > > >> > > > > > >  |
|||||||||||||||||||||||
Other interesting topics