Query works in access, fails in new SQL Server db

I have the following query running in Cold Fusion and ODBC:
SELECT distinct
[Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Program_Name],
[Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Feeder_Program_Name]
as FeederName,
[Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Healthcare_Catalog_2].[Program_Web]
as FeederWeb FROM
[Program_List_with_Feed_and_Exit_Programs_and_Web_Links] where
[Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Program_Name]
= 'Dental Assistant (Cert)' and
[Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Feeder_Program_Name]
<> '' order by
[Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Feeder_Program_Name]
which works using my Access DB, but when used against the SQL Server DB
returns the error:
[Microsoft][ODBC SQL Server Driver][SQL Server]The column prefix
'Program_List_with_Feed_and_Exit_Programs_and_Web_Links.Healthcare_Catalog_2'
does not match with a table name or alias name used in the query.
I guess my question is how this ever worked in the first places, since
there is no
[Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Healthcare_Catalog_2]
table... I can only see that table when I look at the access query in
design view, so it is the result of some kind of join.
How do I proceed with fixing this?Chris Lott wrote:
> I have the following query running in Cold Fusion and ODBC:
> SELECT distinct
> [Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Program_Name],
> [Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Feeder_Program_Name]
> as FeederName,
> [Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Healthcare_Catalog_2].[Program_Web]
> as FeederWeb FROM
> [Program_List_with_Feed_and_Exit_Programs_and_Web_Links] where
> [Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Program_Name]
> = 'Dental Assistant (Cert)' and
> [Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Feeder_Program_Name]
> <> '' order by
> [Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Feeder_Program_Name]
> which works using my Access DB, but when used against the SQL Server DB
> returns the error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]The column prefix
> 'Program_List_with_Feed_and_Exit_Programs_and_Web_Links.Healthcare_Catalog_2'
> does not match with a table name or alias name used in the query.
> I guess my question is how this ever worked in the first places, since
> there is no
> [Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Healthcare_Catalog_2]
> table... I can only see that table when I look at the access query in
> design view, so it is the result of some kind of join.
> How do I proceed with fixing this?
>
Your Access db must have a query with that big long name that joins two or more tables. Create a view in SQL joining
the same tables. If the view already exists, you may not have permission to access it. Or the view may be in another
schema and you will have to preface that view name with that.sql

Query works in access, fails in new SQL Server db

I have the following query running in Cold Fusion and ODBC:
SELECT distinct
& #91;Program_List_with_Feed_and_Exit_Prog
rams_and_Web_Links].[Program_Na
me],
& #91;Program_List_with_Feed_and_Exit_Prog
rams_and_Web_Links].[Feeder_Pro
gram_Name]
as FeederName,
& #91;Program_List_with_Feed_and_Exit_Prog
rams_and_Web_Links].[Healthcare
_Catalog_2].[Program_Web]
as FeederWeb FROM
& #91;Program_List_with_Feed_and_Exit_Prog
rams_and_Web_Links] where
& #91;Program_List_with_Feed_and_Exit_Prog
rams_and_Web_Links].[Program_Na
me]
= 'Dental Assistant (Cert)' and
& #91;Program_List_with_Feed_and_Exit_Prog
rams_and_Web_Links].[Feeder_Pro
gram_Name]
<> '' order by
& #91;Program_List_with_Feed_and_Exit_Prog
rams_and_Web_Links].[Feeder_Pro
gram_Name]
which works using my Access DB, but when used against the SQL Server DB
returns the error:
[Microsoft][ODBC SQL Server Driver][SQL Server]The column prefix
'Program_List_with_Feed_and_Exit_Program
s_and_Web_Links.Healthcare_Catalog_2
'
does not match with a table name or alias name used in the query.
I guess my question is how this ever worked in the first places, since
there is no
& #91;Program_List_with_Feed_and_Exit_Prog
rams_and_Web_Links].[Healthcare
_Catalog_2]
table... I can only see that table when I look at the access query in
design view, so it is the result of some kind of join.
How do I proceed with fixing this?Chris Lott wrote:
> I have the following query running in Cold Fusion and ODBC:
> SELECT distinct
> & #91;Program_List_with_Feed_and_Exit_Prog
rams_and_Web_Links].[Program_
Name],
> & #91;Program_List_with_Feed_and_Exit_Prog
rams_and_Web_Links].[Feeder_P
rogram_Name]
> as FeederName,
> & #91;Program_List_with_Feed_and_Exit_Prog
rams_and_Web_Links].[Healthca
re_Catalog_2].[Program_Web]
> as FeederWeb FROM
> & #91;Program_List_with_Feed_and_Exit_Prog
rams_and_Web_Links] where
> & #91;Program_List_with_Feed_and_Exit_Prog
rams_and_Web_Links].[Program_
Name]
> = 'Dental Assistant (Cert)' and
> & #91;Program_List_with_Feed_and_Exit_Prog
rams_and_Web_Links].[Feeder_P
rogram_Name]
> <> '' order by
> & #91;Program_List_with_Feed_and_Exit_Prog
rams_and_Web_Links].[Feeder_P
rogram_Name]
> which works using my Access DB, but when used against the SQL Server DB
> returns the error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]The column pref
ix
> 'Program_List_with_Feed_and_Exit_Program
s_and_Web_Links.Healthcare_Catalog
_2'
> does not match with a table name or alias name used in the query.
> I guess my question is how this ever worked in the first places, since
> there is no
> & #91;Program_List_with_Feed_and_Exit_Prog
rams_and_Web_Links].[Healthca
re_Catalog_2]
> table... I can only see that table when I look at the access query in
> design view, so it is the result of some kind of join.
> How do I proceed with fixing this?
>
Your Access db must have a query with that big long name that joins two or m
ore tables. Create a view in SQL joining
the same tables. If the view already exists, you may not have permission to
access it. Or the view may be in another
schema and you will have to preface that view name with that.

query with joins

Can someone please re-write this query for me using better
joins? Thank you very much.
select distinct(c.research), t.FAX, t.NAME, CAST
(exp_AUDITDATE AS varchar(11))
AS AUDITDATE from teachers t (nolock), cases c (nolock),
RecordLog l (nolock)
where l.postedDate > 120703 and
l.postedDate < GETDATE() and l.seqno = c.sequence_no and
l.PostedDate = c.PostedDate
and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1 or
exp_auditdate is null)
AND DELIVERY <> 'INACTIVE' and c.research = t.resNo;
Ida
"Ida Carter" <anonymous@.discussions.microsoft.com> wrote in message
news:2971601c465e6$7c924260$a601280a@.phx.gbl...
> Can someone please re-write this query for me using better
> joins? Thank you very much.
> select distinct(c.research), t.FAX, t.NAME, CAST
> (exp_AUDITDATE AS varchar(11))
> AS AUDITDATE from teachers t (nolock), cases c (nolock),
> RecordLog l (nolock)
> where l.postedDate > 120703 and
> l.postedDate < GETDATE() and l.seqno = c.sequence_no and
> l.PostedDate = c.PostedDate
> and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1 or
> exp_auditdate is null)
> AND DELIVERY <> 'INACTIVE' and c.research = t.resNo;
>
> Ida
Here it is. It has distinct in it so it's probably broken, but without DDL
and a description of what you want, it's impossible to tell how broken.
Also it converts a date to a string, which it probably shouldn't do.
select distinct
c.research,
t.FAX,
t.NAME,
CAST(exp_AUDITDATE AS varchar(11))AS AUDITDATE
from
cases c (nolock)
join teachers t (nolock)
on c.research = t.resNo
join RecordLog l (nolock)
on l.seqno = c.sequence_no
and l.PostedDate = c.PostedDate
where l.postedDate > 120703
and l.postedDate < GETDATE()
and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1 or exp_auditdate is null)
AND DELIVERY <> 'INACTIVE'
David
|||Here's a start:
select distinct c.research,
t.FAX,
t.NAME,
CAST(exp_AUDITDATE AS varchar(11)) AS AUDITDATE
from teachers t (nolock)
join cases c (nolock) ON c.research = t.resNo.
join RecordLog l (nolock) ON l.seqno = c.sequence_no
where l.postedDate > '20031207'
and l.postedDate < GETDATE()
and l.PostedDate = c.PostedDate
and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1
or exp_auditdate is null)
AND DELIVERY <> 'INACTIVE'
"Ida Carter" <anonymous@.discussions.microsoft.com> wrote in message
news:2971601c465e6$7c924260$a601280a@.phx.gbl...
> Can someone please re-write this query for me using better
> joins? Thank you very much.
> select distinct(c.research), t.FAX, t.NAME, CAST
> (exp_AUDITDATE AS varchar(11))
> AS AUDITDATE from teachers t (nolock), cases c (nolock),
> RecordLog l (nolock)
> where l.postedDate > 120703 and
> l.postedDate < GETDATE() and l.seqno = c.sequence_no and
> l.PostedDate = c.PostedDate
> and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1 or
> exp_auditdate is null)
> AND DELIVERY <> 'INACTIVE' and c.research = t.resNo;
>
> Ida
|||Thank you so much
Ida

>--Original Message--
>Here's a start:
>select distinct c.research,
> t.FAX,
> t.NAME,
> CAST(exp_AUDITDATE AS varchar(11)) AS AUDITDATE
>from teachers t (nolock)
>join cases c (nolock) ON c.research = t.resNo.
>join RecordLog l (nolock) ON l.seqno = c.sequence_no
>where l.postedDate > '20031207'
> and l.postedDate < GETDATE()
> and l.PostedDate = c.PostedDate
> and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1
> or exp_auditdate is null)
> AND DELIVERY <> 'INACTIVE'
>
>"Ida Carter" <anonymous@.discussions.microsoft.com> wrote
in message[vbcol=seagreen]
>news:2971601c465e6$7c924260$a601280a@.phx.gbl...
better[vbcol=seagreen]
(nolock),[vbcol=seagreen]
and
>
>.
>
|||Thank you.

>--Original Message--
>"Ida Carter" <anonymous@.discussions.microsoft.com> wrote
in message[vbcol=seagreen]
>news:2971601c465e6$7c924260$a601280a@.phx.gbl...
better[vbcol=seagreen]
(nolock),[vbcol=seagreen]
and
>Here it is. It has distinct in it so it's probably
broken, but without DDL
>and a description of what you want, it's impossible to
tell how broken.
>Also it converts a date to a string, which it probably
shouldn't do.
>select distinct
>c.research,
>t.FAX,
>t.NAME,
>CAST(exp_AUDITDATE AS varchar(11))AS AUDITDATE
>from
>cases c (nolock)
>join teachers t (nolock)
> on c.research = t.resNo
>join RecordLog l (nolock)
> on l.seqno = c.sequence_no
> and l.PostedDate = c.PostedDate
>where l.postedDate > 120703
>and l.postedDate < GETDATE()
>and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1 or
exp_auditdate is null)
>AND DELIVERY <> 'INACTIVE'
>David
>
>.
>

query with joins

Can someone please re-write this query for me using better
joins? Thank you very much.
select distinct(c.research), t.FAX, t.NAME, CAST
(exp_AUDITDATE AS varchar(11))
AS AUDITDATE from teachers t (nolock), cases c (nolock),
RecordLog l (nolock)
where l.postedDate > 120703 and
l.postedDate < GETDATE() and l.seqno = c.sequence_no and
l.PostedDate = c.PostedDate
and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1 or
exp_auditdate is null)
AND DELIVERY <> 'INACTIVE' and c.research = t.resNo;
Ida"Ida Carter" <anonymous@.discussions.microsoft.com> wrote in message
news:2971601c465e6$7c924260$a601280a@.phx
.gbl...
> Can someone please re-write this query for me using better
> joins? Thank you very much.
> select distinct(c.research), t.FAX, t.NAME, CAST
> (exp_AUDITDATE AS varchar(11))
> AS AUDITDATE from teachers t (nolock), cases c (nolock),
> RecordLog l (nolock)
> where l.postedDate > 120703 and
> l.postedDate < GETDATE() and l.seqno = c.sequence_no and
> l.PostedDate = c.PostedDate
> and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1 or
> exp_auditdate is null)
> AND DELIVERY <> 'INACTIVE' and c.research = t.resNo;
>
> Ida
Here it is. It has distinct in it so it's probably broken, but without DDL
and a description of what you want, it's impossible to tell how broken.
Also it converts a date to a string, which it probably shouldn't do.
select distinct
c.research,
t.FAX,
t.NAME,
CAST(exp_AUDITDATE AS varchar(11))AS AUDITDATE
from
cases c (nolock)
join teachers t (nolock)
on c.research = t.resNo
join RecordLog l (nolock)
on l.seqno = c.sequence_no
and l.PostedDate = c.PostedDate
where l.postedDate > 120703
and l.postedDate < GETDATE()
and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1 or exp_auditdate is null)
AND DELIVERY <> 'INACTIVE'
David|||Here's a start:
select distinct c.research,
t.FAX,
t.NAME,
CAST(exp_AUDITDATE AS varchar(11)) AS AUDITDATE
from teachers t (nolock)
join cases c (nolock) ON c.research = t.resNo.
join RecordLog l (nolock) ON l.seqno = c.sequence_no
where l.postedDate > '20031207'
and l.postedDate < GETDATE()
and l.PostedDate = c.PostedDate
and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1
or exp_auditdate is null)
AND DELIVERY <> 'INACTIVE'
"Ida Carter" <anonymous@.discussions.microsoft.com> wrote in message
news:2971601c465e6$7c924260$a601280a@.phx
.gbl...
> Can someone please re-write this query for me using better
> joins? Thank you very much.
> select distinct(c.research), t.FAX, t.NAME, CAST
> (exp_AUDITDATE AS varchar(11))
> AS AUDITDATE from teachers t (nolock), cases c (nolock),
> RecordLog l (nolock)
> where l.postedDate > 120703 and
> l.postedDate < GETDATE() and l.seqno = c.sequence_no and
> l.PostedDate = c.PostedDate
> and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1 or
> exp_auditdate is null)
> AND DELIVERY <> 'INACTIVE' and c.research = t.resNo;
>
> Ida|||Thank you so much
Ida

>--Original Message--
>Here's a start:
>select distinct c.research,
> t.FAX,
> t.NAME,
> CAST(exp_AUDITDATE AS varchar(11)) AS AUDITDATE
>from teachers t (nolock)
>join cases c (nolock) ON c.research = t.resNo.
>join RecordLog l (nolock) ON l.seqno = c.sequence_no
>where l.postedDate > '20031207'
> and l.postedDate < GETDATE()
> and l.PostedDate = c.PostedDate
> and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1
> or exp_auditdate is null)
> AND DELIVERY <> 'INACTIVE'
>
>"Ida Carter" <anonymous@.discussions.microsoft.com> wrote
in message
> news:2971601c465e6$7c924260$a601280a@.phx
.gbl...
better[vbcol=seagreen]
(nolock),[vbcol=seagreen]
and[vbcol=seagreen]
>
>.
>|||Thank you.

>--Original Message--
>"Ida Carter" <anonymous@.discussions.microsoft.com> wrote
in message
> news:2971601c465e6$7c924260$a601280a@.phx
.gbl...
better[vbcol=seagreen]
(nolock),[vbcol=seagreen]
and[vbcol=seagreen]
>Here it is. It has distinct in it so it's probably
broken, but without DDL
>and a description of what you want, it's impossible to
tell how broken.
>Also it converts a date to a string, which it probably
shouldn't do.
>select distinct
>c.research,
>t.FAX,
>t.NAME,
>CAST(exp_AUDITDATE AS varchar(11))AS AUDITDATE
>from
>cases c (nolock)
>join teachers t (nolock)
> on c.research = t.resNo
>join RecordLog l (nolock)
> on l.seqno = c.sequence_no
> and l.PostedDate = c.PostedDate
>where l.postedDate > 120703
>and l.postedDate < GETDATE()
>and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1 or
exp_auditdate is null)
>AND DELIVERY <> 'INACTIVE'
>David
>
>.
>

query with joins

Can someone please re-write this query for me using better
joins? Thank you very much.
select distinct(c.research), t.FAX, t.NAME, CAST
(exp_AUDITDATE AS varchar(11))
AS AUDITDATE from teachers t (nolock), cases c (nolock),
RecordLog l (nolock)
where l.postedDate > 120703 and
l.postedDate < GETDATE() and l.seqno = c.sequence_no and
l.PostedDate = c.PostedDate
and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1 or
exp_auditdate is null)
AND DELIVERY <> 'INACTIVE' and c.research = t.resNo;
Ida"Ida Carter" <anonymous@.discussions.microsoft.com> wrote in message
news:2971601c465e6$7c924260$a601280a@.phx.gbl...
> Can someone please re-write this query for me using better
> joins? Thank you very much.
> select distinct(c.research), t.FAX, t.NAME, CAST
> (exp_AUDITDATE AS varchar(11))
> AS AUDITDATE from teachers t (nolock), cases c (nolock),
> RecordLog l (nolock)
> where l.postedDate > 120703 and
> l.postedDate < GETDATE() and l.seqno = c.sequence_no and
> l.PostedDate = c.PostedDate
> and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1 or
> exp_auditdate is null)
> AND DELIVERY <> 'INACTIVE' and c.research = t.resNo;
>
> Ida
Here it is. It has distinct in it so it's probably broken, but without DDL
and a description of what you want, it's impossible to tell how broken.
Also it converts a date to a string, which it probably shouldn't do.
select distinct
c.research,
t.FAX,
t.NAME,
CAST(exp_AUDITDATE AS varchar(11))AS AUDITDATE
from
cases c (nolock)
join teachers t (nolock)
on c.research = t.resNo
join RecordLog l (nolock)
on l.seqno = c.sequence_no
and l.PostedDate = c.PostedDate
where l.postedDate > 120703
and l.postedDate < GETDATE()
and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1 or exp_auditdate is null)
AND DELIVERY <> 'INACTIVE'
David|||Here's a start:
select distinct c.research,
t.FAX,
t.NAME,
CAST(exp_AUDITDATE AS varchar(11)) AS AUDITDATE
from teachers t (nolock)
join cases c (nolock) ON c.research = t.resNo.
join RecordLog l (nolock) ON l.seqno = c.sequence_no
where l.postedDate > '20031207'
and l.postedDate < GETDATE()
and l.PostedDate = c.PostedDate
and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1
or exp_auditdate is null)
AND DELIVERY <> 'INACTIVE'
"Ida Carter" <anonymous@.discussions.microsoft.com> wrote in message
news:2971601c465e6$7c924260$a601280a@.phx.gbl...
> Can someone please re-write this query for me using better
> joins? Thank you very much.
> select distinct(c.research), t.FAX, t.NAME, CAST
> (exp_AUDITDATE AS varchar(11))
> AS AUDITDATE from teachers t (nolock), cases c (nolock),
> RecordLog l (nolock)
> where l.postedDate > 120703 and
> l.postedDate < GETDATE() and l.seqno = c.sequence_no and
> l.PostedDate = c.PostedDate
> and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1 or
> exp_auditdate is null)
> AND DELIVERY <> 'INACTIVE' and c.research = t.resNo;
>
> Ida|||Thank you so much
Ida
>--Original Message--
>Here's a start:
>select distinct c.research,
> t.FAX,
> t.NAME,
> CAST(exp_AUDITDATE AS varchar(11)) AS AUDITDATE
>from teachers t (nolock)
>join cases c (nolock) ON c.research = t.resNo.
>join RecordLog l (nolock) ON l.seqno = c.sequence_no
>where l.postedDate > '20031207'
> and l.postedDate < GETDATE()
> and l.PostedDate = c.PostedDate
> and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1
> or exp_auditdate is null)
> AND DELIVERY <> 'INACTIVE'
>
>"Ida Carter" <anonymous@.discussions.microsoft.com> wrote
in message
>news:2971601c465e6$7c924260$a601280a@.phx.gbl...
>> Can someone please re-write this query for me using
better
>> joins? Thank you very much.
>> select distinct(c.research), t.FAX, t.NAME, CAST
>> (exp_AUDITDATE AS varchar(11))
>> AS AUDITDATE from teachers t (nolock), cases c
(nolock),
>> RecordLog l (nolock)
>> where l.postedDate > 120703 and
>> l.postedDate < GETDATE() and l.seqno = c.sequence_no
and
>> l.PostedDate = c.PostedDate
>> and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1 or
>> exp_auditdate is null)
>> AND DELIVERY <> 'INACTIVE' and c.research = t.resNo;
>>
>> Ida
>
>.
>|||Thank you.
>--Original Message--
>"Ida Carter" <anonymous@.discussions.microsoft.com> wrote
in message
>news:2971601c465e6$7c924260$a601280a@.phx.gbl...
>> Can someone please re-write this query for me using
better
>> joins? Thank you very much.
>> select distinct(c.research), t.FAX, t.NAME, CAST
>> (exp_AUDITDATE AS varchar(11))
>> AS AUDITDATE from teachers t (nolock), cases c
(nolock),
>> RecordLog l (nolock)
>> where l.postedDate > 120703 and
>> l.postedDate < GETDATE() and l.seqno = c.sequence_no
and
>> l.PostedDate = c.PostedDate
>> and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1 or
>> exp_auditdate is null)
>> AND DELIVERY <> 'INACTIVE' and c.research = t.resNo;
>>
>> Ida
>Here it is. It has distinct in it so it's probably
broken, but without DDL
>and a description of what you want, it's impossible to
tell how broken.
>Also it converts a date to a string, which it probably
shouldn't do.
>select distinct
>c.research,
>t.FAX,
>t.NAME,
>CAST(exp_AUDITDATE AS varchar(11))AS AUDITDATE
>from
>cases c (nolock)
>join teachers t (nolock)
> on c.research = t.resNo
>join RecordLog l (nolock)
> on l.seqno = c.sequence_no
> and l.PostedDate = c.PostedDate
>where l.postedDate > 120703
>and l.postedDate < GETDATE()
>and (DATEDIFF(DAY, exp_AUDITDATE, GETDATE()) > 1 or
exp_auditdate is null)
>AND DELIVERY <> 'INACTIVE'
>David
>
>.
>

Query Timout

OK...i have a query that looks something like what is below and is generated on the fly:

SELECT DISTINCT c.*
FROM SOMETHING c
WHERE ( X IN
 (SELECT Y
 FROM Z
 WHERE ZXY = '1') AND
 ( X IN
 (SELECT Y
 FROM Z
 WHERE ZXY = '4')OR
 ( X IN
 (SELECT Y
 FROM Z
 WHERE ZXY = '9')AND
 ( X IN
 (SELECT Y
 FROM Z
 WHERE ZXY = '7'))

as you can tell something like this will take a long time to process.....what is the best way to handle a query like such?...is cursor the way to go.....it's driving me nuts!

Treyanyone...anyone?|||OK...if nobody can help...can someone point me in the right direction?

Trey|||I possibly would create an index on your ZXY column and create a stored procedure like this to enhance performance:

CREATE PROCEDURE proc_DoSomething
AS
SELECT DISTINCT c.* FROM SOMETHING c
WHERE ( X IN (SELECT Y FROM Z WHERE ZXY IN ('1', '4', '7') OR ZXY = '9'))

Note that I've combined your subqueries involving AND , OR operators into one. I guess this should work well for you.|||I would use a stored procedure but...they query is created on the fly...and i don't know how many variables will be passed to the stored procedure...could be 1 or could be 20.

Trey|||Yeah, this is likely more an index issue. There is nothing in this query that seems to me that it should toake all that long, unless you have millions and millions of rows in your table. If you add some indices to the table, you should be fine...|||ok...then something like this should be written how...?

SELECT DISTINCT c.*
FROM dbo.def c
WHERE (id IN
(SELECT def.id
FROM def INNER JOIN
transaction ON def.id = transaction.id
WHERE (transaction.reason_id = '4790') AND (transaction.type_id = '4706') AND
(transaction.created_date > '1/1/1990 12:00:00') AND (transaction.created_date < '3/16/2004 12:00:00') AND
(transaction.removed_verified_date IS NULL))) OR
(id IN
(SELECT def.id
FROM def INNER JOIN
transaction ON def.id = transaction.id
WHERE (transaction.reason_id = '4753') AND (transaction.type_id = '4706') AND
(transaction.created_date > '1/1/1990 12:00:00') AND (transaction.created_date < '3/16/2004 12:00:00') AND
(transaction.removed_verified_date IS NULL))) OR
(id IN
(SELECT def.id
FROM def INNER JOIN
transaction ON def.id = transaction.id
WHERE (transaction.reason_id = '4767') AND (transaction.type_id = '4706') AND
(transaction.created_date > '1/1/1990 12:00:00') AND (transaction.created_date < '3/16/2004 12:00:00') AND
(transaction.removed_verified_date IS NULL))) OR
(id IN
(SELECT def.id
FROM def INNER JOIN
transaction ON def.id = transaction.id
WHERE (transaction.reason_id = '4777') AND (transaction.type_id = '4707') AND
(transaction.created_date > '1/1/1990 12:00:00') AND (transaction.created_date < '3/16/2004 12:00:00') AND
(transaction.removed_verified_date IS NULL))) AND (id IN
(SELECT def.id
FROM def INNER JOIN
transaction ON def.id = transaction.id
WHERE (transaction.reason_id = '4769') AND (transaction.type_id = '4707') AND
(transaction.created_date > '1/1/1990 12:00:00') AND (transaction.created_date < '3/16/2004 12:00:00') AND
(transaction.removed_verified_date IS NULL))) AND (id IN
(SELECT def.id
FROM def INNER JOIN
transaction ON def.id = transaction.id
WHERE (transaction.reason_id = '4767') AND (transaction.type_id = '4707') AND
(transaction.created_date > '1/1/1990 12:00:00') AND (transaction.created_date < '3/16/2004 12:00:00') AND
(transaction.removed_verified_date IS NULL))) AND (id IN
(SELECT def.id
FROM def INNER JOIN
transaction ON def.id = transaction.id
WHERE (transaction.reason_id = '4773') AND (transaction.type_id = '4707') AND
(transaction.created_date > '1/1/1990 12:00:00') AND (transaction.created_date < '3/16/2004 12:00:00') AND
(transaction.removed_verified_date IS NULL)))

Query taking too long

Hi all.
I have a query that is selecting distinct product and state from a main
table with over 3 million rows. I have an index on state and product, and
used a hint to make it use the index, but it still takes 45 seconds or longe
r
to return. I need to get this down to less than a couple of seconds.
What is the best way to do this? Will a view help? Or create a lookup tabl
e?
Thanks!
SusanWhat kind of index do you have? Do you have 2 seperate indices on each field
or a one composite index on both fields? Is the index clustered? What does
the query and table structure look like?
--
MG
"Susan Cooper" wrote:

> Hi all.
> I have a query that is selecting distinct product and state from a main
> table with over 3 million rows. I have an index on state and product, and
> used a hint to make it use the index, but it still takes 45 seconds or lon
ger
> to return. I need to get this down to less than a couple of seconds.
> What is the best way to do this? Will a view help? Or create a lookup ta
ble?
> Thanks!
> Susan|||The index is a composite index of both fields, with State first. It is not
clustered.
The table is:
[dbo].[policy] (
[policy_number] [char] (12) , --PK
[description] [char] (30) ,
[status] [char] (1) ,
[effective_datetime] [datetime] NULL ,
[state] [char] (2) ,
[source] [char] (3) ,
[source_details] [char] (10) ,
[num_of_rated_drivers] [int] NULL ,
[num_of_excluded_drivers] [int] NULL ,
[num_of_non_driver_residents] [int] NULL ,
[num_of_vehicles] [int] NULL ,
[program] [char] (1) ,
[term] [int] NULL ,
[bill_plan_id] [char] (10) ,
[percent_down_payment] [int] NULL ,
[number_of_installments] [int] NULL ,
[down_payment_amount] [int] NULL ,
[payment_method] [char] (1) ,
[premium_amount] [int] NULL ,
[market_code] [char] (12) ,
[agency_code] [char] (10) ,
[producer_code] [char] (10) ,
[local_fee_1] [int] NULL ,
[local_fee_2] [int] NULL ,
[municipal_tax_rate] [int] NULL ,
[datetime_quote_created] [datetime] NULL ,
[created_by] [char] (10) ,
[datetime_last_updated] [datetime] NULL ,
[updated_by] [char] (10) ,
[datetime_uploaded] [datetime] NULL ,
[uploaded_by] [char] (10) ,
[upload_status] [char] (1) ,
[fees] [int] NULL ,
[received_date] [datetime] NULL ,
[processed_date] [datetime] NULL ,
[Credit_Check_Result] [char] (1) ,
[Number_of_Credit_Requests] [int] NULL ,
[Credit_Score_Level] [char] (2) ,
[Score_Provider] [char] (3) ,
[Score_Reference_Number] [int] NULL ,
[Tier_Group] [char] (2) ,
[Tier] [int] NULL ,
[Adverse_Action_Notice] [char] (1) ,
[Product] [int] NULL ,
[Agent_License_Number] [char] (10) ,
[Underwriting_Group] [int] NULL ,
[Number_of_Nonchargeable_Incidents] [int] NULL ,
[Rate_Book_Indicator] [char] (10) ,
[Version_Number] [char] (50) ,
[Pos_status] [char] (1) ,
[POS_token_id] [char] (32) ,
[UDI_ID] [char] (38) ,
[Quote_ID] [char] (32)
The query is:
select distinct state, product
from dbo.policy
WITH (INDEX (state_program))
The purpose of this is that a new application needs all of the products
associated with each state.
Thanks!
"MGeles" wrote:
[vbcol=seagreen]
> What kind of index do you have? Do you have 2 seperate indices on each fie
ld
> or a one composite index on both fields? Is the index clustered? What do
es
> the query and table structure look like?
> --
> MG
>
> "Susan Cooper" wrote:
>|||Is the query meant to build a list for a dropdown or something like that? I
f
so I'd probably just use a lookup table containing the distinct possibilitie
s
like you had said originally.
I'm not sure that the query would benefit that much from an index because it
looks like it would need to go through the entire talbe to get teh results
that you want.
Did you recently add the index and get a peformance improvement?
--
MG
"Susan Cooper" wrote:
[vbcol=seagreen]
> The index is a composite index of both fields, with State first. It is no
t
> clustered.
> The table is:
> [dbo].[policy] (
> [policy_number] [char] (12) , --PK
> [description] [char] (30) ,
> [status] [char] (1) ,
> [effective_datetime] [datetime] NULL ,
> [state] [char] (2) ,
> [source] [char] (3) ,
> [source_details] [char] (10) ,
> [num_of_rated_drivers] [int] NULL ,
> [num_of_excluded_drivers] [int] NULL ,
> [num_of_non_driver_residents] [int] NULL ,
> [num_of_vehicles] [int] NULL ,
> [program] [char] (1) ,
> [term] [int] NULL ,
> [bill_plan_id] [char] (10) ,
> [percent_down_payment] [int] NULL ,
> [number_of_installments] [int] NULL ,
> [down_payment_amount] [int] NULL ,
> [payment_method] [char] (1) ,
> [premium_amount] [int] NULL ,
> [market_code] [char] (12) ,
> [agency_code] [char] (10) ,
> [producer_code] [char] (10) ,
> [local_fee_1] [int] NULL ,
> [local_fee_2] [int] NULL ,
> [municipal_tax_rate] [int] NULL ,
> [datetime_quote_created] [datetime] NULL ,
> [created_by] [char] (10) ,
> [datetime_last_updated] [datetime] NULL ,
> [updated_by] [char] (10) ,
> [datetime_uploaded] [datetime] NULL ,
> [uploaded_by] [char] (10) ,
> [upload_status] [char] (1) ,
> [fees] [int] NULL ,
> [received_date] [datetime] NULL ,
> [processed_date] [datetime] NULL ,
> [Credit_Check_Result] [char] (1) ,
> [Number_of_Credit_Requests] [int] NULL ,
> [Credit_Score_Level] [char] (2) ,
> [Score_Provider] [char] (3) ,
> [Score_Reference_Number] [int] NULL ,
> [Tier_Group] [char] (2) ,
> [Tier] [int] NULL ,
> [Adverse_Action_Notice] [char] (1) ,
> [Product] [int] NULL ,
> [Agent_License_Number] [char] (10) ,
> [Underwriting_Group] [int] NULL ,
> [Number_of_Nonchargeable_Incidents] [int] NULL ,
> [Rate_Book_Indicator] [char] (10) ,
> [Version_Number] [char] (50) ,
> [Pos_status] [char] (1) ,
> [POS_token_id] [char] (32) ,
> [UDI_ID] [char] (38) ,
> [Quote_ID] [char] (32)
> The query is:
> select distinct state, product
> from dbo.policy
> WITH (INDEX (state_program))
> The purpose of this is that a new application needs all of the products
> associated with each state.
> Thanks!
> "MGeles" wrote:
>|||I think this will be used for a drop down or something similar. I almost
created a lookup table, but then wondered if there was a better way to do
this.
I did recently add the index, and it didn't help things much at all.
Thanks so much for your help.
"MGeles" wrote:
[vbcol=seagreen]
> Is the query meant to build a list for a dropdown or something like that?
If
> so I'd probably just use a lookup table containing the distinct possibilit
ies
> like you had said originally.
> I'm not sure that the query would benefit that much from an index because
it
> looks like it would need to go through the entire talbe to get teh results
> that you want.
> Did you recently add the index and get a peformance improvement?
> --
> MG
>
> "Susan Cooper" wrote:
>|||On Tue, 25 Apr 2006 08:34:02 -0700, Susan Cooper wrote:

>The index is a composite index of both fields, with State first. It is not
>clustered.
(snip DDL)
>The query is:
>select distinct state, product
>from dbo.policy
>WITH (INDEX (state_program))
Hi Susan,
Since there is no WHERE clause, this query has to scan the complete
index. With over 3 milion rows, this will take time.
You might consider using an indexed view. This basically precomputes the
information you need and changes the precomputed results every time the
data in the table changes. This will introduce some overhead for changes
to the data in the table, but judging by the name, this is not a table
that gets changed very frequently.
CREATE VIEW ProductsPerState WITH SCHEMABINDING
AS
SELECT state, product, COUNT_BIG(*) AS tally
FROM dbo.policy
GROUP BY state, product
go
CREATE UNIQUE CLUSTERED INDEX ix_ProductsPerState
ON ProductsPerState(state, product)
go
Once the view is created, you can build the dropdown by querying the
ProductsPerState view.
Hugo Kornelis, SQL Server MVP

Query taking too long

Hi all.
I have a query that is selecting distinct product and state from a main
table with over 3 million rows. I have an index on state and product, and
used a hint to make it use the index, but it still takes 45 seconds or longer
to return. I need to get this down to less than a couple of seconds.
What is the best way to do this? Will a view help? Or create a lookup table?
Thanks!
SusanWhat kind of index do you have? Do you have 2 seperate indices on each field
or a one composite index on both fields? Is the index clustered? What does
the query and table structure look like?
--
MG
"Susan Cooper" wrote:
> Hi all.
> I have a query that is selecting distinct product and state from a main
> table with over 3 million rows. I have an index on state and product, and
> used a hint to make it use the index, but it still takes 45 seconds or longer
> to return. I need to get this down to less than a couple of seconds.
> What is the best way to do this? Will a view help? Or create a lookup table?
> Thanks!
> Susan|||The index is a composite index of both fields, with State first. It is not
clustered.
The table is:
[dbo].[policy] (
[policy_number] [char] (12) , --PK
[description] [char] (30) ,
[status] [char] (1) ,
[effective_datetime] [datetime] NULL ,
[state] [char] (2) ,
[source] [char] (3) ,
[source_details] [char] (10) ,
[num_of_rated_drivers] [int] NULL ,
[num_of_excluded_drivers] [int] NULL ,
[num_of_non_driver_residents] [int] NULL ,
[num_of_vehicles] [int] NULL ,
[program] [char] (1) ,
[term] [int] NULL ,
[bill_plan_id] [char] (10) ,
[percent_down_payment] [int] NULL ,
[number_of_installments] [int] NULL ,
[down_payment_amount] [int] NULL ,
[payment_method] [char] (1) ,
[premium_amount] [int] NULL ,
[market_code] [char] (12) ,
[agency_code] [char] (10) ,
[producer_code] [char] (10) ,
[local_fee_1] [int] NULL ,
[local_fee_2] [int] NULL ,
[municipal_tax_rate] [int] NULL ,
[datetime_quote_created] [datetime] NULL ,
[created_by] [char] (10) ,
[datetime_last_updated] [datetime] NULL ,
[updated_by] [char] (10) ,
[datetime_uploaded] [datetime] NULL ,
[uploaded_by] [char] (10) ,
[upload_status] [char] (1) ,
[fees] [int] NULL ,
[received_date] [datetime] NULL ,
[processed_date] [datetime] NULL ,
[Credit_Check_Result] [char] (1) ,
[Number_of_Credit_Requests] [int] NULL ,
[Credit_Score_Level] [char] (2) ,
[Score_Provider] [char] (3) ,
[Score_Reference_Number] [int] NULL ,
[Tier_Group] [char] (2) ,
[Tier] [int] NULL ,
[Adverse_Action_Notice] [char] (1) ,
[Product] [int] NULL ,
[Agent_License_Number] [char] (10) ,
[Underwriting_Group] [int] NULL ,
[Number_of_Nonchargeable_Incidents] [int] NULL ,
[Rate_Book_Indicator] [char] (10) ,
[Version_Number] [char] (50) ,
[Pos_status] [char] (1) ,
[POS_token_id] [char] (32) ,
[UDI_ID] [char] (38) ,
[Quote_ID] [char] (32)
The query is:
select distinct state, product
from dbo.policy
WITH (INDEX (state_program))
The purpose of this is that a new application needs all of the products
associated with each state.
Thanks!
"MGeles" wrote:
> What kind of index do you have? Do you have 2 seperate indices on each field
> or a one composite index on both fields? Is the index clustered? What does
> the query and table structure look like?
> --
> MG
>
> "Susan Cooper" wrote:
> > Hi all.
> >
> > I have a query that is selecting distinct product and state from a main
> > table with over 3 million rows. I have an index on state and product, and
> > used a hint to make it use the index, but it still takes 45 seconds or longer
> > to return. I need to get this down to less than a couple of seconds.
> >
> > What is the best way to do this? Will a view help? Or create a lookup table?
> >
> > Thanks!
> > Susan|||Is the query meant to build a list for a dropdown or something like that? If
so I'd probably just use a lookup table containing the distinct possibilities
like you had said originally.
I'm not sure that the query would benefit that much from an index because it
looks like it would need to go through the entire talbe to get teh results
that you want.
Did you recently add the index and get a peformance improvement?
--
MG
"Susan Cooper" wrote:
> The index is a composite index of both fields, with State first. It is not
> clustered.
> The table is:
> [dbo].[policy] (
> [policy_number] [char] (12) , --PK
> [description] [char] (30) ,
> [status] [char] (1) ,
> [effective_datetime] [datetime] NULL ,
> [state] [char] (2) ,
> [source] [char] (3) ,
> [source_details] [char] (10) ,
> [num_of_rated_drivers] [int] NULL ,
> [num_of_excluded_drivers] [int] NULL ,
> [num_of_non_driver_residents] [int] NULL ,
> [num_of_vehicles] [int] NULL ,
> [program] [char] (1) ,
> [term] [int] NULL ,
> [bill_plan_id] [char] (10) ,
> [percent_down_payment] [int] NULL ,
> [number_of_installments] [int] NULL ,
> [down_payment_amount] [int] NULL ,
> [payment_method] [char] (1) ,
> [premium_amount] [int] NULL ,
> [market_code] [char] (12) ,
> [agency_code] [char] (10) ,
> [producer_code] [char] (10) ,
> [local_fee_1] [int] NULL ,
> [local_fee_2] [int] NULL ,
> [municipal_tax_rate] [int] NULL ,
> [datetime_quote_created] [datetime] NULL ,
> [created_by] [char] (10) ,
> [datetime_last_updated] [datetime] NULL ,
> [updated_by] [char] (10) ,
> [datetime_uploaded] [datetime] NULL ,
> [uploaded_by] [char] (10) ,
> [upload_status] [char] (1) ,
> [fees] [int] NULL ,
> [received_date] [datetime] NULL ,
> [processed_date] [datetime] NULL ,
> [Credit_Check_Result] [char] (1) ,
> [Number_of_Credit_Requests] [int] NULL ,
> [Credit_Score_Level] [char] (2) ,
> [Score_Provider] [char] (3) ,
> [Score_Reference_Number] [int] NULL ,
> [Tier_Group] [char] (2) ,
> [Tier] [int] NULL ,
> [Adverse_Action_Notice] [char] (1) ,
> [Product] [int] NULL ,
> [Agent_License_Number] [char] (10) ,
> [Underwriting_Group] [int] NULL ,
> [Number_of_Nonchargeable_Incidents] [int] NULL ,
> [Rate_Book_Indicator] [char] (10) ,
> [Version_Number] [char] (50) ,
> [Pos_status] [char] (1) ,
> [POS_token_id] [char] (32) ,
> [UDI_ID] [char] (38) ,
> [Quote_ID] [char] (32)
> The query is:
> select distinct state, product
> from dbo.policy
> WITH (INDEX (state_program))
> The purpose of this is that a new application needs all of the products
> associated with each state.
> Thanks!
> "MGeles" wrote:
> > What kind of index do you have? Do you have 2 seperate indices on each field
> > or a one composite index on both fields? Is the index clustered? What does
> > the query and table structure look like?
> > --
> > MG
> >
> >
> > "Susan Cooper" wrote:
> >
> > > Hi all.
> > >
> > > I have a query that is selecting distinct product and state from a main
> > > table with over 3 million rows. I have an index on state and product, and
> > > used a hint to make it use the index, but it still takes 45 seconds or longer
> > > to return. I need to get this down to less than a couple of seconds.
> > >
> > > What is the best way to do this? Will a view help? Or create a lookup table?
> > >
> > > Thanks!
> > > Susan|||I think this will be used for a drop down or something similar. I almost
created a lookup table, but then wondered if there was a better way to do
this.
I did recently add the index, and it didn't help things much at all.
Thanks so much for your help.
"MGeles" wrote:
> Is the query meant to build a list for a dropdown or something like that? If
> so I'd probably just use a lookup table containing the distinct possibilities
> like you had said originally.
> I'm not sure that the query would benefit that much from an index because it
> looks like it would need to go through the entire talbe to get teh results
> that you want.
> Did you recently add the index and get a peformance improvement?
> --
> MG
>
> "Susan Cooper" wrote:
> > The index is a composite index of both fields, with State first. It is not
> > clustered.
> >
> > The table is:
> > [dbo].[policy] (
> > [policy_number] [char] (12) , --PK
> > [description] [char] (30) ,
> > [status] [char] (1) ,
> > [effective_datetime] [datetime] NULL ,
> > [state] [char] (2) ,
> > [source] [char] (3) ,
> > [source_details] [char] (10) ,
> > [num_of_rated_drivers] [int] NULL ,
> > [num_of_excluded_drivers] [int] NULL ,
> > [num_of_non_driver_residents] [int] NULL ,
> > [num_of_vehicles] [int] NULL ,
> > [program] [char] (1) ,
> > [term] [int] NULL ,
> > [bill_plan_id] [char] (10) ,
> > [percent_down_payment] [int] NULL ,
> > [number_of_installments] [int] NULL ,
> > [down_payment_amount] [int] NULL ,
> > [payment_method] [char] (1) ,
> > [premium_amount] [int] NULL ,
> > [market_code] [char] (12) ,
> > [agency_code] [char] (10) ,
> > [producer_code] [char] (10) ,
> > [local_fee_1] [int] NULL ,
> > [local_fee_2] [int] NULL ,
> > [municipal_tax_rate] [int] NULL ,
> > [datetime_quote_created] [datetime] NULL ,
> > [created_by] [char] (10) ,
> > [datetime_last_updated] [datetime] NULL ,
> > [updated_by] [char] (10) ,
> > [datetime_uploaded] [datetime] NULL ,
> > [uploaded_by] [char] (10) ,
> > [upload_status] [char] (1) ,
> > [fees] [int] NULL ,
> > [received_date] [datetime] NULL ,
> > [processed_date] [datetime] NULL ,
> > [Credit_Check_Result] [char] (1) ,
> > [Number_of_Credit_Requests] [int] NULL ,
> > [Credit_Score_Level] [char] (2) ,
> > [Score_Provider] [char] (3) ,
> > [Score_Reference_Number] [int] NULL ,
> > [Tier_Group] [char] (2) ,
> > [Tier] [int] NULL ,
> > [Adverse_Action_Notice] [char] (1) ,
> > [Product] [int] NULL ,
> > [Agent_License_Number] [char] (10) ,
> > [Underwriting_Group] [int] NULL ,
> > [Number_of_Nonchargeable_Incidents] [int] NULL ,
> > [Rate_Book_Indicator] [char] (10) ,
> > [Version_Number] [char] (50) ,
> > [Pos_status] [char] (1) ,
> > [POS_token_id] [char] (32) ,
> > [UDI_ID] [char] (38) ,
> > [Quote_ID] [char] (32)
> >
> > The query is:
> > select distinct state, product
> > from dbo.policy
> > WITH (INDEX (state_program))
> >
> > The purpose of this is that a new application needs all of the products
> > associated with each state.
> >
> > Thanks!
> >
> > "MGeles" wrote:
> >
> > > What kind of index do you have? Do you have 2 seperate indices on each field
> > > or a one composite index on both fields? Is the index clustered? What does
> > > the query and table structure look like?
> > > --
> > > MG
> > >
> > >
> > > "Susan Cooper" wrote:
> > >
> > > > Hi all.
> > > >
> > > > I have a query that is selecting distinct product and state from a main
> > > > table with over 3 million rows. I have an index on state and product, and
> > > > used a hint to make it use the index, but it still takes 45 seconds or longer
> > > > to return. I need to get this down to less than a couple of seconds.
> > > >
> > > > What is the best way to do this? Will a view help? Or create a lookup table?
> > > >
> > > > Thanks!
> > > > Susan|||On Tue, 25 Apr 2006 08:34:02 -0700, Susan Cooper wrote:
>The index is a composite index of both fields, with State first. It is not
>clustered.
(snip DDL)
>The query is:
>select distinct state, product
>from dbo.policy
>WITH (INDEX (state_program))
Hi Susan,
Since there is no WHERE clause, this query has to scan the complete
index. With over 3 milion rows, this will take time.
You might consider using an indexed view. This basically precomputes the
information you need and changes the precomputed results every time the
data in the table changes. This will introduce some overhead for changes
to the data in the table, but judging by the name, this is not a table
that gets changed very frequently.
CREATE VIEW ProductsPerState WITH SCHEMABINDING
AS
SELECT state, product, COUNT_BIG(*) AS tally
FROM dbo.policy
GROUP BY state, product
go
CREATE UNIQUE CLUSTERED INDEX ix_ProductsPerState
ON ProductsPerState(state, product)
go
Once the view is created, you can build the dropdown by querying the
ProductsPerState view.
--
Hugo Kornelis, SQL Server MVP