anyone good with queries

Hi below is a simplified database table setup that I have but basically it is
3 tables that are joined and one table has a date in it. Also in all 3
tables is a value maxi and I need to retrieve 1 maxi value for each day from
the query. If the value is NULL in table A I want to get the value from
table B and if it is null in table B I want to get it from table C. If it is
Null in all 3 tables I will just use 0 for the value. So use maxi from table
A if not null, but if null check table b and use maxi from that table if not
null, then if null use value from table c for maxi.
tableA
******************************************
pri key * color_id * size_id * date* maxi *
******************************************
* 1 * 2 * 5 * 2/1/07* 7 *
******************************************
* 2 * 4 * 6 * 2/2/07* NULL *
******************************************
tableB
****************************
* color id * color name * maxi *
****************************
* 2 * blue * 6 *
****************************
* 4 * red * NULL *
***************************
tableC
****************************
* size id * color name * maxi *
****************************
* 5 * small * 6 *
****************************
* 6 * medium * 10 *
***************************
so a query for a date range of 2/1/07 to 2/2/07 would return
2 records and maxi would be 7 for 2/1/07 from table A and 10 from
table C for 2/2/07.
Thanks
Paul G
Software engineer.
Try:
select
coalesce (a.maxi, b.maxi, c.maxi, 0)
from
table A a
left join
tableB b on b.[pri key] = a.[pri key]
left join
tableC c on c.[pri key] = a.[pri key]
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:41785FDC-FBEF-4A50-863F-6C96A51ABE76@.microsoft.com...
Hi below is a simplified database table setup that I have but basically it
is
3 tables that are joined and one table has a date in it. Also in all 3
tables is a value maxi and I need to retrieve 1 maxi value for each day from
the query. If the value is NULL in table A I want to get the value from
table B and if it is null in table B I want to get it from table C. If it
is
Null in all 3 tables I will just use 0 for the value. So use maxi from
table
A if not null, but if null check table b and use maxi from that table if not
null, then if null use value from table c for maxi.
tableA
******************************************
pri key * color_id * size_id * date* maxi *
******************************************
* 1 * 2 * 5 * 2/1/07* 7 *
******************************************
* 2 * 4 * 6 * 2/2/07* NULL *
******************************************
tableB
****************************
* color id * color name * maxi *
****************************
* 2 * blue * 6 *
****************************
* 4 * red * NULL *
***************************
tableC
****************************
* size id * color name * maxi *
****************************
* 5 * small * 6 *
****************************
* 6 * medium * 10 *
***************************
so a query for a date range of 2/1/07 to 2/2/07 would return
2 records and maxi would be 7 for 2/1/07 from table A and 10 from
table C for 2/2/07.
Thanks
Paul G
Software engineer.
|||it works! thanks
Paul G
Software engineer.
"Tom Moreau" wrote:

> Try:
> select
> coalesce (a.maxi, b.maxi, c.maxi, 0)
> from
> table A a
> left join
> tableB b on b.[pri key] = a.[pri key]
> left join
> tableC c on c.[pri key] = a.[pri key]
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:41785FDC-FBEF-4A50-863F-6C96A51ABE76@.microsoft.com...
> Hi below is a simplified database table setup that I have but basically it
> is
> 3 tables that are joined and one table has a date in it. Also in all 3
> tables is a value maxi and I need to retrieve 1 maxi value for each day from
> the query. If the value is NULL in table A I want to get the value from
> table B and if it is null in table B I want to get it from table C. If it
> is
> Null in all 3 tables I will just use 0 for the value. So use maxi from
> table
> A if not null, but if null check table b and use maxi from that table if not
> null, then if null use value from table c for maxi.
> tableA
> ******************************************
> pri key * color_id * size_id * date* maxi *
> ******************************************
> * 1 * 2 * 5 * 2/1/07* 7 *
> ******************************************
> * 2 * 4 * 6 * 2/2/07* NULL *
> ******************************************
> tableB
> ****************************
> * color id * color name * maxi *
> ****************************
> * 2 * blue * 6 *
> ****************************
> * 4 * red * NULL *
> ***************************
> tableC
> ****************************
> * size id * color name * maxi *
> ****************************
> * 5 * small * 6 *
> ****************************
> * 6 * medium * 10 *
> ***************************
> so a query for a date range of 2/1/07 to 2/2/07 would return
> 2 records and maxi would be 7 for 2/1/07 from table A and 10 from
> table C for 2/2/07.
> Thanks
> --
> Paul G
> Software engineer.
>