Mental Block

10 Nov 2005 6:48 PM

John

Can some please help with this SQL problem:

I have tables in select query and i want to show all
dbo.TblTerritory.Description even is there is no data in the other columns,
ie there are no records of type 'lost' in ProjectStatus

SELECT     TOP 100 PERCENT dbo.TblTerritory.Description,
dbo.jp_tblproject_stats.Counter, dbo.tblCalendar_jp.monthname, CONVERT(int,
CONVERT(varchar,
                      dbo.jp_tblproject_stats.Mnth) + CONVERT(varchar,
dbo.jp_tblproject_stats.Yr)) AS dtsort
FROM         dbo.jp_tblproject_stats INNER JOIN
                      dbo.tblCalendar_jp ON dbo.jp_tblproject_stats.Mnth =
dbo.tblCalendar_jpM RIGHT OUTER JOIN
                      dbo.TblTerritory ON
dbo.jp_tblproject_stats.Territory_Code = dbo.TblTerritory.Code
WHERE     (dbo.TblTerritory.Manager = 'clive wallom') AND
(dbo.jp_tblproject_stats.ProjectStatus = 'lost')
GROUP BY dbo.TblTerritory.Description, dbo.jp_tblproject_stats.Counter,
dbo.tblCalendar_jp.monthname, CONVERT(int, CONVERT(varchar,
                      dbo.jp_tblproject_stats.Mnth) + CONVERT(varchar,
dbo.jp_tblproject_stats.Yr))
ORDER BY dbo.TblTerritory.Description, CONVERT(int, CONVERT(varchar,
dbo.jp_tblproject_stats.Mnth) + CONVERT(varchar,
dbo.jp_tblproject_stats.Yr))

pleae help

Regards

John

10 Nov 2005 10:17 PM

Abhishek Pandey

John:

When there is a requirement of having all row returned for a particular
column no matter if it has records in other tables you are joining to, one
should be very careful in putting in the where clause.

When your where clause is not properly designed, it will filter out the rows
having null value in that column. You can modify your where clause to let it
include null values or you can put these condition while you join two tables.
(the way Chris suggested you)

Putting this in ON conditions while you are joining two table at times might
become quite complex.
An easier way would be to modify the where clause to let it include the nulls
say for example:

instead of ( dbo.TblTerritory.Manager = 'clive wallom' )
you cna put ( dbo.TblTerritory.Manager = 'clive wallom'
or dbo.TblTerritory.Manager is null)

Either of the approach will give you expected result and will perform equally.

Abhishek
Show quote

"John" wrote:

> Can some please help with this SQL problem:
>
> I have tables in select query and i want to show all
> dbo.TblTerritory.Description even is there is no data in the other columns,
> ie there are no records of type 'lost' in ProjectStatus
>
> SELECT     TOP 100 PERCENT dbo.TblTerritory.Description,
> dbo.jp_tblproject_stats.Counter, dbo.tblCalendar_jp.monthname, CONVERT(int,
> CONVERT(varchar,
>                       dbo.jp_tblproject_stats.Mnth) + CONVERT(varchar,
> dbo.jp_tblproject_stats.Yr)) AS dtsort
> FROM         dbo.jp_tblproject_stats INNER JOIN
>                       dbo.tblCalendar_jp ON dbo.jp_tblproject_stats.Mnth =
> dbo.tblCalendar_jpM RIGHT OUTER JOIN
>                       dbo.TblTerritory ON
> dbo.jp_tblproject_stats.Territory_Code = dbo.TblTerritory.Code
> WHERE     (dbo.TblTerritory.Manager = 'clive wallom') AND
> (dbo.jp_tblproject_stats.ProjectStatus = 'lost')
> GROUP BY dbo.TblTerritory.Description, dbo.jp_tblproject_stats.Counter,
> dbo.tblCalendar_jp.monthname, CONVERT(int, CONVERT(varchar,
>                       dbo.jp_tblproject_stats.Mnth) + CONVERT(varchar,
> dbo.jp_tblproject_stats.Yr))
> ORDER BY dbo.TblTerritory.Description, CONVERT(int, CONVERT(varchar,
> dbo.jp_tblproject_stats.Mnth) + CONVERT(varchar,
> dbo.jp_tblproject_stats.Yr))
>
> pleae help
>
> Regards
>
> John
>
>
>

10 Nov 2005 10:49 PM

Chris2

Show quote

"John" <topgu***@hotmail.com> wrote in message
news:437395f5$0$23285$db0fefd9@news.zen.co.uk...
> Can some please help with this SQL problem:
>
> I have tables in select query and i want to show all
> dbo.TblTerritory.Description even is there is no data in the other
columns,
> ie there are no records of type 'lost' in ProjectStatus
>
> SELECT     TOP 100 PERCENT dbo.TblTerritory.Description,
> dbo.jp_tblproject_stats.Counter, dbo.tblCalendar_jp.monthname,
CONVERT(int,
> CONVERT(varchar,
>                       dbo.jp_tblproject_stats.Mnth) +
CONVERT(varchar,
> dbo.jp_tblproject_stats.Yr)) AS dtsort
> FROM         dbo.jp_tblproject_stats INNER JOIN
>                       dbo.tblCalendar_jp ON

dbo.jp_tblproject_stats.Mnth =

> dbo.tblCalendar_jpM RIGHT OUTER JOIN
> dbo.TblTerritory ON
> dbo.jp_tblproject_stats.Territory_Code = dbo.TblTerritory.Code
> WHERE (dbo.TblTerritory.Manager = 'clive wallom') AND
> (dbo.jp_tblproject_stats.ProjectStatus = 'lost')
> GROUP BY dbo.TblTerritory.Description,

dbo.jp_tblproject_stats.Counter,
Show quote

> dbo.tblCalendar_jp.monthname, CONVERT(int, CONVERT(varchar,
> dbo.jp_tblproject_stats.Mnth) +
CONVERT(varchar,
> dbo.jp_tblproject_stats.Yr))
> ORDER BY dbo.TblTerritory.Description, CONVERT(int,
CONVERT(varchar,
> dbo.jp_tblproject_stats.Mnth) + CONVERT(varchar,
> dbo.jp_tblproject_stats.Yr))
>
> pleae help
>
> Regards
>
> John
>

John,

-- The query realigned and aliased for readability:

SELECT TOP 100 PERCENT
         T1.Description
        ,PS1.Counter
        ,C1.monthname
        ,CONVERT(int, CONVERT(varchar, PS1.Mnth)
       + CONVERT(varchar, PS1.Yr)) AS dtsort
    FROM dbo.jp_tblproject_stats AS PS1
         INNER JOIN
         dbo.tblCalendar_jp AS C1
--                  Note right here there is no column name
--                  On the right-hand side of the = operator,
--                  just the table alias.
      ON PS1.Mnth = C1
         RIGHT OUTER JOIN
         dbo.TblTerritory AS T1
      ON PS1.Territory_Code = T1.Code
   WHERE (T1.Manager = 'clive wallom')
     AND (PS1.ProjectStatus = 'lost')
GROUP BY T1.Description
        ,PS1.Counter
        ,C1.monthname
        ,CONVERT(int ,CONVERT(varchar, PS1.Mnth)
       + CONVERT(varchar, PS1.Yr))
ORDER BY T1.Description
        ,CONVERT(int, CONVERT(varchar, PS1.Mnth)
       + CONVERT(varchar, PS1.Yr))

The above originally appeared as:

    FROM dbo.jp_tblproject_stats
         INNER JOIN
         dbo.tblCalendar_jp
      ON dbo.jp_tblproject_stats.Mnth
       = dbo.tblCalendar_jpM
--                         .<some column name goes here>
--      Probably should be .Mnth

Sincerely,

Chris O.

11 Nov 2005 3:02 PM

John

Thanks for your assistance, suprising what a bit of organising acheives. it
was '.m'

Show quote

"Chris2" <rainofsteel.NOTVA***@GETRIDOF.luminousrain.com> wrote in message
news:vaKdnYt0T5kyIu7eRVn-sg@comcast.com...
>
> "John" <topgu***@hotmail.com> wrote in message
> news:437395f5$0$23285$db0fefd9@news.zen.co.uk...
>> Can some please help with this SQL problem:
>>
>> I have tables in select query and i want to show all
>> dbo.TblTerritory.Description even is there is no data in the other
> columns,
>> ie there are no records of type 'lost' in ProjectStatus
>>
>> SELECT     TOP 100 PERCENT dbo.TblTerritory.Description,
>> dbo.jp_tblproject_stats.Counter, dbo.tblCalendar_jp.monthname,
> CONVERT(int,
>> CONVERT(varchar,
>>                       dbo.jp_tblproject_stats.Mnth) +
> CONVERT(varchar,
>> dbo.jp_tblproject_stats.Yr)) AS dtsort
>> FROM         dbo.jp_tblproject_stats INNER JOIN
>>                       dbo.tblCalendar_jp ON
> dbo.jp_tblproject_stats.Mnth =
>> dbo.tblCalendar_jpM RIGHT OUTER JOIN
>>                       dbo.TblTerritory ON
>> dbo.jp_tblproject_stats.Territory_Code = dbo.TblTerritory.Code
>> WHERE     (dbo.TblTerritory.Manager = 'clive wallom') AND
>> (dbo.jp_tblproject_stats.ProjectStatus = 'lost')
>> GROUP BY dbo.TblTerritory.Description,
> dbo.jp_tblproject_stats.Counter,
>> dbo.tblCalendar_jp.monthname, CONVERT(int, CONVERT(varchar,
>>                       dbo.jp_tblproject_stats.Mnth) +
> CONVERT(varchar,
>> dbo.jp_tblproject_stats.Yr))
>> ORDER BY dbo.TblTerritory.Description, CONVERT(int,
> CONVERT(varchar,
>> dbo.jp_tblproject_stats.Mnth) + CONVERT(varchar,
>> dbo.jp_tblproject_stats.Yr))
>>
>> pleae help
>>
>> Regards
>>
>> John
>>
>
> John,
>
> -- The query realigned and aliased for readability:
>
> SELECT TOP 100 PERCENT
>         T1.Description
>        ,PS1.Counter
>        ,C1.monthname
>        ,CONVERT(int, CONVERT(varchar, PS1.Mnth)
>       + CONVERT(varchar, PS1.Yr)) AS dtsort
>    FROM dbo.jp_tblproject_stats AS PS1
>         INNER JOIN
>         dbo.tblCalendar_jp AS C1
> --                  Note right here there is no column name
> --                  On the right-hand side of the = operator,
> --                  just the table alias.
>      ON PS1.Mnth = C1
>         RIGHT OUTER JOIN
>         dbo.TblTerritory AS T1
>      ON PS1.Territory_Code = T1.Code
>   WHERE (T1.Manager = 'clive wallom')
>     AND (PS1.ProjectStatus = 'lost')
> GROUP BY T1.Description
>        ,PS1.Counter
>        ,C1.monthname
>        ,CONVERT(int ,CONVERT(varchar, PS1.Mnth)
>       + CONVERT(varchar, PS1.Yr))
> ORDER BY T1.Description
>        ,CONVERT(int, CONVERT(varchar, PS1.Mnth)
>       + CONVERT(varchar, PS1.Yr))
>
> The above originally appeared as:
>
>    FROM dbo.jp_tblproject_stats
>         INNER JOIN
>         dbo.tblCalendar_jp
>      ON dbo.jp_tblproject_stats.Mnth
>       = dbo.tblCalendar_jpM
> --                         .<some column name goes here>
> --      Probably should be .Mnth
>
> Sincerely,
>
> Chris O.
>
>