Mental Block on SQL query. Joining one to many with one of the many

Sorry for the akward title, not sure how to say this. I have a Person table and a addresses table. Each person may have many address records as shown below:

Person

--

PersonID | AutoNum

fName | varchar

lName | varchar

...etc

Addresses

addressID | AutoNum

personID | int (FK)

address1 | varchar

city | varchar

state | varchar

isPrimary | bit

...etc

What I'm trying to do is select all from Person and the city and state of each person's primary address. A little voice keeps saying subquery...but I can't figure it out. So far I have the SQL below, but if there is no address or no address where isPrimary = 1, it fails to return the person record. I need the person record regardless of if they have a primary address. Does that make sense?

Doesn't return all Person records:

SELECT Person.*, Address.City, Address.State

FROM Person LEFT OUTER JOIN Address

ON Person.PersonID = Address.PersonID
WHERE (Address.isPrimary= 1)
ORDER BY Person.lName, Person.fName

Try:

Code Snippet

SELECT Person.*, Address.City, Address.State, Address.isPrimary

FROM Person LEFT OUTER JOIN Address

ON Person.PersonID = Address.PersonID
AND (Address.isPrimary= 1)
ORDER BY Person.lName, Person.fName

|||Perfect, thank you.

Mental Block

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" <topguy75@.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.|||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 null
s
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 equall
y.
Abhishek
"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
>
>|||Thanks for your assistance, suprising what a bit of organising acheives. it
was '.m'
"Chris2" <rainofsteel.NOTVALID@.GETRIDOF.luminousrain.com> wrote in message
news:vaKdnYt0T5kyIu7eRVn-sg@.comcast.com...
> "John" <topguy75@.hotmail.com> wrote in message
> news:437395f5$0$23285$db0fefd9@.news.zen.co.uk...
> columns,
> CONVERT(int,
> CONVERT(varchar,
> dbo.jp_tblproject_stats.Mnth =
> dbo.jp_tblproject_stats.Counter,
> CONVERT(varchar,
> CONVERT(varchar,
> 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.
>

Memory problem.

Im having a problem with SQL Server fragmenting memory and not being able to obtain a large enough block of contiguous memory for some operations to run.

I have a number of stored procedures which, using UDFs call extended stored procedures, which in turn call COM objects (see UDF code below). Over a period of some weeks use memory seems to become fragmented and the stored procedures will error due to lack of contiguous memory.

BEGIN

DECLARE @.objPCPlus int
DECLARE @.Result int

--Create the object using the in built stored procedure
EXEC sp_OACreate 'AFDUtilX.Utility', @.objPCPlus OUT,4

--set the Postcode property of the object
EXEC sp_OASetProperty @.objPCPlus, 'Postcode', @.strPostcode

--Initiate the CheckPostcode routine
EXEC sp_OAMethod @.objPCPlus, 'CheckPostcode'

EXEC sp_OAGetProperty @.objPCPlus, 'Result', @.Result OUT

EXEC sp_OADestroy @.objPCPlus OUT

Return @.Result

END

Due to the many connections which would have to be re-established and its 24-7 use stopping and starting SQL Server is not an ideal solution.

Is there any way of forcing a memory clear out without stopping SQL Server that I can schedule to run at a low demand time?Who's 'AFDUtilX.Utility' and is it bug-free in respect to all the calls to it that you mentioned?