Query Help

[Karl A. Homburg]

I have a query below that returns a table with some client information along
with the last project manager that met with them and the date they met.
Nothing is included in the table if there are no meetings for that client.
Is there a way that I can still have the client information included even if
there are no meetings?

SELECT T.ContactIDNum, dbo.ContactView .Organization, dbo.ContactView .Name,
dbo.ContactView .UPEAPM, T.UPEAContact, T.[Date]
FROM dbo.MeetingView T INNER JOIN
dbo.ContactView ON T.ContactIDNum =
dbo.ContactView .ContactIDNum
WHERE (T.[Date] =
(SELECT MAX([Date])
FROM Meeting
WHERE ContactIDNum = T.ContactIDNum) )

--
--
Karl A. Homburg
Electrical Engineer
U.P. Engineers & Architects, Inc.
100 Portage Street, Houghton, MI 49931
PH: (906) 482-4810 FX: (906) 482-9799

[Erland Sommarskog]

Re: Query Help

Karl A. Homburg (k-n-o-s-p-a-m-homburg@upea.co m) writes:[color=blue]
> I have a query below that returns a table with some client information
> along with the last project manager that met with them and the date they
> met. Nothing is included in the table if there are no meetings for that
> client. Is there a way that I can still have the client information
> included even if there are no meetings?
>
> SELECT T.ContactIDNum, dbo.ContactView .Organization, dbo.ContactView .Name,
> dbo.ContactView .UPEAPM, T.UPEAContact, T.[Date]
> FROM dbo.MeetingView T INNER JOIN
> dbo.ContactView ON T.ContactIDNum =
> dbo.ContactView .ContactIDNum
> WHERE (T.[Date] =
> (SELECT MAX([Date])
> FROM Meeting
> WHERE ContactIDNum = T.ContactIDNum) )[/color]

For this type of query, it is always helpful to include CREATE TABLE
statements of your tables, and INSERT statements with sample data
and finally the desired output from the sample. Failing to provide
that increases the risk that you answer is based on a fair amount of
guesswork, like this suggestion:

SELECT T.ContactIDNum, C.Organization, C.Name,
C.UPEAPM, T.UPEAContact, T.[Date]
FROM dbo.ContactView C
LEFT JOIN dbo.MeetingView T
ON T.ContactIDNum = C.ContactIDNum
AND (T.[Date] = (SELECT MAX([Date])
FROM Meeting M
WHERE M.ContactIDNum = T.ContactIDNum) )


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Hugo Kornelis]

Re: Query Help

On Wed, 25 Aug 2004 15:40:03 -0400, Karl A. Homburg wrote:
[color=blue]
>I have a query below that returns a table with some client information along
>with the last project manager that met with them and the date they met.
>Nothing is included in the table if there are no meetings for that client.
>Is there a way that I can still have the client information included even if
>there are no meetings?
>
>SELECT T.ContactIDNum, dbo.ContactView .Organization, dbo.ContactView .Name,
>dbo.ContactVie w.UPEAPM, T.UPEAContact, T.[Date]
>FROM dbo.MeetingView T INNER JOIN
> dbo.ContactView ON T.ContactIDNum =
>dbo.ContactVie w.ContactIDNum
>WHERE (T.[Date] =
> (SELECT MAX([Date])
> FROM Meeting
> WHERE ContactIDNum = T.ContactIDNum) )[/color]

Hi Karl,

SELECT T.ContactIDNum, dbo.ContactView .Organization,
dbo.ContactView .Name, dbo.ContactView .UPEAPM,
T.UPEAContact, T.[Date]
FROM dbo.MeetingView T
RIGHT JOIN dbo.ContactView
ON T.ContactIDNum = dbo.ContactView .ContactIDNum
AND T.[Date] = (SELECT MAX([Date])
FROM Meeting
WHERE ContactIDNum = T.ContactIDNum) )
(untested)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Ross Presser]

Re: Query Help

On Wed, 25 Aug 2004 15:40:03 -0400, Karl A. Homburg wrote:
[color=blue]
> I have a query below that returns a table with some client information along
> with the last project manager that met with them and the date they met.
> Nothing is included in the table if there are no meetings for that client.
> Is there a way that I can still have the client information included even if
> there are no meetings?
>
> SELECT T.ContactIDNum, dbo.ContactView .Organization, dbo.ContactView .Name,
> dbo.ContactView .UPEAPM, T.UPEAContact, T.[Date]
> FROM dbo.MeetingView T INNER JOIN
> dbo.ContactView ON T.ContactIDNum =
> dbo.ContactView .ContactIDNum
> WHERE (T.[Date] =
> (SELECT MAX([Date])
> FROM Meeting
> WHERE ContactIDNum = T.ContactIDNum) )[/color]

try

SELECT T.ContactIDNum, dbo.ContactView .Organization, dbo.ContactView .Name,
dbo.ContactView .UPEAPM, T.UPEAContact, T.[Date]
FROM dbo.MeetingView T
RIGHT JOIN dbo.ContactView ON T.ContactIDNum = dbo.ContactView .ContactIDNum
WHERE (T.[Date] IS NULL
OR T.[Date] =
(SELECT MAX([Date])
FROM Meeting
WHERE ContactIDNum = T.ContactIDNum) )

[Karl A. Homburg]

Re: Query Help

This one almost works. The only problem is that the ClientIDNum for the
rows that do not have any meetings shows up as null.

--
--
Karl A. Homburg
Electrical Engineer
U.P. Engineers & Architects, Inc.
100 Portage Street, Houghton, MI 49931
PH: (906) 482-4810 FX: (906) 482-9799
"Ross Presser" <rpresser@imtek .com> wrote in message
news:xw2983mmhl yy.1liq73j8yi7i 1$.dlg@40tude.n et...[color=blue]
> On Wed, 25 Aug 2004 15:40:03 -0400, Karl A. Homburg wrote:
>[color=green]
>> I have a query below that returns a table with some client information
>> along
>> with the last project manager that met with them and the date they met.
>> Nothing is included in the table if there are no meetings for that
>> client.
>> Is there a way that I can still have the client information included even
>> if
>> there are no meetings?
>>
>> SELECT T.ContactIDNum, dbo.ContactView .Organization,
>> dbo.ContactView .Name,
>> dbo.ContactView .UPEAPM, T.UPEAContact, T.[Date]
>> FROM dbo.MeetingView T INNER JOIN
>> dbo.ContactView ON T.ContactIDNum =
>> dbo.ContactView .ContactIDNum
>> WHERE (T.[Date] =
>> (SELECT MAX([Date])
>> FROM Meeting
>> WHERE ContactIDNum = T.ContactIDNum) )[/color]
>
> try
>
> SELECT T.ContactIDNum, dbo.ContactView .Organization, dbo.ContactView .Name,
> dbo.ContactView .UPEAPM, T.UPEAContact, T.[Date]
> FROM dbo.MeetingView T
> RIGHT JOIN dbo.ContactView ON T.ContactIDNum =
> dbo.ContactView .ContactIDNum
> WHERE (T.[Date] IS NULL
> OR T.[Date] =
> (SELECT MAX([Date])
> FROM Meeting
> WHERE ContactIDNum = T.ContactIDNum) )[/color]

[Hugo Kornelis]

Re: Query Help

On Wed, 25 Aug 2004 18:55:46 -0400, Karl A. Homburg wrote:
[color=blue]
>This one almost works. The only problem is that the ClientIDNum for the
>rows that do not have any meetings shows up as null.[/color]

Hi Karl,

Do the suggestions of Erland and me "almost work" as well?

Displaying the ClientIDNum for the rows without any meeting can be
achieved by replacing T.ContactIDNum (in the SELECT list) with
dbo.ContactView .ContactIDNum.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)