Rotational Query?

We're facing a large dilemma. We've rebuilt our entire database in a much
more "normalized" fashion than we had previously (the design was
outrageously bad before). Ultimately, we have the following scenario and
we're getting a huge timeout problem:
Tables:
TblOrder (large, central table, contains all "files", approximately
130,000 records)
OrderFees (joined to TblOrder by FileNumber field, contains FeeType and
Fee)
DateTypes (joined to AppraisalDates by DateType field, contains the
"name" of the Date)
AppraisalDates (joined to TblOrder by FileNumber field, contains
DateType Field and Date)
Right now we're doing a query that returns approximately 5000 records from
TblOrder, then it queries AppraisalDates to get the relevant dates, then it
queries OrderFees, get the relevant fees and then it cycles to the next
record in TblOrder. Big timeout problems.
The only solution I could come up with for this is to create a view that
somehow rotates the relevant records from OrderFees and AppraisalDates and
merges them with TblOrder so that we only have to do one query instead of 3
separate queries. Is this an appropriate solution? I don't know that this
will buy us any "time".
Any suggestions would be appreciated.
Thanks!
JamesPlease post DDL, sample INSERTs and required results so that we don't have
to guess.
http://www.aspfaq.com/etiquette.asp?id=5006
I don't understand why you would "query TblOrder... then query
AppraisalDates...", etc. Don't you just join these tables? You seem to be
describing a cursor.
David Portas
SQL Server MVP
--|||Is there a reason why you can't pull the records from all 4 tables in a
single query with joins?
From your description it looks like:
SELECT columns FROM
tblOrder JOIN OrderFees ON FileNumber...
tblOrder JOIN AppraisalDates ON FileNumber..
AppraisalDates JOIN DateTypes on DateType
The DDL and some INSERT commands for the various tables as well as the
output you would like to see would be helpful. Someone here could then
craft a query for you.
Rick Sawtell
MCT, MCSD, MCDBA
"James" <cppjames@.aol.com> wrote in message
news:%23Srk%23butEHA.3448@.TK2MSFTNGP09.phx.gbl...
> We're facing a large dilemma. We've rebuilt our entire database in a much
> more "normalized" fashion than we had previously (the design was
> outrageously bad before). Ultimately, we have the following scenario and
> we're getting a huge timeout problem:
> Tables:
> TblOrder (large, central table, contains all "files", approximately
> 130,000 records)
> OrderFees (joined to TblOrder by FileNumber field, contains FeeType
and
> Fee)
> DateTypes (joined to AppraisalDates by DateType field, contains the
> "name" of the Date)
> AppraisalDates (joined to TblOrder by FileNumber field, contains
> DateType Field and Date)
> Right now we're doing a query that returns approximately 5000 records from
> TblOrder, then it queries AppraisalDates to get the relevant dates, then
it
> queries OrderFees, get the relevant fees and then it cycles to the next
> record in TblOrder. Big timeout problems.
> The only solution I could come up with for this is to create a view that
> somehow rotates the relevant records from OrderFees and AppraisalDates and
> merges them with TblOrder so that we only have to do one query instead of
3
> separate queries. Is this an appropriate solution? I don't know that
this
> will buy us any "time".
> Any suggestions would be appreciated.
> Thanks!
> James
>|||Did you find an answer for this one? If not, post your queries and DDL and
we can help out.
Thanks,
Michael C., MCDBA
"James" <cppjames@.aol.com> wrote in message
news:%23Srk%23butEHA.3448@.TK2MSFTNGP09.phx.gbl...
> We're facing a large dilemma. We've rebuilt our entire database in a much
> more "normalized" fashion than we had previously (the design was
> outrageously bad before). Ultimately, we have the following scenario and
> we're getting a huge timeout problem:
> Tables:
> TblOrder (large, central table, contains all "files", approximately
> 130,000 records)
> OrderFees (joined to TblOrder by FileNumber field, contains FeeType
and
> Fee)
> DateTypes (joined to AppraisalDates by DateType field, contains the
> "name" of the Date)
> AppraisalDates (joined to TblOrder by FileNumber field, contains
> DateType Field and Date)
> Right now we're doing a query that returns approximately 5000 records from
> TblOrder, then it queries AppraisalDates to get the relevant dates, then
it
> queries OrderFees, get the relevant fees and then it cycles to the next
> record in TblOrder. Big timeout problems.
> The only solution I could come up with for this is to create a view that
> somehow rotates the relevant records from OrderFees and AppraisalDates and
> merges them with TblOrder so that we only have to do one query instead of
3
> separate queries. Is this an appropriate solution? I don't know that
this
> will buy us any "time".
> Any suggestions would be appreciated.
> Thanks!
> James
>

Rotational Query?

We're facing a large dilemma. We've rebuilt our entire database in a much
more "normalized" fashion than we had previously (the design was
outrageously bad before). Ultimately, we have the following scenario and
we're getting a huge timeout problem:
Tables:
TblOrder (large, central table, contains all "files", approximately
130,000 records)
OrderFees (joined to TblOrder by FileNumber field, contains FeeType and
Fee)
DateTypes (joined to AppraisalDates by DateType field, contains the
"name" of the Date)
AppraisalDates (joined to TblOrder by FileNumber field, contains
DateType Field and Date)
Right now we're doing a query that returns approximately 5000 records from
TblOrder, then it queries AppraisalDates to get the relevant dates, then it
queries OrderFees, get the relevant fees and then it cycles to the next
record in TblOrder. Big timeout problems.
The only solution I could come up with for this is to create a view that
somehow rotates the relevant records from OrderFees and AppraisalDates and
merges them with TblOrder so that we only have to do one query instead of 3
separate queries. Is this an appropriate solution? I don't know that this
will buy us any "time".
Any suggestions would be appreciated.
Thanks!
James
Please post DDL, sample INSERTs and required results so that we don't have
to guess.
http://www.aspfaq.com/etiquette.asp?id=5006
I don't understand why you would "query TblOrder... then query
AppraisalDates...", etc. Don't you just join these tables? You seem to be
describing a cursor.
David Portas
SQL Server MVP
|||Is there a reason why you can't pull the records from all 4 tables in a
single query with joins?
From your description it looks like:
SELECT columns FROM
tblOrder JOIN OrderFees ON FileNumber...
tblOrder JOIN AppraisalDates ON FileNumber..
AppraisalDates JOIN DateTypes on DateType
The DDL and some INSERT commands for the various tables as well as the
output you would like to see would be helpful. Someone here could then
craft a query for you.
Rick Sawtell
MCT, MCSD, MCDBA
"James" <cppjames@.aol.com> wrote in message
news:%23Srk%23butEHA.3448@.TK2MSFTNGP09.phx.gbl...
> We're facing a large dilemma. We've rebuilt our entire database in a much
> more "normalized" fashion than we had previously (the design was
> outrageously bad before). Ultimately, we have the following scenario and
> we're getting a huge timeout problem:
> Tables:
> TblOrder (large, central table, contains all "files", approximately
> 130,000 records)
> OrderFees (joined to TblOrder by FileNumber field, contains FeeType
and
> Fee)
> DateTypes (joined to AppraisalDates by DateType field, contains the
> "name" of the Date)
> AppraisalDates (joined to TblOrder by FileNumber field, contains
> DateType Field and Date)
> Right now we're doing a query that returns approximately 5000 records from
> TblOrder, then it queries AppraisalDates to get the relevant dates, then
it
> queries OrderFees, get the relevant fees and then it cycles to the next
> record in TblOrder. Big timeout problems.
> The only solution I could come up with for this is to create a view that
> somehow rotates the relevant records from OrderFees and AppraisalDates and
> merges them with TblOrder so that we only have to do one query instead of
3
> separate queries. Is this an appropriate solution? I don't know that
this
> will buy us any "time".
> Any suggestions would be appreciated.
> Thanks!
> James
>
|||Did you find an answer for this one? If not, post your queries and DDL and
we can help out.
Thanks,
Michael C., MCDBA
"James" <cppjames@.aol.com> wrote in message
news:%23Srk%23butEHA.3448@.TK2MSFTNGP09.phx.gbl...
> We're facing a large dilemma. We've rebuilt our entire database in a much
> more "normalized" fashion than we had previously (the design was
> outrageously bad before). Ultimately, we have the following scenario and
> we're getting a huge timeout problem:
> Tables:
> TblOrder (large, central table, contains all "files", approximately
> 130,000 records)
> OrderFees (joined to TblOrder by FileNumber field, contains FeeType
and
> Fee)
> DateTypes (joined to AppraisalDates by DateType field, contains the
> "name" of the Date)
> AppraisalDates (joined to TblOrder by FileNumber field, contains
> DateType Field and Date)
> Right now we're doing a query that returns approximately 5000 records from
> TblOrder, then it queries AppraisalDates to get the relevant dates, then
it
> queries OrderFees, get the relevant fees and then it cycles to the next
> record in TblOrder. Big timeout problems.
> The only solution I could come up with for this is to create a view that
> somehow rotates the relevant records from OrderFees and AppraisalDates and
> merges them with TblOrder so that we only have to do one query instead of
3
> separate queries. Is this an appropriate solution? I don't know that
this
> will buy us any "time".
> Any suggestions would be appreciated.
> Thanks!
> James
>

Rotational Query?

We're facing a large dilemma. We've rebuilt our entire database in a much
more "normalized" fashion than we had previously (the design was
outrageously bad before). Ultimately, we have the following scenario and
we're getting a huge timeout problem:
Tables:
TblOrder (large, central table, contains all "files", approximately
130,000 records)
OrderFees (joined to TblOrder by FileNumber field, contains FeeType and
Fee)
DateTypes (joined to AppraisalDates by DateType field, contains the
"name" of the Date)
AppraisalDates (joined to TblOrder by FileNumber field, contains
DateType Field and Date)
Right now we're doing a query that returns approximately 5000 records from
TblOrder, then it queries AppraisalDates to get the relevant dates, then it
queries OrderFees, get the relevant fees and then it cycles to the next
record in TblOrder. Big timeout problems.
The only solution I could come up with for this is to create a view that
somehow rotates the relevant records from OrderFees and AppraisalDates and
merges them with TblOrder so that we only have to do one query instead of 3
separate queries. Is this an appropriate solution? I don't know that this
will buy us any "time".
Any suggestions would be appreciated.
Thanks!
JamesPlease post DDL, sample INSERTs and required results so that we don't have
to guess.
http://www.aspfaq.com/etiquette.asp?id=5006
I don't understand why you would "query TblOrder... then query
AppraisalDates...", etc. Don't you just join these tables? You seem to be
describing a cursor.
--
David Portas
SQL Server MVP
--|||Is there a reason why you can't pull the records from all 4 tables in a
single query with joins?
From your description it looks like:
SELECT columns FROM
tblOrder JOIN OrderFees ON FileNumber...
tblOrder JOIN AppraisalDates ON FileNumber..
AppraisalDates JOIN DateTypes on DateType
The DDL and some INSERT commands for the various tables as well as the
output you would like to see would be helpful. Someone here could then
craft a query for you.
Rick Sawtell
MCT, MCSD, MCDBA
"James" <cppjames@.aol.com> wrote in message
news:%23Srk%23butEHA.3448@.TK2MSFTNGP09.phx.gbl...
> We're facing a large dilemma. We've rebuilt our entire database in a much
> more "normalized" fashion than we had previously (the design was
> outrageously bad before). Ultimately, we have the following scenario and
> we're getting a huge timeout problem:
> Tables:
> TblOrder (large, central table, contains all "files", approximately
> 130,000 records)
> OrderFees (joined to TblOrder by FileNumber field, contains FeeType
and
> Fee)
> DateTypes (joined to AppraisalDates by DateType field, contains the
> "name" of the Date)
> AppraisalDates (joined to TblOrder by FileNumber field, contains
> DateType Field and Date)
> Right now we're doing a query that returns approximately 5000 records from
> TblOrder, then it queries AppraisalDates to get the relevant dates, then
it
> queries OrderFees, get the relevant fees and then it cycles to the next
> record in TblOrder. Big timeout problems.
> The only solution I could come up with for this is to create a view that
> somehow rotates the relevant records from OrderFees and AppraisalDates and
> merges them with TblOrder so that we only have to do one query instead of
3
> separate queries. Is this an appropriate solution? I don't know that
this
> will buy us any "time".
> Any suggestions would be appreciated.
> Thanks!
> James
>|||Did you find an answer for this one? If not, post your queries and DDL and
we can help out.
Thanks,
Michael C., MCDBA
"James" <cppjames@.aol.com> wrote in message
news:%23Srk%23butEHA.3448@.TK2MSFTNGP09.phx.gbl...
> We're facing a large dilemma. We've rebuilt our entire database in a much
> more "normalized" fashion than we had previously (the design was
> outrageously bad before). Ultimately, we have the following scenario and
> we're getting a huge timeout problem:
> Tables:
> TblOrder (large, central table, contains all "files", approximately
> 130,000 records)
> OrderFees (joined to TblOrder by FileNumber field, contains FeeType
and
> Fee)
> DateTypes (joined to AppraisalDates by DateType field, contains the
> "name" of the Date)
> AppraisalDates (joined to TblOrder by FileNumber field, contains
> DateType Field and Date)
> Right now we're doing a query that returns approximately 5000 records from
> TblOrder, then it queries AppraisalDates to get the relevant dates, then
it
> queries OrderFees, get the relevant fees and then it cycles to the next
> record in TblOrder. Big timeout problems.
> The only solution I could come up with for this is to create a view that
> somehow rotates the relevant records from OrderFees and AppraisalDates and
> merges them with TblOrder so that we only have to do one query instead of
3
> separate queries. Is this an appropriate solution? I don't know that
this
> will buy us any "time".
> Any suggestions would be appreciated.
> Thanks!
> James
>

Rotate table query help

I need to join two tables I guess in a rotate fashion but have not
been able to figure it out. We have a Taxo_Status table that contains
up to 2 entries per species. These records need to be joined
Taxo_Status_LUT table such that one record is turned per species with
a state status description (category_code = "S") and a federal status
description (category_code = "F").
This is the result set I need
Species_Code State_Status_Desc Federal_Status_Desc
HALE Endangered Threatened
KALA null Sensitive
MAMU Endangered null
TAXO_STATUS Table
Species_Code Taxo_Listing_ID
HALE 3
HALE 5
MAMU 3
KALA 4
TAXO_STATUS_LUT Table
Taxo_Listing_ID Category_Code Status_Desc
1 S Sensitive
2 S Threatened
3 S Endangered
4 F Sensitive
5 F Threatened
6 F Endangeredselect ts.species_code,
max(case when category_code='S' then status_desc end) as
Status_Status_Desc,
max(case when category_code='F' then status_desc end) as
Federal_Status_Desc
from taxo_status ts
join taxo_status_lut tsl on ts.taxo_listing_id = tsl.taxo_listing_id
group by ts.species_code
order by ts.species_code
Randy K wrote:
> I need to join two tables I guess in a rotate fashion but have not
> been able to figure it out. We have a Taxo_Status table that contains
> up to 2 entries per species. These records need to be joined
> Taxo_Status_LUT table such that one record is turned per species with
> a state status description (category_code = "S") and a federal status
> description (category_code = "F").
> This is the result set I need
> Species_Code State_Status_Desc Federal_Status_Desc
> HALE Endangered Threatened
> KALA null Sensitive
> MAMU Endangered null
> --
> TAXO_STATUS Table
> Species_Code Taxo_Listing_ID
> HALE 3
> HALE 5
> MAMU 3
> KALA 4
> --
> TAXO_STATUS_LUT Table
> Taxo_Listing_ID Category_Code Status_Desc
> 1 S Sensitive
> 2 S Threatened
> 3 S Endangered
> 4 F Sensitive
> 5 F Threatened
> 6 F Endangered

Rotate table help

I need to join two tables I guess in a rotate fashion but have not
been able to figure it out. We have a Taxo_Status table that contains
up to 2 entries per species. These records need to be joined
Taxo_Status_LUT table such that one record is turned per species with
a state status description (category_code = "S") and a federal status
description (category_code = "F").
This is the sult set I need
Species_Code State_Status_Desc Federal_Status_Desc
HALE Endangered Threatened
KALA null Sensitive
MAMU Endangered null
TAXO_STATUS Table
Species_Code Taxo_Listing_ID
HALE 3
HALE 5
MAMU 3
KALA 4
TAXO_STATUS_LUT Table
Taxo_Listing_ID Category_Code Status_Desc
1 S Sensitive
2 S Threatened
3 S Endangered
4 F Sensitive
5 F Threatened
6 F EndangeredHopefully this query can help you:
select c.species_code, max(c.State_Status_Desc) as 'State_Status_Desc',
max(c.Federal_Status_Desc) as 'Federal_Status_Desc'
from
(select a.species_code,
case when b.Category_Code = 'S' then b.Status_Desc else null end as
'State_Status_Desc',
case when b.Category_Code = 'F' then b.Status_Desc else null end as
'Federal_Status_Desc'
from
#TAXO_STATUS a , #TAXO_STATUS_LUT b
where a.Taxo_Listing_ID = b.Taxo_Listing_ID) c
group by c.species_code
"Randy K" wrote:

> I need to join two tables I guess in a rotate fashion but have not
> been able to figure it out. We have a Taxo_Status table that contains
> up to 2 entries per species. These records need to be joined
> Taxo_Status_LUT table such that one record is turned per species with
> a state status description (category_code = "S") and a federal status
> description (category_code = "F").
> This is the sult set I need
> Species_Code State_Status_Desc Federal_Status_Desc
> HALE Endangered Threatened
> KALA null Sensitive
> MAMU Endangered null
> --
> TAXO_STATUS Table
> Species_Code Taxo_Listing_ID
> HALE 3
> HALE 5
> MAMU 3
> KALA 4
> --
> TAXO_STATUS_LUT Table
> Taxo_Listing_ID Category_Code Status_Desc
> 1 S Sensitive
> 2 S Threatened
> 3 S Endangered
> 4 F Sensitive
> 5 F Threatened
> 6 F Endangered
>|||Thanks to Trey my query has been solved
select ts.species_code,
max(case when category_code='S' then status_desc end) as
Status_Status_Desc,
max(case when category_code='F' then status_desc end) as
Federal_Status_Desc
from taxo_status ts
join taxo_status_lut tsl on ts.taxo_listing_id =
tsl.taxo_listing_id
group by ts.species_code
order by ts.species_code
On Fri, 18 Nov 2005 18:41:35 GMT, wawork@.hotmail.com (Randy K) wrote:

>I need to join two tables I guess in a rotate fashion but have not
>been able to figure it out. We have a Taxo_Status table that contains
>up to 2 entries per species. These records need to be joined
>Taxo_Status_LUT table such that one record is turned per species with
>a state status description (category_code = "S") and a federal status
>description (category_code = "F").
>This is the sult set I need
>Species_Code State_Status_Desc Federal_Status_Desc
> HALE Endangered Threatened
> KALA null Sensitive
> MAMU Endangered null
>--
>TAXO_STATUS Table
>Species_Code Taxo_Listing_ID
>HALE 3
>HALE 5
>MAMU 3
>KALA 4
>--
>TAXO_STATUS_LUT Table
>Taxo_Listing_ID Category_Code Status_Desc
> 1 S Sensitive
> 2 S Threatened
> 3 S Endangered
> 4 F Sensitive
> 5 F Threatened
> 6 F Endangered

Rotate table help

I need to join two tables I guess in a rotate fashion but have not
been able to figure it out. We have a Taxo_Status table that contains
up to 2 entries per species. These records need to be joined
Taxo_Status_LUT table such that one record is turned per species with
a state status description (category_code = "S") and a federal status
description (category_code = "F").
This is the sult set I need
Species_Code State_Status_Desc Federal_Status_Desc
HALE Endangered Threatened
KALA null Sensitive
MAMU Endangered null
TAXO_STATUS Table
Species_Code Taxo_Listing_ID
HALE 3
HALE 5
MAMU 3
KALA 4
TAXO_STATUS_LUT Table
Taxo_Listing_ID Category_Code Status_Desc
1 S Sensitive
2 S Threatened
3 S Endangered
4 F Sensitive
5 F Threatened
6 F Endangered
Hopefully this query can help you:
select c.species_code, max(c.State_Status_Desc) as 'State_Status_Desc',
max(c.Federal_Status_Desc) as 'Federal_Status_Desc'
from
(select a.species_code,
case when b.Category_Code = 'S' then b.Status_Desc else null end as
'State_Status_Desc',
case when b.Category_Code = 'F' then b.Status_Desc else null end as
'Federal_Status_Desc'
from
#TAXO_STATUS a , #TAXO_STATUS_LUT b
where a.Taxo_Listing_ID = b.Taxo_Listing_ID) c
group by c.species_code
"Randy K" wrote:

> I need to join two tables I guess in a rotate fashion but have not
> been able to figure it out. We have a Taxo_Status table that contains
> up to 2 entries per species. These records need to be joined
> Taxo_Status_LUT table such that one record is turned per species with
> a state status description (category_code = "S") and a federal status
> description (category_code = "F").
> This is the sult set I need
> Species_Code State_Status_Desc Federal_Status_Desc
> HALE Endangered Threatened
> KALA null Sensitive
> MAMU Endangered null
> --
> TAXO_STATUS Table
> Species_Code Taxo_Listing_ID
> HALE 3
> HALE 5
> MAMU 3
> KALA 4
> --
> TAXO_STATUS_LUT Table
> Taxo_Listing_ID Category_Code Status_Desc
> 1 S Sensitive
> 2 S Threatened
> 3 S Endangered
> 4 F Sensitive
> 5 F Threatened
> 6 F Endangered
>
|||Thanks to Trey my query has been solved
select ts.species_code,
max(case when category_code='S' then status_desc end) as
Status_Status_Desc,
max(case when category_code='F' then status_desc end) as
Federal_Status_Desc
from taxo_status ts
join taxo_status_lut tsl on ts.taxo_listing_id =
tsl.taxo_listing_id
group by ts.species_code
order by ts.species_code
On Fri, 18 Nov 2005 18:41:35 GMT, wawork@.hotmail.com (Randy K) wrote:

>I need to join two tables I guess in a rotate fashion but have not
>been able to figure it out. We have a Taxo_Status table that contains
>up to 2 entries per species. These records need to be joined
>Taxo_Status_LUT table such that one record is turned per species with
>a state status description (category_code = "S") and a federal status
>description (category_code = "F").
>This is the sult set I need
>Species_Code State_Status_Desc Federal_Status_Desc
> HALE Endangered Threatened
> KALA null Sensitive
> MAMU Endangered null
>--
>TAXO_STATUS Table
>Species_Code Taxo_Listing_ID
>HALE 3
>HALE 5
>MAMU 3
>KALA 4
>--
>TAXO_STATUS_LUT Table
>Taxo_Listing_ID Category_Code Status_Desc
> 1 S Sensitive
> 2 S Threatened
> 3 S Endangered
> 4 F Sensitive
> 5 F Threatened
> 6 F Endangered

Rotate table help

I need to join two tables I guess in a rotate fashion but have not
been able to figure it out. We have a Taxo_Status table that contains
up to 2 entries per species. These records need to be joined
Taxo_Status_LUT table such that one record is turned per species with
a state status description (category_code = "S") and a federal status
description (category_code = "F").
This is the sult set I need
Species_Code State_Status_Desc Federal_Status_Desc
HALE Endangered Threatened
KALA null Sensitive
MAMU Endangered null
--
TAXO_STATUS Table
Species_Code Taxo_Listing_ID
HALE 3
HALE 5
MAMU 3
KALA 4
--
TAXO_STATUS_LUT Table
Taxo_Listing_ID Category_Code Status_Desc
1 S Sensitive
2 S Threatened
3 S Endangered
4 F Sensitive
5 F Threatened
6 F EndangeredHopefully this query can help you:
select c.species_code, max(c.State_Status_Desc) as 'State_Status_Desc',
max(c.Federal_Status_Desc) as 'Federal_Status_Desc'
from
(select a.species_code,
case when b.Category_Code = 'S' then b.Status_Desc else null end as
'State_Status_Desc',
case when b.Category_Code = 'F' then b.Status_Desc else null end as
'Federal_Status_Desc'
from
#TAXO_STATUS a , #TAXO_STATUS_LUT b
where a.Taxo_Listing_ID = b.Taxo_Listing_ID) c
group by c.species_code
"Randy K" wrote:
> I need to join two tables I guess in a rotate fashion but have not
> been able to figure it out. We have a Taxo_Status table that contains
> up to 2 entries per species. These records need to be joined
> Taxo_Status_LUT table such that one record is turned per species with
> a state status description (category_code = "S") and a federal status
> description (category_code = "F").
> This is the sult set I need
> Species_Code State_Status_Desc Federal_Status_Desc
> HALE Endangered Threatened
> KALA null Sensitive
> MAMU Endangered null
> --
> TAXO_STATUS Table
> Species_Code Taxo_Listing_ID
> HALE 3
> HALE 5
> MAMU 3
> KALA 4
> --
> TAXO_STATUS_LUT Table
> Taxo_Listing_ID Category_Code Status_Desc
> 1 S Sensitive
> 2 S Threatened
> 3 S Endangered
> 4 F Sensitive
> 5 F Threatened
> 6 F Endangered
>|||Thanks to Trey my query has been solved
select ts.species_code,
max(case when category_code='S' then status_desc end) as
Status_Status_Desc,
max(case when category_code='F' then status_desc end) as
Federal_Status_Desc
from taxo_status ts
join taxo_status_lut tsl on ts.taxo_listing_id =tsl.taxo_listing_id
group by ts.species_code
order by ts.species_code
On Fri, 18 Nov 2005 18:41:35 GMT, wawork@.hotmail.com (Randy K) wrote:
>I need to join two tables I guess in a rotate fashion but have not
>been able to figure it out. We have a Taxo_Status table that contains
>up to 2 entries per species. These records need to be joined
>Taxo_Status_LUT table such that one record is turned per species with
>a state status description (category_code = "S") and a federal status
>description (category_code = "F").
>This is the sult set I need
>Species_Code State_Status_Desc Federal_Status_Desc
> HALE Endangered Threatened
> KALA null Sensitive
> MAMU Endangered null
>--
>TAXO_STATUS Table
>Species_Code Taxo_Listing_ID
>HALE 3
>HALE 5
>MAMU 3
>KALA 4
>--
>TAXO_STATUS_LUT Table
>Taxo_Listing_ID Category_Code Status_Desc
> 1 S Sensitive
> 2 S Threatened
> 3 S Endangered
> 4 F Sensitive
> 5 F Threatened
> 6 F Endangered