Query Help

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-9799Karl A. Homburg (k-n-o-s-p-a-m-homburg@.upea.com) writes:
> 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))

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@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Wed, 25 Aug 2004 15:40:03 -0400, Karl A. Homburg wrote:

>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))

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)|||On Wed, 25 Aug 2004 15:40:03 -0400, Karl A. Homburg wrote:

> 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))

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))|||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:xw2983mmhlyy.1liq73j8yi7i1$.dlg@.40tude.net...
> On Wed, 25 Aug 2004 15:40:03 -0400, Karl A. Homburg wrote:
>> 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))
> 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))|||On Wed, 25 Aug 2004 18:55:46 -0400, Karl A. Homburg wrote:

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

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)

Query Editor standalone? Like Query analyzer

With Sql Server 2000 you could select and launch the query analyzer without going through the Enterprise Manager. Is there a method for doing the same thing with the new Query Editor, without going through the SqlServer Studio Manager?

Thanks;
Mark E. JohnsonThere is not, no.|||Mark,

Query Analyzer from SQL Server 2000 can connect to a SQL Server 2005 database and be used as before (Note that I can't give any advice on what licensing issues there may be). Not all of the Object Browser functionality works, but from your question, I suspect that's not as important. But the editor features, including graphical query plans, for example, work just fine against 2005.

Steve Kass
Drew University
SQL Server MVP
|||Thank you. What it really means for us, is that there will be a different method for pulling up the query tool. A discussion about what tools we really desire on a developers desktop etc will be in order.

Thanks again;

query cannot be executed

Hello. When I attempt to query a table I receive the
error from the SQL Enterprise Manager "The query cannot
be executed because some files are either missing or not
registered. Run setup again to make sure the required
files are regestered." This is the path that I use:
Enterprise Manager - sql server to attach to - select
database - select table - select to query all rows. Then
it looks like it is starting then the I get the error.
Please help!
I also have uninstalled & reinstalled the MDAC. Did not
correct it.
This is strange because it worked Friday & come back
Monday & it doesn't work?
I solved this problem by removing the registry key
HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\Set up\ExceptionComponents
and reinstalling MDAC 2.8.
Egon.
"Pete Scalercio" <pscalercio@.3w-interactive.com> wrote in message news:<b67f01c47986$5d69f460$a601280a@.phx.gbl>...
> Hello. When I attempt to query a table I receive the
> error from the SQL Enterprise Manager "The query cannot
> be executed because some files are either missing or not
> registered. Run setup again to make sure the required
> files are regestered."

Query by Example in Enterprise Manager

Why hasn't Microsoft adopted the same level of excellent QBE in Enterprise Manager that is included with MS Access for building queries? I am just not a die hard fan of using Query Analyzer and templates for building complex queries. I can understand using QA for performance tuning, etc. but for queries like UPDATE queries using RIGHT OUTER JOINS that have many attribute references, I miss my QBE in Access.

When I did my Masters, I wrote a research paper on Query By Example developed in the 70's by Dr. Mosh Zloof of IBM and there are some really good points about it.

1.) Visually seeing the relationships and joins.

2.) Elimination of typo errors.

3.) Eliminates reference ambiguity.

I don't mind keying in a complex query, but a nice visual tool with the relationships/joined mapped out and the elimination of reference ambituity would make me feel a little better before I hit the Execute Query button. Sometimes the Parse Query button does not exactly give me that warm fuzzy feeling but it helps.

Does anyone know if MS ever plans to adopt the level of QBE provided in MS Access or are there any third party tools available in comparison?

I'm not asking for help with keying in queries through Query Analyzer or any arguments about why that is so much greater. I'm just looking for QBE improvements in SQL server's Enterprise Manager.I understand what you are saying about visualizing the query and seeing the relationship. You are right in saying that MS Access does a good job of that, however, as a DBMS, Access just doesn't cut it. SQL Server is great.

If you are looking to visualize in SQL Server, there is a diagram tool under the database that you are working with so you can build relationships... you can also perform a visual query by right clicking on a table and selecting query from the popup menu. From there you can add tables and make relationships all by pointing and clicking... good thing to do if you aren't too sure about sql... it looks just like access's version and runs almost the same way.

But hey, look at it this way... in Access you are not in control... you simply control where you point and click and Access does the rest. In Query Analyzer, you are more than just a user ... you have flexibility and at the same time you are becoming more and more competant and confident in writing SQL and T-SQL... Remember, there is hardly anything that can't be fixed. So don't be afraid to click that button!!!!! Keep going at it and you'll be a SQL pro in no time!!!!!!|||Believe me, I've been working with SQL Server for 2 1/2 years but I still miss the QBE capability of Grid Analyzer in MS Access. For example, in SQL's Query Analyzer you can't add a joined table for an UPDATE query but you can for a SELECT. What's up with that? Seems pretty basic to me.|||Personally, I think it's funny that everyone balks at using QBE in EM for query building. I found that in Access, my percentage of reference ambiguity using Grid Manager was zero. However, when I use QA and templates, it's probably 5 to 25% and then I have to make corrections. To me, it's like comparing BASIC to VISUAL BASIC. Visual tools are awesome! Why people think it's such an insult to use them is beyond me. Just my two cents.

Query builder bug?

HI, in a lookup component, when I want to use the query builder, it will show tables from a different connection manager than the one I picked for being the lookup source. E.g. connection manager A points to a schema that contains table1 and table2 and connection manager B points to a schema that contains table 5 and table 6.

When I configure the lookup component to use connection manager B, I still see connection manager A tablles; isntead of seeing table 5 and 6 I see table 1 and 2.

Am I missing something?

Thank you for your help,
Cctoe

Please report the bug on the Product Feedback site.

Thanks.

Query builder bug?

HI, in a lookup component, when I want to use the query builder, it will show tables from a different connection manager than the one I picked for being the lookup source. E.g. connection manager A points to a schema that contains table1 and table2 and connection manager B points to a schema that contains table 5 and table 6.

When I configure the lookup component to use connection manager B, I still see connection manager A tablles; isntead of seeing table 5 and 6 I see table 1 and 2.

Am I missing something?

Thank you for your help,
Cctoe

Please report the bug on the Product Feedback site.

Thanks.

Query Assistance for Newbie

I need to modify a query through which I've done mostly using the Query
builder in Enterprise Manager so it can be run periodically without manual
editing. The query builder creates the following text:

select [ATHSTATDATA].[NSTATEVENTID] as "Page or
Search",[ATHSTATDATA].[NDATA] as "Hits if Search", [ATHSTATDATA].[VCDATA] as
"Topic or Search String", [ATHSTATDATA].[DTTIMESTAMP] as "Time of Activity"
from [ATHSTATDATA]
where [ATHSTATDATA].[DTTIMESTAMP]<'4/30/2004 12:00:00 AM' AND
[ATHSTATDATA].[DTTIMESTAMP]>'4/23/2004 12:00:00 AM'
order by [ATHSTATDATA].[DTTIMESTAMP]

What I need to do is to replace the specific dates (<'4/30/2004 12:00:00
AM', '4/23/2004 12:00:00 AM') so that I get only rows created from the time
at which the query is run and back 7 days. Thanks in advance for whatever
help you can provide!...
WHERE dttimestamp
BETWEEN CAST(DATEDIFF(DAY,7,CURRENT_TIMESTAMP) AS DATETIME)
AND CURRENT_TIMESTAMP

--
David Portas
SQL Server MVP
--|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> ...
> WHERE dttimestamp
> BETWEEN CAST(DATEDIFF(DAY,7,CURRENT_TIMESTAMP) AS DATETIME)
> AND CURRENT_TIMESTAMP

WHERE dttimestamp
BETWEEN dateadd(DAY, -7, CURRENT_TIMESTAMP) AND CURRENT_TIMESTAMP

Would be somewhat less cryptic, and not rely on undocumented
conversion between integer and datetime.

Now, Ray said:

so that I get only rows created from the time at which the query is run
and back 7 days.

Which the above does on the hour, but Roy's sample query indicated that
he wanted it by calender day. In such we should write:

BETWEEN dateadd(DAY, -7, convert(char(8), CURRENT_TIMESTAMP, 112) AND
CURRENT_TIMESTAMP

This still not match the original query, as this had < and >, but
I don't really know what Ray means with today, I have to stop there.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp