Query Question

How can I get similar results from a query like this >
SELECT au_lname, state
FROM authors
WHERE state IN ('CA', 'IN', 'MD')
Except to replace ('CA', 'IN', 'MD') With a field that will have a similar
format'
The Field is Misc it's contents are (100, 101, 102) and so on. I need to
display a row for each occurrence of 3 digits in that field and replacing
('CA', 'IN', 'MD') with something like (SELECT au_id FROM titleauthor
WHERE royaltyper < 50) is not the same thing..
I'm thinking I need some type of variable length array, but I am out of
practice and not sure, can some one please help me.."WANNABE" <breichenbach AT istate DOT com> wrote in message
news:e34iWyJwGHA.1216@.TK2MSFTNGP03.phx.gbl...
> How can I get similar results from a query like this >
> SELECT au_lname, state
> FROM authors
> WHERE state IN ('CA', 'IN', 'MD')
> Except to replace ('CA', 'IN', 'MD') With a field that will have a similar
> format'
> The Field is Misc it's contents are (100, 101, 102) and so on. I need to
> display a row for each occurrence of 3 digits in that field and replacing
> ('CA', 'IN', 'MD') with something like (SELECT au_id FROM titleauthor
> WHERE royaltyper < 50) is not the same thing..
> I'm thinking I need some type of variable length array, but I am out of
> practice and not sure, can some one please help me..
>
I'm not completely following here, but you can use either an IN clause or a
WHERE EXISTS clause.
Perhaps something like:
SELECT au_id
FROM titleauthor
WHERE SomeValue IN (SELECT myLookupValues FROM sometable WHERE
somecondition)
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks for you response Rick, but I think what you have described below is
what I have been trying to get to work. When I run this >>
SELECT au_lname, state
FROM authors
WHERE state IN ('CA', 'IN', 'MD')
I get a long list of records. I would like to get the same long list of
records by running something like the following query, AFTER I HAVE MODIFIED
THE stores TABLE TO INCLUDE THE stid FIELD and ENTERED THE VALUE (CA, IN,
MD) into that field for the record where stor_id is equal to 7067.
When I run this next query AFTER I have made the modifications described
above, I get only column headers>>
SELECT au_lname, state
FROM authors
WHERE state IN
(SELECT stid
FROM stores
WHERE stor_id = '7067')
This is all done in testing using the PUBS database, and here are the
queries used to modify that db
alter table pubs.dbo.stores add stid char(50)
UPDATE stores
SET [stid] = '(CA, IN, MD)'
where stor_id = '7067'
======================================="Rick Sawtell" <Quickening@.msn.com> wrote in message
news:%23OwzMOKwGHA.1288@.TK2MSFTNGP02.phx.gbl...
> "WANNABE" <breichenbach AT istate DOT com> wrote in message
> news:e34iWyJwGHA.1216@.TK2MSFTNGP03.phx.gbl...
>> How can I get similar results from a query like this >
>> SELECT au_lname, state
>> FROM authors
>> WHERE state IN ('CA', 'IN', 'MD')
>> Except to replace ('CA', 'IN', 'MD') With a field that will have a
>> similar format'
>> The Field is Misc it's contents are (100, 101, 102) and so on. I need to
>> display a row for each occurrence of 3 digits in that field and replacing
>> ('CA', 'IN', 'MD') with something like (SELECT au_id FROM titleauthor
>> WHERE royaltyper < 50) is not the same thing..
>> I'm thinking I need some type of variable length array, but I am out of
>> practice and not sure, can some one please help me..
> I'm not completely following here, but you can use either an IN clause or
> a WHERE EXISTS clause.
> Perhaps something like:
> SELECT au_id
> FROM titleauthor
> WHERE SomeValue IN (SELECT myLookupValues FROM sometable WHERE
> somecondition)
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||WANNABE wrote:
> Thanks for you response Rick, but I think what you have described below is
> what I have been trying to get to work. When I run this >>
> SELECT au_lname, state
> FROM authors
> WHERE state IN ('CA', 'IN', 'MD')
> I get a long list of records. I would like to get the same long list of
> records by running something like the following query, AFTER I HAVE MODIFIED
> THE stores TABLE TO INCLUDE THE stid FIELD and ENTERED THE VALUE (CA, IN,
> MD) into that field for the record where stor_id is equal to 7067.
> When I run this next query AFTER I have made the modifications described
> above, I get only column headers>>
> SELECT au_lname, state
> FROM authors
> WHERE state IN
> (SELECT stid
> FROM stores
> WHERE stor_id = '7067')
> This is all done in testing using the PUBS database, and here are the
> queries used to modify that db
> alter table pubs.dbo.stores add stid char(50)
> UPDATE stores
> SET [stid] = '(CA, IN, MD)'
> where stor_id = '7067'
You're looking for a way to parse a comma-delimited string and use its
elements in a query. Start by reading this:
http://www.realsqlguy.com/serendipity/archives/4-Parse-A-Delimited-String-Into-A-Table.html
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks Tracy, but that is the opposite of what I am trying to do, which is
to parse a delimited string from a table. Can someone tell me how'
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:44E31A57.9030809@.realsqlguy.com...
> WANNABE wrote:
>> Thanks for you response Rick, but I think what you have described below
>> is what I have been trying to get to work. When I run this >>
>> SELECT au_lname, state
>> FROM authors
>> WHERE state IN ('CA', 'IN', 'MD')
>> I get a long list of records. I would like to get the same long list of
>> records by running something like the following query, AFTER I HAVE
>> MODIFIED THE stores TABLE TO INCLUDE THE stid FIELD and ENTERED THE VALUE
>> (CA, IN, MD) into that field for the record where stor_id is equal to
>> 7067.
>> When I run this next query AFTER I have made the modifications described
>> above, I get only column headers>>
>> SELECT au_lname, state
>> FROM authors
>> WHERE state IN
>> (SELECT stid
>> FROM stores
>> WHERE stor_id = '7067')
>> This is all done in testing using the PUBS database, and here are the
>> queries used to modify that db
>> alter table pubs.dbo.stores add stid char(50)
>> UPDATE stores
>> SET [stid] = '(CA, IN, MD)'
>> where stor_id = '7067'
> You're looking for a way to parse a comma-delimited string and use its
> elements in a query. Start by reading this:
> http://www.realsqlguy.com/serendipity/archives/4-Parse-A-Delimited-String-Into-A-Table.html
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com