Query/View Question

I am trying to create a view that returns data from three tables and can't seem to get it to return the data that I want. I am no SQL expert, so hopefully someone can give me some insight into what I need to do.

The tables are basically set up like this:

TABLE 1

PrimaryKey

Textfield1

Textfield2

Textfield3

TABLE 2

PrimaryKey

Table1ForeignKey

Table3ForeignKey

Textfield1

TABLE 3

PrimaryKey

Textfield1

Textfield2

Textfield3

Table 1 and Table 3 are each joined to Table 2 on their respective Primary/Foreign Key fields.

I want the view to return all of the records from Table 1, even if there are no matching records in Table 2.

From Table 2 I only want the latest record for each record in Table 1.

I want the view to look something like this:

Table 1

PrimaryKey

Table1

Textfield1

Table2

Textfield

Table3

Textfield

In other words, I want to return one record in the view for each record in table 1, and I want the data from table 2 in each of those records to represent the last record added to table 2.

Can anyone enlighten me on the query necessary to get this view?

Hi,

some more questions:

how do you define "the latest" in table2 ?

HTH, Jens Suessmeyer,

http://www.sqlserver2005.de

|||Since the Primary Key field autoincrements, the 'latest' record from Table 2 will always be the max(table2.primarykey).|||

Perhaps my question will make more sense explained like this:

I will use an analogy of checking out books from the library.

Table 1 is a table of books, with a primary key of bookid.

Table 2 is a detail record of who withdrew the book, when, when it was returned, etc. with a primary key of DetailID and has a foreign key to Table 1 to identify the book as well as a foreign key to table 3 to identify who withdrew it.

Table 3 is a table of library card holders contact info with a primary key of CardholderID.

All of the primary keys are auto-incrementing.

I want the view to basically give me a snapshot of ALL books, and if it a particular book is currently withdrawn, I want to see who has it and when they checked it out.

I hope that makes more sense.

|||

OK, keeping your analogy in mind, the query should be like:

Select
T1.PrimaryKey,T1.TextField,
T2.PrimaryKey,T2.TextField,
T3.TextField
FROM Table1 T1
LEFT JOIN
(
SELECT Table1FK, Table3FK,Textfield
FROM Table2
INNER JOIN
(
SELECT MAX(PrimaryKey) as PK, Table1PK
FROM TABLE2
GROUP BY Table1PK
) SubQuery
ON Subquery.PK = Table2.PK
AND SubQuery.Table1PK = Table2.Table1PK
) T2
ON
T1.PrimaryKey = T2.Table1FK
INNER JOIN Table3 T3
ON T3.PrimaryKey = T2.Table1FK

untested....

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Query/Table Join

Hey guys,

I need to perform a query on two tables that look like this:

ATTENDANCE
attendanceid
memberid
meetingid
meetingdate

MEMBERS
memberid
firstname
lastaname

I can run this query just fine:

select m.memberid from members m, attendance a where
a.meetingid = 47 and m.memberid = a.memberid

This gives me memberid's for members that are present.

I need members that are not present.

select m.memberid from members m, attendance a where
a.meetingid = 47 and m.memberid <> a.memberid

This returns a thousand rows when it should return no more than 25.

I am sure this is just a simple join but I do not have my SQL for dummies book with me at the moment ;-)

Thanks,

MikeI guess this is what u r trying for.

select memberid from members where memberid not in(select memberid from attendance where meetingid = 47)

Originally posted by mycoolross
Hey guys,

I need to perform a query on two tables that look like this:

ATTENDANCE
attendanceid
memberid
meetingid
meetingdate

MEMBERS
memberid
firstname
lastaname

I can run this query just fine:

select m.memberid from members m, attendance a where
a.meetingid = 47 and m.memberid = a.memberid

This gives me memberid's for members that are present.

I need members that are not present.

select m.memberid from members m, attendance a where
a.meetingid = 47 and m.memberid <> a.memberid

This returns a thousand rows when it should return no more than 25.

I am sure this is just a simple join but I do not have my SQL for dummies book with me at the moment ;-)

Thanks,

Mike|||THANK YOU!!

Query/Reporting Tool for SQL Server Tables

Just a general question about any recommendations for a third party tool that would allow setting up a data-dictionary against SQL Server tables and a user could go in and build their own queries, reports. Perhaps they could save their query/report definitions for reuse. Other nice to haves - ability to output to PDF and export to XLS. Would prefer web interface, but can consider desktop as well. Any immediate recommendations?

Thanks.Allowing users direct access to tables is exactly the sort of thing good DBAs AVOID doing.|||Now, Now...

http://www.cognos.com/products/cognos8businessintelligence/reporting.html?lid=//Products//Cognos8BI//Reporting|||Brett - thanks for the product suggestion. That's the exact type of suggestion I'm looking for and welcome others.|||Well, I know when I've been snubbed... :(
Cognos is a good product, and has been around for a while. You might also look into DI Diver: http://www.dimensionalinsight.com/|||Blindman - sorry if you were snubbed.

And great thanks for suggesting another potential tool.

Obviously I have a lot to learn - and you and other experts on this board have been key in me understanding. I'm indebted to you and others and am sending cases of vitual beer and wild women your way.|||I would recommend stylereport http://www.inetsoft.com. Definitely the best reporting tool around.

query, special transpose and merge of data

hi all,

i'm a newbie with a big problem :). I want to create a query, which creates data in a merged and transposed way.

i have 3 tables, but only two of them are interesting for me now.

Table0:
F_id, prop1, prop2 ...
-------
10, x, y
20, v, d

Table1 (m:n Table, connecting Table0 with Table 2):
F_id, V_id
----
10, a
10, b
20, a

Table2:
V_id
--
a
b
c
d

the sql should create this result:
SQL Result:
F_id, a, b, c, d
-----
10, 1, 1, 0, 0
20, 1, 0, 0, 0

the tupels of Tabl2 with V_id should be transposed as columns names of the sql result.
Then every entry in the Table1 (m:n-Table) should insert a '1' in the column of the corresponding V_Id, otherwise if there is no connection between Table0 and Table2 in the m:n-Table, then there is a '0' to be inserted.

In the moment i have no clue, i read a lot about transposing and crosstab things, but that was no help for my special problem.

I appreciate any help.
Thanks!select T0.F_id
, sum(case when T1.V_id = a
then 1 else 0 end) as a
, sum(case when T1.V_id = b
then 1 else 0 end) as b
, sum(case when T1.V_id = c
then 1 else 0 end) as c
, sum(case when T1.V_id = d
then 1 else 0 end) as d
from Table0 as T0
inner
join Table1 as T1
on T1.F_id = T0.F_id
group
by T0.F_id|||Thanks. But the problem is that the Table1-entries are dynamic or the number of entries are variable.

Maybe i correct sth; its not really important to get all of these Table1-entries, but all entries from the m:n Table should be inserted.|||thanks, but you should have mentioned that in your initial post

would have saved me wasting my time writing sql that you can't use

:)|||Besides, this looks like a clasical "homework" assignment -- what have you done yourself to solve the problem? :mad:|||lkbrown, if the number of entries is variable, then this problem cannot be done with just sql

which is probably why he was posting|||if the number of entries is variable, then this problem cannot be done with just sql
It can, by using recursive SQL.
(Of course, one cannot return a "variable" number of columns, but a column can be returned which contains a variable amount of concatenations of expressions.)|||It can, by using recursive SQL.oh, please do show an example

and please make sure it is standard sql, not db2 or something proprietary

:)|||oh, please do show an example

and please make sure it is standard sql, not db2 or something proprietary
WITH T(F_id, aux, V_id) AS
( SELECT Table0.F_id,
MIN(Table1.V_id),
COALESCE(T.V_id, '') || ', ' || MIN(T1.V_id)
FROM Table0 AS T0 LEFT OUTER JOIN T ON T0.F_id = T.F_id
INNER JOIN Table1 AS T1 ON T0.F_id = T1.F_id
WHERE T.aux IS NULL or T1.V_id > T.aux
GROUP BY Table0.F_id
)
SELECT F_id, V_id
FROM T

Didn't test it, so there could be some minor tweaks ...)|||that's mighty impressive, i like it

but frankly, i get lost when i try to understand what it's doing

:)|||thanks a lot.
i tried to get it work, even though i didnt get it completely. I need a little time for it.|||I've tested the following and it works:
create table T0 ( f int ) ;
create table T1 ( f int , v char(1) ) ;
insert into T0(f) values(10) union all values(20) ;
insert into T1(f,v) values(10,'a') union all values(10,'b') ;
insert into T1(f,v) values(20,'b') union values(20,'c') union values(20,'d');

with T (f, v, aux) AS
(SELECT f, CAST('' AS varchar(255)), CAST(null AS varchar(255)) FROM T0
UNION ALL
SELECT T.f, T.v||', '||coalesce(T1.v, ''), coalesce(T1.v, '')
FROM T, T1
WHERE T.f = T1.f AND coalesce(T.aux, '') < T1.v
)
SELECT f, substr(v, 3)
FROM T AS Tx
WHERE length(v) = (SELECT max(length(v))
FROM T
WHERE T.f = Tx.f)
Quick explanation:
The "recursive" table T is built up as follows:
- First it's given all rows of table T0, i.e.
10, '', ''
20, '', ''
- Then the join of this table with T1 is added. The result is
10, '', ''
20, '', ''
10, ', a', 'a'
10, ', b', 'b'
20, ', b', 'b'
20, ', c', 'c'
20, ', d', 'd'
- This last step is iterated, but such that only rows of T and T1 are considered to be joined if T.aux (last column) is strictly smaller than T1.v .
Hence the following rows are added to T in step 3:
10, ', a, b', 'b'
20, ', b, c', 'c'
20, ', b, d', 'd'
20, ', c, d', 'd'
Finally (for the small tables used here) the row
20, ', b, c, d', 'd'
is added.
With this table T, the actual query (SELECT f, substr(v, 3) FROM T) is executed. The "substring" removes the leading ", " while the "WHERE" condition only keeps the longest strings in v, per f, i.e. the result is:
t | v
-- + ---
10 | a, b
20 | b, c, d|||So Sorry, that i didnt thanked you!
Thanks a lot Peter!!!! This was helping me out!!!!!!

Query works then later timeouts

We have a query which joins 3 tables where all 3 of the tables have less tha
n 300 rows each.
The query is fine when there is less than 50 users but when it gets over 50
it runs until it timeouts. During this time while it is running and not ret
urning I can do selects on each individual table but if I run the query with
the joins it never returnsWhat is the waittype for that spid during the slow times?
Andrew J. Kelly
SQL Server MVP
"Darin Browne" <anonymous@.discussions.microsoft.com> wrote in message
news:00EDE2AB-4CE0-4014-A0F6-18552B409DB1@.microsoft.com...
> We have a query which joins 3 tables where all 3 of the tables have less
than 300 rows each.
> The query is fine when there is less than 50 users but when it gets over
50 it runs until it timeouts. During this time while it is running and not
returning I can do selects on each individual table but if I run the query
with the joins it never returns.
> We've checked locks, if anything is blocking it, indexes used and
everything is fine during the never ending select.
> Any suggestions on what we could do? We're stumped.
> Thanks.
>

Query with multiple tables Use of JOIN vs WHERE

I have inherited a database, written a few years back and the people who
designed the stored procedures seem to do things differently than I learned
and it seems to work faster, but I cannot figure out why. I check the
execution plans and they appear identical, but the statistics show a HUGE
(to me) difference in reads (147 for the first method and 40 for the second)
. The second method takes about 10 seconds less to return the results. (O
f
course the code is wrapped in a stored procedure - but I was curious about
this "new" way of doing things so I extracted the select statements to do
comparisons).
I'm sure that the explanation is simple and is just an area of SQL coding I
hadn't been exposed to yet.
Any info appreciated,
Nancy
CODE:
It is a simple query to get a count joining 3 tables
I would normally do the query using:
Select count(CODE1)
from Table1
JOIN x_hcfa_cpt
ON
Table1.CLAIMNO = Table2.CLAIM_NUMBER
JOIN x_hcfa_cpt_mas
ON
Table1.TERM_NUMBER = Table3.TERM_NUMBER
where
Table1.TERM_NUMBER = 'asdftcdww'
AND
(Table1.STATUS='false'
or
Table1.STATUS='true')
and
Table1.FLAG='false'
But in the code I inherited they used:
Select count(CODE1)
from Table1, Table2, Table3
where
Table1.X_TERMINAL_NUMBER = 'asdftcdww'
AND
Table1.CLAIMNO = Table2.CLAIM_NUMBER
and
Table1.TERM_NUMBER = Table3.TERM_NUMBER
and
(Table1.STATUS='false'
or
Table1.STATUS='true')
and
Table1.FLAG ='false'> Select count(CODE1)
> from Table1, Table2, Table3
This is non-standard code and should be avoided (especially for outer joins
due to non-conforming behavior, but for inner joins as well). My suggestion
is to re-write the code with JOIN statements, and to avoid ambiguity, I make
it a standard practice to include the type of JOIN, so I would use the INNER
keyword as well (even though it is the default).|||There should be no difference between SQL-92 JOINs (1st statement) and
the older style (2nd statement).
I'm guessing that x_hcfa_cpt and x_hcfa_cpt_mas are Table2 and Table3 in
the first query?
Also, in the first query, the where clause uses Table1.TERM_NUMBER
whereas the 2nd query uses Table1.X_TERMINAL_NUMBER. Could that be the
difference in performance?
Nancy Lytle wrote:

>I have inherited a database, written a few years back and the people who
>designed the stored procedures seem to do things differently than I learned
>and it seems to work faster, but I cannot figure out why. I check the
>execution plans and they appear identical, but the statistics show a HUGE
>(to me) difference in reads (147 for the first method and 40 for the second
)
>. The second method takes about 10 seconds less to return the results. (O
f
>course the code is wrapped in a stored procedure - but I was curious about
>this "new" way of doing things so I extracted the select statements to do
>comparisons).
>I'm sure that the explanation is simple and is just an area of SQL coding I
>hadn't been exposed to yet.
>Any info appreciated,
>Nancy
>CODE:
>It is a simple query to get a count joining 3 tables
>I would normally do the query using:
>Select count(CODE1)
>from Table1
>JOIN Table2
>ON
>Table1.CLAIMNO = Table2.CLAIM_NUMBER
>JOIN Table3
>ON
>Table1.TERM_NUMBER = Table3.TERM_NUMBER
>where
>Table1.TERM_NUMBER = 'asdftcdww'
>AND
> (Table1.STATUS='false'
> or
> Table1.STATUS='true')
>and
> Table1.FLAG='false'
>But in the code I inherited they used:
>Select count(CODE1)
>from Table1, Table2, Table3
>where
>Table1.X_TERMINAL_NUMBER = 'asdftcdww'
>AND
>Table1.CLAIMNO = Table2.CLAIM_NUMBER
>and
>Table1.TERM_NUMBER = Table3.TERM_NUMBER
>and
>(Table1.STATUS='false'
>or
>Table1.STATUS='true')
>and
>Table1.FLAG ='false'
>
>|||On Tue, 13 Sep 2005 16:37:16 -0400, Nancy Lytle wrote:

>I have inherited a database, written a few years back and the people who
>designed the stored procedures seem to do things differently than I learned
>and it seems to work faster, but I cannot figure out why.
(snip)
Hi Nacny,
I'll assume that the different table names and column names are a result
of you renaming some tables and columns when preparing the post, and the
code you actually tested this on didn't have these differences :-)
The difference in the queries are the two different styles of join
notation. In old versions of SQL Server (note that I'm talking real old
here - older than SQL Server 6.5), only the version with the
comma-delimited list of tables is allowed. The more verbose version with
infixed join operators was added later, to adhere to the ANSI standard.
For inner joins, there is absolutely no difference between the two
versions. They are both defined in the ANSI standard, both acccepted by
SQL Server and they will both return the same results. They'll also use
the same execution plan, so that there's no performance difference
either.
For outer joins, things are different. The "old-style notation" (that
uses =* and *= in the WHERE clause to define inner and outer tables) is
ambiguous. It's not defined in the ANSI standard. MS has announced that
it will drop support for =* and *= in a future version. In fact, I
recall reading somewhere that SQL Server 2005 will only accept =* and *=
in the backward compatibility mode.

> I check the
>execution plans and they appear identical, but the statistics show a HUGE
>(to me) difference in reads (147 for the first method and 40 for the second
)
>. The second method takes about 10 seconds less to return the results.
Did you run both tests on an empty cache? I suspect not - and that's
what causes the difference.
Test it like this:
DECLARE @.start datetime
DECLARE @.end datetime
-- Flush all dirty buffers to disk
CHECKPOINT
-- Remove all previously read pages from the data cache
DBCC DROPCLEANBUFFERS
-- Remove all previously compiled execution plans as well
DBCC FREEPROCCACHE
-- Now start the real test
SET @.start = CURRENT_TIMESTAMP
#### ####
#### YOUR QUERY GOES HERE ####
#### ####
SET @.end = CURRENT_TIMESTAMP
SELECT @.start AS StartTime,
@.end AS EndTime,
DATEDIFF(ms, @.Start, @.End) AS "Elapsed (ms)"
If the code that you are testing returns many rows, change the SELECT to
a SELECT ... INTO #temp_table to eliminate the speed of the network and
the display speed of your client from the equation.
I'd be VERY surprised if you still get significant differences if you
test the queries like this. (Small differences are to be expected,
especially if the server you're running this on has other things to do
as well).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks, Hugo, Trey and Aaron!
I guess there is more sql code I am going to have to change, the SP's (and
they are all named sp_ !) are dotted with uses of comma delimited lists of
tables for joins and usage of *=, not to mention tons of select *'s, and the
sp_ naming convention.
I used Hugo query and that helped me see the real difference between the
two, which is actually very slight and leans toward the use of JOINs.
Thanks again, this is a great group!
Nancy
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:m1fei1paaf5g8qo9ef509q9cqocdmds466@.
4ax.com...
> On Tue, 13 Sep 2005 16:37:16 -0400, Nancy Lytle wrote:
>
> (snip)
> Hi Nacny,
> I'll assume that the different table names and column names are a result
> of you renaming some tables and columns when preparing the post, and the
> code you actually tested this on didn't have these differences :-)
> The difference in the queries are the two different styles of join
> notation. In old versions of SQL Server (note that I'm talking real old
> here - older than SQL Server 6.5), only the version with the
> comma-delimited list of tables is allowed. The more verbose version with
> infixed join operators was added later, to adhere to the ANSI standard.
> For inner joins, there is absolutely no difference between the two
> versions. They are both defined in the ANSI standard, both acccepted by
> SQL Server and they will both return the same results. They'll also use
> the same execution plan, so that there's no performance difference
> either.
> For outer joins, things are different. The "old-style notation" (that
> uses =* and *= in the WHERE clause to define inner and outer tables) is
> ambiguous. It's not defined in the ANSI standard. MS has announced that
> it will drop support for =* and *= in a future version. In fact, I
> recall reading somewhere that SQL Server 2005 will only accept =* and *=
> in the backward compatibility mode.
>
> Did you run both tests on an empty cache? I suspect not - and that's
> what causes the difference.
> Test it like this:
> DECLARE @.start datetime
> DECLARE @.end datetime
> -- Flush all dirty buffers to disk
> CHECKPOINT
> -- Remove all previously read pages from the data cache
> DBCC DROPCLEANBUFFERS
> -- Remove all previously compiled execution plans as well
> DBCC FREEPROCCACHE
> -- Now start the real test
> SET @.start = CURRENT_TIMESTAMP
> #### ####
> #### YOUR QUERY GOES HERE ####
> #### ####
> SET @.end = CURRENT_TIMESTAMP
> SELECT @.start AS StartTime,
> @.end AS EndTime,
> DATEDIFF(ms, @.Start, @.End) AS "Elapsed (ms)"
> If the code that you are testing returns many rows, change the SELECT to
> a SELECT ... INTO #temp_table to eliminate the speed of the network and
> the display speed of your client from the equation.
> I'd be VERY surprised if you still get significant differences if you
> test the queries like this. (Small differences are to be expected,
> especially if the server you're running this on has other things to do
> as well).
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Wed, 14 Sep 2005 08:59:40 -0400, Nancy Lytle wrote:

>Thanks, Hugo, Trey and Aaron!
>I guess there is more sql code I am going to have to change, the SP's (and
>they are all named sp_ !) are dotted with uses of comma delimited lists of
>tables for joins and usage of *=, not to mention tons of select *'s, and th
e
>sp_ naming convention.
>I used Hugo query and that helped me see the real difference between the
>two, which is actually very slight and leans toward the use of JOINs.
>Thanks again, this is a great group!
>Nancy
Hi Nancy,
I guess that the "very slight" difference you see falls within the
bounds of statistic inaccuracy. If you repeat the test a few times, you
should see that there really is no difference between the two.
As far as rewriting code, I'd say: find the right path between
religiously rewriting everything (costly, time-consuming, and will
introduce bugs, if only by typo's and copy/paste errors) on the one end,
and leaving working code untouched on the other end.
If you decide to start rewriting where it's needed most, then begin with
the use of =* and *= for outer joins, as they are on the deprecated
feature list. Next should be the sp_ prefix and the use of SELECT *
(both are performance killers in their own ways; both induce a risk of
unexpectedly breaking your code when some change is made elsewhere).
The join syntax for inner joins (i.e. the use of comma-delimited table
list without any =* or *=) should be last on your list, as this is only
a readability improvement (and not all experts would agree that it's an
improvement - there are a few SQL experts who think that the "old style"
join notation is often better, though I'm not one of them).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks for the suggestions.
I did a complete search and the locations for the *= code is in dynamic sql
written in .asp pages. So, since I am changing the dynamic sql to a stored
procedure, I can get rid of the *= and select * and created proper procedure
naming all at the same time.
I will wait to modify the names of the sp_'s that are already written until
I have a chance to sit down with the other programmers, etc, and we come up
with a plan. My initial thought was to simply recreate the SPs changing
only the name, so we would have essentially 2 sp's that did the same thing,
just one sp_ and one usp_ names. Then we could start cutting over the names
in the code without breaking anything, that couldn't be fixed almost
immediately.
But this is my first time really taking on a task like this, does this sound
like a plan?
Do you have any articles or books to recommend to a new DBA/developer?
Thanks again,
Nancy
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:3rvgi15u21emupk0gfqe9pl5dpfk8kgl0s@.
4ax.com...
> On Wed, 14 Sep 2005 08:59:40 -0400, Nancy Lytle wrote:
>
> Hi Nancy,
> I guess that the "very slight" difference you see falls within the
> bounds of statistic inaccuracy. If you repeat the test a few times, you
> should see that there really is no difference between the two.
> As far as rewriting code, I'd say: find the right path between
> religiously rewriting everything (costly, time-consuming, and will
> introduce bugs, if only by typo's and copy/paste errors) on the one end,
> and leaving working code untouched on the other end.
> If you decide to start rewriting where it's needed most, then begin with
> the use of =* and *= for outer joins, as they are on the deprecated
> feature list. Next should be the sp_ prefix and the use of SELECT *
> (both are performance killers in their own ways; both induce a risk of
> unexpectedly breaking your code when some change is made elsewhere).
> The join syntax for inner joins (i.e. the use of comma-delimited table
> list without any =* or *=) should be last on your list, as this is only
> a readability improvement (and not all experts would agree that it's an
> improvement - there are a few SQL experts who think that the "old style"
> join notation is often better, though I'm not one of them).
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Thu, 15 Sep 2005 09:51:15 -0400, Nancy Lytle wrote:

>Thanks for the suggestions.
>I did a complete search and the locations for the *= code is in dynamic sql
>written in .asp pages. So, since I am changing the dynamic sql to a stored
>procedure, I can get rid of the *= and select * and created proper procedur
e
>naming all at the same time.
Hi Nancy,
Wow, that's a major improvement - getting rid of two major pitfalls at
once!

>I will wait to modify the names of the sp_'s that are already written until
>I have a chance to sit down with the other programmers, etc, and we come up
>with a plan. My initial thought was to simply recreate the SPs changing
>only the name, so we would have essentially 2 sp's that did the same thing,
>just one sp_ and one usp_ names. Then we could start cutting over the name
s
>in the code without breaking anything, that couldn't be fixed almost
>immediately.
>But this is my first time really taking on a task like this, does this soun
d
>like a plan?
Discussing things with the developers is definitely a great idea. As
long as your modifications are invisible to them (such as replacing
dynamic =* crap with non-dynamic OUTER JOINs), you could do you work in
silence (though I'd even recommend communicating your actions in that
case). But if your changes are going toa ffect the developers (and they
will if you intend to eventually remove the badly named stored
procedures), they should be informed, and invited to participate.
But if you are renaming, then I'd just drop the prefix completely. I've
never managed to see the added value of
EXEC usp_MakeMonthlyReport
over
EXEC MakeMonthlyReport

>Do you have any articles or books to recommend to a new DBA/developer?
http://www.aspfaq.com/show.asp?id=2423
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Query with multiple tables

How can i xquery the results from multiple tables?What exactly are you trying to do, combine the native XML or shredded ones?
Can you post your table DDL with some sample data? Note that XML typed
values cannot be compared, sorted or grouped and hence cannot be in direct
SQL predicates.
Anith|||I got a table with XML-Data about books, in an other table i stored XML-Data
about authors. Now i want to combine the data using xquery, but i do not kno
w
how to query over multiple tables.

Query with MAX funtion

Hello,
I have 2 tables.
one table contains employees with their salary.
second table contains department.
I can select the highest salary in each department but
I would like to select the name of the employee who makes the highest salary in each department.
Can you help me to make this query ?
Thanks,
Aur=E9lieThis is a multi-part message in MIME format.
--=_NextPart_000_033B_01C37935.CF55CC40
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Try:
select
d.DeptName
, e.EmployeeName
from
Depts as d
join
Employees as e on e.Dept =3D d.Dept
where
e.Salary =3D
(
select
max (e2.Salary)
from
Employees as e2
where
e2.Dept =3D d.Dept
)
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Aur=E9lie" <av@.lbn.fr> wrote in message =news:294501c37955$7b0322d0$a601280a@.phx.gbl...
Hello,
I have 2 tables.
one table contains employees with their salary.
second table contains department.
I can select the highest salary in each department but
I would like to select the name of the employee who makes the highest salary in each department.
Can you help me to make this query ?
Thanks,
Aur=E9lie
--=_NextPart_000_033B_01C37935.CF55CC40
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Try:
select
=d.DeptName
, e.EmployeeName
from
Depts as =d
join
Employees as =e on e.Dept =3D d.Dept
where
e.Salary ==3D
(
=select
= max (e2.Salary)
=from
= Employees as e2
=where
= e2.Dept =3D d.Dept
)
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Aur=E9lie" =wrote in message news:294501c37955$7b=0322d0$a601280a@.phx.gbl...Hello,I have 2 tables.one table contains employees with their =salary.second table contains department.I can select the highest salary in each =department butI would like to select the name of the employee who makes the =highest salary in each department.Can you help me to make this query ?Thanks,Aur=E9lie

---

--=_NextPart_000_033B_01C37935.CF55CC40--|||This is a multi-part message in MIME format.
--=_NextPart_000_0016_01C37937.E00063F0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Tom's Query will work provided that the Salary field is declared as a =money or decimal field. I have seen many databases where (for =portability and the capability of storing very large numbers) that money =field are defined as "float" columns. If this is the case you should =use a 'delta' value when comparing the salaries - because of computer =rounding errors you should never directly compare floats, doubles, =anything with a sliding decimal...so the comparison would be:
where abs(e.salary - (select max (e2.Salary) from Employees as e2 where
e2.Dept =3D d.Dept) ) < 0.0001
Bruce Carson
Director of Technology
Edgewater Technology
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:%23QjHCdVeDHA.2680@.TK2MSFTNGP11.phx.gbl...
Try:
select
d.DeptName
, e.EmployeeName
from
Depts as d
join
Employees as e on e.Dept =3D d.Dept
where
e.Salary =3D
(
select
max (e2.Salary)
from
Employees as e2
where
e2.Dept =3D d.Dept
)
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Aur=E9lie" <av@.lbn.fr> wrote in message =news:294501c37955$7b0322d0$a601280a@.phx.gbl...
Hello,
I have 2 tables.
one table contains employees with their salary.
second table contains department.
I can select the highest salary in each department but
I would like to select the name of the employee who makes the highest salary in each department.
Can you help me to make this query ?
Thanks,
Aur=E9lie
--=_NextPart_000_0016_01C37937.E00063F0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Tom's Query will work provided that the =Salary field is declared as a money or decimal field. I have seen many =databases where (for portability and the capability of storing very large numbers) =that money field are defined as "float" columns. If this is the case =you should use a 'delta' value when comparing the salaries - because of computer =rounding errors you should never directly compare floats, doubles, anything with =a sliding decimal...so the comparison would be:
where abs(e.salary - (select max (e2.Salary) from Employees as =e2 where
= e2.Dept =3D d.Dept) ) < 0.0001
Bruce Carson
Director of Technology
Edgewater =Technology
"Tom Moreau" = wrote in message news:%23QjHCdVeDHA.=2680@.TK2MSFTNGP11.phx.gbl...
Try:

select
d.DeptName
, e.EmployeeName
from
Depts as d
join
Employees =as e on e.Dept =3D d.Dept
where
e.Salary =3D
(
=select
= max (e2.Salary)
=from
= Employees as e2
=where
= e2.Dept =3D d.Dept
)
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql


"Aur=E9lie" =wrote in message news:294501c37955$7b=0322d0$a601280a@.phx.gbl...Hello,I have 2 tables.one table contains employees with their =salary.second table contains department.I can select the highest salary in each department butI would like to select the name of the employee who =makes the highest salary in each department.Can you help me to =make this query ?Thanks,Aur=E9lie

---

--=_NextPart_000_0016_01C37937.E00063F0--|||This is a multi-part message in MIME format.
--=_NextPart_000_037D_01C37939.390DAC40
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Good point. I just assumed it was stored as money. When you assume, =...
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Bruce A. Carson" <bcarson@.asgoth.com> wrote in message =news:eBBfolVeDHA.3576@.tk2msftngp13.phx.gbl...
Tom's Query will work provided that the Salary field is declared as a =money or decimal field. I have seen many databases where (for =portability and the capability of storing very large numbers) that money =field are defined as "float" columns. If this is the case you should =use a 'delta' value when comparing the salaries - because of computer =rounding errors you should never directly compare floats, doubles, =anything with a sliding decimal...so the comparison would be:
where abs(e.salary - (select max (e2.Salary) from Employees as e2 where e2.Dept =3D d.Dept) ) < 0.0001
Bruce Carson
Director of Technology
Edgewater Technology
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:%23QjHCdVeDHA.2680@.TK2MSFTNGP11.phx.gbl...
Try:
select
d.DeptName
, e.EmployeeName
from
Depts as d
join
Employees as e on e.Dept =3D d.Dept
where
e.Salary =3D
(
select
max (e2.Salary)
from
Employees as e2
where
e2.Dept =3D d.Dept
)
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Aur=E9lie" <av@.lbn.fr> wrote in message =news:294501c37955$7b0322d0$a601280a@.phx.gbl...
Hello,
I have 2 tables.
one table contains employees with their salary.
second table contains department.
I can select the highest salary in each department but
I would like to select the name of the employee who makes the highest salary in each department.
Can you help me to make this query ?
Thanks,
Aur=E9lie
--=_NextPart_000_037D_01C37939.390DAC40
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Good point. I just assumed it =was stored as money. When you assume, ...
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Bruce A. Carson" wrote in =message news:eBBfolVeDHA.3576=@.tk2msftngp13.phx.gbl...
Tom's Query will work provided that the =Salary field is declared as a money or decimal field. I have seen many =databases where (for portability and the capability of storing very large numbers) =that money field are defined as "float" columns. If this is the case =you should use a 'delta' value when comparing the salaries - because of computer =rounding errors you should never directly compare floats, doubles, anything with =a sliding decimal...so the comparison would be:
where abs(e.salary - (select max (e2.Salary) from Employees as =e2 where = e2.Dept =3D d.Dept) ) < 0.0001
Bruce Carson
Director of Technology
Edgewater =Technology
"Tom Moreau" = wrote in message news:%23QjHCdVeDHA.=2680@.TK2MSFTNGP11.phx.gbl...
Try:

select
d.DeptName
, e.EmployeeName
from
Depts as d
join
Employees =as e on e.Dept =3D d.Dept
where
e.Salary =3D
(
=select
= max (e2.Salary)
=from
= Employees as e2
=where
= e2.Dept =3D d.Dept
)
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql


"Aur=E9lie" =wrote in message news:294501c37955$7b=0322d0$a601280a@.phx.gbl...Hello,I have 2 tables.one table contains employees with their =salary.second table contains department.I can select the highest salary in each department butI would like to select the name of the employee who =makes the highest salary in each department.Can you help me to =make this query ?Thanks,Aur=E9lie

---

--=_NextPart_000_037D_01C37939.390DAC40--

Query with Joins problem

Hello

Let me explain the problem I am having:
I have two tables, data_t and a_data_t
a_data_t is the archive table of data_t

The two tables are exactly the same.

In the table values are stored:
Value (A numeric value)
Code (A text code to identify a report with data)
Line (The line number)
Col (The Col Number)
EDate (The date of entry)
Grp (A number of a group the data belongs to)

I want to get the value from data_t minus the value from a_data_t with
the same Code, Line and Col but with a different EDate (To view the
variance).

Here is my statement:

select d1.line, d1.col, (IsNull(d1.value,0) - IsNull(d2.value,0)) as
value from data_t d1
full outer join a_data_t d2 on d1.Code = d2.Code and d2.line = d2.line
and d1.col = d2.col
where
d1.Code = 'XC001' and d1.line between 1 and 20 and d1.grp = 26
and d1.EDate = '2006/06' and d2.grp = 26 and d2.EDate = '2006/05'
order by d1.line, d1.col

It works fine EXCEPT when there is a value in either of the tables that
isn't in the other one, then a value is not given.

Example:
data_t doens't have a value for line=1 and col=2 and grp=26 and Code =
'XC001' and EDate = '2006/06'
a_data_t has the value of 50000 for the same details (Except Edate of
'2006/5')
Instead of returning -50000 it doesn't return anything.

I hope I could explain it correctly.
Any help will be greatly appreciated.

Thanks.(wilhelm.kleu@.gmail.com) writes:
> Here is my statement:
> select d1.line, d1.col, (IsNull(d1.value,0) - IsNull(d2.value,0)) as
> value from data_t d1
> full outer join a_data_t d2 on d1.Code = d2.Code and d2.line = d2.line
> and d1.col = d2.col
> where
> d1.Code = 'XC001' and d1.line between 1 and 20 and d1.grp = 26
> and d1.EDate = '2006/06' and d2.grp = 26 and d2.EDate = '2006/05'
> order by d1.line, d1.col
> It works fine EXCEPT when there is a value in either of the tables that
> isn't in the other one, then a value is not given.

This is because thw WHERE clause nullifiles the benefit of the full
join. The full join operation bulids a table which consists of the
union of all rows in both tables, and when a row in one table does
not have a match in the other, all columns for that other table are
NULL.

Then you add a WHERE condition where you filter away all NULL values,
so you only get rows that are in both tables.

Try replacing WHERE with AND and see what happens. I'm not sure this
will give the desired result, but without knowledge of the keys it's
a bit difficult to say what you are looking for.

A standard suggestion for this sort of questions is that you post:

o CREATE TABLE statements for your tables.
o INSERT statements with sample data.
o The desired result given the sample.

This makes it easy to copy and paste and develop a tested solution.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql

Query with a lot of left joins - Rewrite?

Hello,

I have a query with 11 left joins. Some hits against tables with small amounts of reference data, whereas others are not so small. Should I rewrite this in another way, as performance is a requirement on this one? Or, should I do it another way?

How would I rewrite left joins? Any examples?

Thanks.

Moving to T-SQL inside SQL Server, where you are most likely to find experts to answer this question.|||

When you have that many referenced tables/joins, you will get a perf hit, espcially if you have large tables involved. The only trick in the book that helps is to filter large tables down to smaller #temp tables. Then use these #temp tables for your join. You will reduce the log need to process your large tables.

e.g.

Code Snippet

select *

into #tmp1

from large_table1

where filter=123

select *

into #tmp2

from large_table2

where filter=xyz

select *

from #tmp1 left join #tmp2 on ...

|||

Before applying any kind of performance tuning to your query, first answer this question. Is there any performance issue when testing it? There is nothing particularly slow about a query with 11 left joins, if your data is structured correctly and (very important) indexex correctly.

The kinds of things you can "guess" and do (like breaking down into temp tables, as OJ states) are excellent strategies if the optimizer fails you, but 9 times out of 10, even for really complex queries, SQL Server will do whatever you are trying to do to improve performance of the joins on their own.

If you don't have a test environment with adeqate amounts of data to do performance testing, this process is a heck of a lot more work, but as a rule, I don't like to do any performance guessing, do performance testing/reactive tuning BEFORE you get to your production environment if at all possible.

Query with a lot of left joins - Rewrite?

Hello,

I have a query with 11 left joins. Some hits against tables with small amounts of reference data, whereas others are not so small. Should I rewrite this in another way, as performance is a requirement on this one? Or, should I do it another way?

How would I rewrite left joins? Any examples?

Thanks.

Moving to T-SQL inside SQL Server, where you are most likely to find experts to answer this question.|||

When you have that many referenced tables/joins, you will get a perf hit, espcially if you have large tables involved. The only trick in the book that helps is to filter large tables down to smaller #temp tables. Then use these #temp tables for your join. You will reduce the log need to process your large tables.

e.g.

Code Snippet

select *

into #tmp1

from large_table1

where filter=123

select *

into #tmp2

from large_table2

where filter=xyz

select *

from #tmp1 left join #tmp2 on ...

|||

Before applying any kind of performance tuning to your query, first answer this question. Is there any performance issue when testing it? There is nothing particularly slow about a query with 11 left joins, if your data is structured correctly and (very important) indexex correctly.

The kinds of things you can "guess" and do (like breaking down into temp tables, as OJ states) are excellent strategies if the optimizer fails you, but 9 times out of 10, even for really complex queries, SQL Server will do whatever you are trying to do to improve performance of the joins on their own.

If you don't have a test environment with adeqate amounts of data to do performance testing, this process is a heck of a lot more work, but as a rule, I don't like to do any performance guessing, do performance testing/reactive tuning BEFORE you get to your production environment if at all possible.

Query with 5 tables, grouping by year

How would I group results from 4 tables, each with a year field, so the
results are one row for each year, all based on one clientid from the client
table
For instance, my tables:
Client table: fields ID, ClientName
Tables 1 through 4 all have the same fields, in addition to others: Fields
ID, ClientID (fk to Client table), ValidYear, Data ...
I want my results to be one row per year (we'll be querying only one
client), for example
Year Table1Data Table2Data Table3Data Table4Data
2001 1,000 3,300 15,000 445
2002 1,212 etc.
I've started with:
Select Table1.Data, Table1.ValidYear,
Table2.Data, Table2.ValidYear,
Table3.Data, Table3.ValidYear,
Table4.Data, Table4.ValidYear,
tblClient.ID, tblClient.ClientName
from Table1
inner join tblClient as a on a.ID = Table1.ClientID
inner join tblClient as b on b.ID = Table1.ClientID
inner join tblClient as c on c.ID = Table1.ClientID
inner join tblClient as d on d.ID = Table1.ClientID
and that's as far as I got, as soon as I enter criteria for ValidYear, I get
either too many rows of data or none at all. Not all data tables have data
for all years, by the way.
Thanks very much for your help.Hi,
would you like to check out he usage of DATEPART?
In your SELECT statement, you actually can put DatePart(year,
Table2.ValidYear) instead of Table2.ValidYear.
In the end of your SELECT statement, you just need to put GROUP BY
Table1.Data, Table2.Data etc to get a distinct value.
I hope this is what you are looking for.
Leo Leong
"et" wrote:

> How would I group results from 4 tables, each with a year field, so the
> results are one row for each year, all based on one clientid from the clie
nt
> table
> For instance, my tables:
> Client table: fields ID, ClientName
> Tables 1 through 4 all have the same fields, in addition to others: Field
s
> ID, ClientID (fk to Client table), ValidYear, Data ...
> I want my results to be one row per year (we'll be querying only one
> client), for example
> Year Table1Data Table2Data Table3Data Table4Data
> 2001 1,000 3,300 15,000 445
> 2002 1,212 etc.
> I've started with:
> Select Table1.Data, Table1.ValidYear,
> Table2.Data, Table2.ValidYear,
> Table3.Data, Table3.ValidYear,
> Table4.Data, Table4.ValidYear,
> tblClient.ID, tblClient.ClientName
> from Table1
> inner join tblClient as a on a.ID = Table1.ClientID
> inner join tblClient as b on b.ID = Table1.ClientID
> inner join tblClient as c on c.ID = Table1.ClientID
> inner join tblClient as d on d.ID = Table1.ClientID
> and that's as far as I got, as soon as I enter criteria for ValidYear, I g
et
> either too many rows of data or none at all. Not all data tables have dat
a
> for all years, by the way.
> Thanks very much for your help.
>
>

Query whith Linked Server

Hi gruoup
I have to make a query from SQL Server tables joined with Visual FoxPro
xbase tables. I created a Linked Server with Microrosft OLE DB Provider for
Visual FoxPro provider, whth VFPOLEDB.1 string provider. Everything runs ok
within a Business Intelligence Development Studio environment, but when I
implement my report in SQL Reporting Server this one reports an error
because it can't create the object VFPOLEDB. I proved any combination of
Linked Server security tab page without to solve the problem.
Any help about this error I'll thank very much.I want to add some coments to clarify the problem.
The report runs at a right way from Internet Explorer 7 browser when I
generate it in a machine at which runs the Report Server, but I have
problems when I run it under Mozilla Fire Fox at local server machine or it
fails too from any browser at any intranet machine.
Thanks.
<tiempotecnologia@.newsgroup.nospam> escribió en el mensaje
news:Ou2%23o5kPHHA.1380@.TK2MSFTNGP05.phx.gbl...
> Hi gruoup
> I have to make a query from SQL Server tables joined with Visual FoxPro
> xbase tables. I created a Linked Server with Microrosft OLE DB Provider
> for Visual FoxPro provider, whth VFPOLEDB.1 string provider. Everything
> runs ok within a Business Intelligence Development Studio environment, but
> when I implement my report in SQL Reporting Server this one reports an
> error because it can't create the object VFPOLEDB. I proved any
> combination of Linked Server security tab page without to solve the
> problem.
> Any help about this error I'll thank very much.
>|||Hello Tiem
My understanding of this issue is that: You have a linked server in the sql
server and you wants to use it in the reporting services.
I would like to know what's the credential you supplied in the datasource
for the sql server.
If you use a sa account, did this report could be accessed?
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Wei, thanks for your time!
I configured the database with RS Configuration tool under SQL credentials
using sa account and the report and its queries run correctly within
Business Intelligence Dev. Studio, i.e. the report preview, the execute of
query in data tab page, etc, everything runs ok, but from another intranet
machine, where I must authenticate with the account under run RS web
services, Report Manager opens the initial parameters view, I supply them
and then an error occurs because VFPOLEDB data provider object can`t create.
The physical Visual FoxPro tables are stored in an intranet machine within a
shared folder with read permission for anyone. Today I made a test moving
this tables to RS server local folder and I created a linked server ponting
to it but the same resulted. I tested with Domain\Useraccount credentials
for RS database too and the same resulted.
I'm waiting for help. Thanks in advance.
Arturo Carrión
artcarrion@.yahoo.com.ar
at Tiempo Hard SA
Mendoza, Argentina
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> escribió en el mensaje
news:POsb5hrPHHA.2304@.TK2MSFTNGHUB02.phx.gbl...
> Hello Tiem
> My understanding of this issue is that: You have a linked server in the
> sql
> server and you wants to use it in the reporting services.
> I would like to know what's the credential you supplied in the datasource
> for the sql server.
> If you use a sa account, did this report could be accessed?
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hello Arturo,
When I mentioned credentials, I mean the credentials you use in the report.
You could connect to the report manager, find the report and then provide
the credential information.
Sincerely yours,
Wei Lu
Microsoft Online Partner Support
=====================================================
PLEASE NOTE: The partner managed newsgroups are provided to assist with
break/fix
issues and simple how to questions.
We also love to hear your product feedback!
Let us know what you think by posting
- from the web interface: Partner Feedback
- from your newsreader: microsoft.private.directaccess.partnerfeedback.
We look forward to hearing from you!
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hello, Wei
When you mentioned credentials, did you refer to ones to access SQL Server
or the Web Services ?
If I've a good understanding and you refered to SQL Server credentials, when
I generate a report I define its data source providing a connect string with
RS server name and initial catalog, but I don't define credentials. These
ones are defined at RS database configuration, not at a single report.
Please, correct me if I'm wrong.
I think that the problem seems to be related with some VFPOLEDB Provider
Security settings, do you believe it ?
Thank you very much.
Arturo Carrión
.Net/SQL Server Developer at
Tiempo Hard SA
artcarrion@.yahoo.com.ar
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> escribió en el mensaje
news:baptaH2PHHA.2304@.TK2MSFTNGHUB02.phx.gbl...
> Hello Arturo,
> When I mentioned credentials, I mean the credentials you use in the
> report.
> You could connect to the report manager, find the report and then provide
> the credential information.
> Sincerely yours,
> Wei Lu
> Microsoft Online Partner Support
> =====================================================> PLEASE NOTE: The partner managed newsgroups are provided to assist with
> break/fix
> issues and simple how to questions.
> We also love to hear your product feedback!
> Let us know what you think by posting
> - from the web interface: Partner Feedback
> - from your newsreader: microsoft.private.directaccess.partnerfeedback.
> We look forward to hearing from you!
> ======================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others
> may learn and benefit from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ======================================================>|||Hi Wei.
I'm so sorry!. Definitively I'm wrong. When I define a data source I must
provide the server and credentials. I defined them as sa account and its
password, test button has no problem.
Sincerely yours,
Arturo Carrión
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> escribió en el mensaje
news:baptaH2PHHA.2304@.TK2MSFTNGHUB02.phx.gbl...
> Hello Arturo,
> When I mentioned credentials, I mean the credentials you use in the
> report.
> You could connect to the report manager, find the report and then provide
> the credential information.
> Sincerely yours,
> Wei Lu
> Microsoft Online Partner Support
> =====================================================> PLEASE NOTE: The partner managed newsgroups are provided to assist with
> break/fix
> issues and simple how to questions.
> We also love to hear your product feedback!
> Let us know what you think by posting
> - from the web interface: Partner Feedback
> - from your newsreader: microsoft.private.directaccess.partnerfeedback.
> We look forward to hearing from you!
> ======================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others
> may learn and benefit from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ======================================================>|||Great Wei !
I solve the problem following this 3 steps:
1. SQL Server Windows Service, Browser and RS all must run under domain
account or net service account.
2. VFPOLEDB Provider Setting "Allow Inprocess" must be checked.
3. DataSource at each report must have sql credentials (your suggestion), in
our case, sa account.
Thank you very much for your help.
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> escribió en el mensaje
news:baptaH2PHHA.2304@.TK2MSFTNGHUB02.phx.gbl...
> Hello Arturo,
> When I mentioned credentials, I mean the credentials you use in the
> report.
> You could connect to the report manager, find the report and then provide
> the credential information.
> Sincerely yours,
> Wei Lu
> Microsoft Online Partner Support
> =====================================================> PLEASE NOTE: The partner managed newsgroups are provided to assist with
> break/fix
> issues and simple how to questions.
> We also love to hear your product feedback!
> Let us know what you think by posting
> - from the web interface: Partner Feedback
> - from your newsreader: microsoft.private.directaccess.partnerfeedback.
> We look forward to hearing from you!
> ======================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others
> may learn and benefit from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ======================================================>|||Hello Arturo,
Thanks for the update and glad to hear you resolved this issue.
You need to pass the credential in the datasource of the report so other
client could use this credential to connect to the linked server.
Otherwise, they will access denied.
If you have any questions, please feel free to let me know.
Sincerely yours,
Wei Lu
Microsoft Online Partner Support
=====================================================
PLEASE NOTE: The partner managed newsgroups are provided to assist with
break/fix
issues and simple how to questions.
We also love to hear your product feedback!
Let us know what you think by posting
- from the web interface: Partner Feedback
- from your newsreader: microsoft.private.directaccess.partnerfeedback.
We look forward to hearing from you!
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================sql

Query using multiple tables

Hi, I have a problem which I thought it has a simple solution but now I'm not even sure it is possible.

I have 3 tablesClients <-ooClientContacts oo->Contacts
(the <-oo means one to may relation between the tables)

A Client may have related none, one or many Contact records. The table ClientContacts is the link that stores that information. The field ClientContacts.Category represents the type of the contact and it will be used in queries. It may be owner, accountant, employee, etc.

My goal is to run a query which will return

Clients.Company, Clients.MailingStreet, Clients.MailingCity, Clients.MailingState
Contacts.FirstName, Contacts.LastName, Contacts.[E-mailAddress]
WHERE (Clients.WorkOnHold = 0)

The result should return values for
Contacts.FirstName, Contacts.LastName, Contacts.[E-mailAddress] if the Client has attached Contact records filtered by category,
and '','','' or <NULL>,<NULL>,<NULL> if the Client does not have any Contact records.

I tryed an INNER JOIN but it will return juts the records having contact information.

Any solutions are appreciated.
Thanks.

Clients

CREATE TABLE [Clients] (
[ClientID] [int] IDENTITY (1, 1) NOT NULL ,
[Company] [varchar] (100),
[MailingStreet] [varchar] (50),
[MailingCity] [varchar] (35),
[MailingState] [varchar] (35) ,
[MailingZip] [varchar] (10),
[WorkOnHold] [bit] NULL ,
[ClientNotes] [varchar] (500),
CONSTRAINT [PK_Clients] PRIMARY KEY CLUSTERED
(
[ClientID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

Contacts

CREATE TABLE [Contacts] (
[ContactID] [int] IDENTITY (1, 1) NOT NULL ,
[FirstName] [varchar] (50) NOT NULL ,
[LastName] [varchar] (50) NOT NULL ,
[JobTitle] [varchar] (50),
[BusinessStreet] [varchar] (50),
[BusinessCity] [varchar] (35),
[BusinessState] [varchar] (35),
[BusinessPhone] [varchar] (20),
[BusinessFax] [varchar] (20),
[E-mailAddress] [varchar] (255),
CONSTRAINT [PK_Contacts] PRIMARY KEY CLUSTERED
(
[ContactID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

ClientContacts

CREATE TABLE [ClientContacts] (
[ClientID] [int] NOT NULL ,
[ContactID] [int] NOT NULL ,
[Category] [varchar] (50),
CONSTRAINT [FK_ClientContacts_Clients] FOREIGN KEY
(
[ClientID]
) REFERENCES [Clients] (
[ClientID]
) ON DELETE CASCADE ,
CONSTRAINT [FK_ClientContacts_Contacts] FOREIGN KEY
(
[ContactID]
) REFERENCES [Contacts] (
[ContactID]
) ON DELETE CASCADE
) ON [PRIMARY]
GO

The INNER JOIN I tryed but is not good. It returns just clients having contacts attached.

SELECT Clients.Company, Clients.MailingStreet, Clients.MailingCity, Clients.MailingState, Contacts.FirstName, Contacts.LastName,
 Contacts.[E-mailAddress]
FROM ClientContacts INNER JOIN
 Clients ON ClientContacts.ClientID = Clients.ClientID INNER JOIN
 Contacts ON ClientContacts.ContactID = Contacts.ContactID
WHERE (Clients.WorkOnHold = 0)

You will need a to use LEFT OUTER JOIN from Clients to the other tables:

SELECT Clients.Company, Clients.MailingStreet, Clients.MailingCity, Clients.MailingState, Contacts.FirstName, Contacts.LastName, Contacts.[E-mailAddress]
FROMClients LEFT OUTER JOIN
 ClientContacts ON ClientContacts.ClientID = Clients.ClientID INNER JOIN
 Contacts ON ClientContacts.ContactID = Contacts.ContactID
WHERE (Clients.WorkOnHold = 0)

|||Thanks for the attempt.
I tried it and it is not good !
It still returns only Clients having Contacts attached.

Any other solutions?
Thanks.|||I think I found the solution.
The first join has to be a LEFT OUTER JOIN (as ehorn pointed), but the second one should be a FULL OUTER JOIN.

SELECT Clients.Company, Clients.MailingStreet, Clients.MailingCity, Clients.MailingState,
 Contacts.FirstName, Contacts.LastName, Contacts.[E-mailAddress]
FROM Clients
LEFT OUTER JOIN ClientContacts ON ClientContacts.ClientID = Clients.ClientID
FULL OUTER JOIN Contacts ON ClientContacts.ContactID = Contacts.ContactID
WHERE (Clients.WorkOnHold=0)

This code seems to work fine. I hope it is not just a coincidence the fact I get the result I expected.

Thanks.

Query using mathematical function of values from 2 tables has a performance prob

When I am executing a query that uses a mathematical function on values from 2 tables the query takes much longer than the same query that uses values from 1 table, even though the join remains the same.

Why is this happening?
Is there a way to bypass this problem?

Long query ( values from 2 tables ) :
SELECT
MAX ( ( SIGN ( attribute.keyValue- ( -2027587559 ) ) *SIGN ( attribute.keyValue- ( -2027587559 ) ) -1 ) *-1*data.val ) AS maxVal
FROM
DATA data,
ATTR attribute,
TREE_ELEMENT elm,
TREE_ELEMENT subject
WHERE
data.elmId=elm.id
AND attribute.keyValue IN ( 345647222,1569153803,1569146115,-2027587559 )
AND subject.id=elm.subjectId
AND subject.name = test

Short query ( values from 1 table ) :
SELECT
MAX ( ( SIGN ( data.keyValue- ( -2027587559 ) ) *SIGN ( data.keyValue- ( -2027587559 ) ) -1 ) *-1*data.val ) AS maxVal
FROM
DATA data,
ATTR attribute,
TREE_ELEMENT elm,
TREE_ELEMENT subject
WHERE
data.elmId=elm.id
AND attribute.keyValue IN ( 345647222,1569153803,1569146115,-2027587559 )
AND subject.id=elm.subjectId
AND subject.name = test

Long query execution plan:
Execution Tree
-----
Stream Aggregate ( DEFINE: ( [Expr1004]=MAX ( ( sign ( [attribute].[keyValue]--2027587559 ) *sign ( [attribute].[keyValue]--2027587559 ) -1 ) * ( -1*[data].[val] ) ) ) )
|--Nested Loops ( Inner Join )
|--Hash Match ( Inner Join, HASH: ( [elm].[id] ) = ( [data].[elmId] ) , RESIDUAL: ( [data].[elmId]=[elm].[id] ) )
| |--Nested Loops ( Inner Join, OUTER REFERENCES: ( [subject].[id] ) )
| | |--Index Seek ( OBJECT: ( [TREE_ELEMENT].[TREE_ELEMENT_NAME_IDX] AS [subject] ) ,
SEEK: ( [subject].[name]=test ) ORDERED FORWARD )
| | |--Index Seek ( OBJECT: ( [TREE_ELEMENT].[TREE_ELEMENT_APP_ID_IDX] AS [elm] ) ,
SEEK: ( [elm].[subjectId]=[subject].[id] ) ORDERED FORWARD )
| |--Clustered Index Scan ( OBJECT: ( [DATA].[PK__DATAS_SAMPL__485B9C89] AS [data] ) )
|--Table Spool
|--Index Seek ( OBJECT: ( [ATTR].[TREE_Z_IDX] AS [attribute] ) ,
SEEK: ( [attribute].[keyValue]=-2027587559 OR [attribute].[keyValue]=345647222 OR [attribute].[keyValue]=1569146115 OR [attribute].[keyValue]=1569153803 ) ORDERED FORWARD )

Short query execution plan:
Execution Tree
-----
Stream Aggregate ( DEFINE: ( [Expr1004]=MAX ( [partialagg1005] ) ) )
|--Nested Loops ( Inner Join )
|--Stream Aggregate ( DEFINE: ( [partialagg1005]=MAX ( ( sign ( [data].[keyValue]--2027587559 ) *sign ( [data].[keyValue]--2027587559 ) -1 ) * ( -1*[data].[val] ) ) ) )
| |--Hash Match ( Inner Join, HASH: ( [elm].[id] ) = ( [data].[elmId] ) , RESIDUAL: ( [data].[elmId]=[elm].[id] ) )
| |--Nested Loops ( Inner Join, OUTER REFERENCES: ( [subject].[id] ) )
| | |--Index Seek ( OBJECT: ( [TREE_ELEMENT].[TREE_ELEMENT_NAME_IDX] AS [subject] ) ,
SEEK: ( [subject].[name]=test ) ORDERED FORWARD )
| | |--Index Seek ( OBJECT: ( [TREE_ELEMENT].[TREE_ELEMENT_APP_ID_IDX] AS [elm] ) ,
SEEK: ( [elm].[subjectId]=[subject].[id] ) ORDERED FORWARD )
| |--Clustered Index Scan ( OBJECT: ( [DATA].[PK__DATAS_SAMPL__485B9C89] AS [data] ) )
|--Index Seek ( OBJECT: ( [ATTR].[TREE_Z_IDX] AS [attribute] ) ,
SEEK: ( [attribute].[keyValue]=-2027587559 OR [attribute].[keyValue]=345647222 OR [attribute].[keyValue]=1569146115 OR [attribute].[keyValue]=1569153803 ) ORDERED FORWARD )Just a quick comment:
I don't actually see a(ny) join(s) - instead I see you using WHERE clauses; which is not advised!
The execution plan is assuming INNER JOINS which might not be what you want either.

Query using 'AND' on a linking table

Hi

I'm not an SQL expert and I'm having some problems doing a query on a linking table. I'm using SQL Server Compact Edition with the tables in question having the following (simplified) structure:

Contacts Table: ContactId (pk), DisplayName

SkillInstances Table: SkillInstID (pk), ObjectId(fk),SkillsId(fk)

Skills Table: SkillsId(pk), Skill

(The ObjectId in SkillInstances is ContactId in the Contacts table)

The idea is that a Contact can have one or more 'Skills'. Each Skill for a Contact has an SkillInstances row which references the particular Skill in the Skills table.

Typically I would want a query which returns Contacts with skills 'ABC' or 'XYZ'. This is no problem. But I cannot formulate a query that returns all Contacts with skills 'ABC' AND 'XYZ'.

SELECT Contacts.DisplayName
FROM SkillInstances INNER JOIN
Skills ON SkillInstances.SkillId = Skills.SkillId INNER JOIN
Contacts ON SkillInstances.ObjectId = Contacts.ContactId
WHERE (Skills.Skill = 'ABC') OR (Skills.Skill.= 'XYZ')

To get the AND condition, I tried creating two derived tables using the above SQL as a sub-queries but this does not seem to work on SQL Server CE.

Any ideas or thoughts much appreciated.

Regards

John Wilkie

Try the examples below which should both return the same results - one may be more performant than the other with your data.

Chris

Code Snippet

SELECT c.DisplayName

FROM Contacts c

WHERE EXISTS ( SELECT 1

FROM SkillInstances ski

INNER JOIN Skills sk ON sk.SkillID = ski.SkillID

WHERE ski.ObjectID = c.ContactID

AND sk.Skill = 'ABC' )

AND EXISTS ( SELECT 1

FROM SkillInstances ski

INNER JOIN Skills sk ON sk.SkillID = ski.SkillID

WHERE ski.ObjectID = c.ContactID

AND sk.Skill = 'XYZ' )

GO

SELECT c.DisplayName

FROM Contacts c

INNER JOIN SkillInstances ski ON ski.ObjectID = c.ContactID

INNER JOIN Skills sk ON sk.SkillID = ski.SkillID

WHERE sk.Skill IN('ABC', 'XYZ')

GROUP BY c.ContactID, c.DisplayName

HAVING COUNT(DISTINCT sk.Skill) = 2

GO

|||hi try this..

SELECT Contacts.DisplayName
FROM SkillInstances INNER JOIN
Skills ON SkillInstances.SkillId = Skills.SkillId INNER JOIN
Contacts ON SkillInstances.ObjectId = Contacts.ContactId
WHERE (Skills.Skill = 'ABC')
OR (Skills.Skill.= 'XYZ')
GROUP BY
Constacts.DisplayName
HAVING COUNT(Skills.Skill) = 2|||

here few more...

Code Snippet

Create Table #contacts(

[ContactId] int ,

[DisplayName] Varchar(100)

);

Insert Into #contactsValues('1','Nancy');

Insert Into #contactsValues('2','Andrew');

Insert Into #contactsValues('3','Janet');

Insert Into #contactsValues('4','Margaret');

Insert Into #contactsValues('5','Steven');

Create Table #skills (

[SkillsId] int ,

[Skill] Varchar(100)

);

Insert Into #skills Values('1','ASP.NET');

Insert Into #skills Values('2','SQL Server');

Insert Into #skills Values('3','C#');

Insert Into #skills Values('4','JavaScript');

Create Table #skillinstances (

[SkillInstID] int ,

[ObjectId] int ,

[SkillsId] int

);

Insert Into #skillinstances Values(1,1,1);

Insert Into #skillinstances Values(2,1,2);

Insert Into #skillinstances Values(3,1,3);

Insert Into #skillinstances Values(4,2,1);

Insert Into #skillinstances Values(5,2,2);

Insert Into #skillinstances Values(6,3,1);

Insert Into #skillinstances Values(7,3,3);

Insert Into #skillinstances Values(8,4,1);

Insert Into #skillinstances Values(9,4,2);

Insert Into #skillinstances Values(10,5,1);

Insert Into #skillinstances Values(11,5,3);

Insert Into #skillinstances Values(12,5,4);

--Using IN

SELECT

C.DisplayName

FROM

#SkillInstances SI

JOIN #Skills S1 ON SI.SkillsId = S1.SkillsIdAnd S1.Skill in ('ASP.NET')

INNER JOIN #Contacts C ON SI.ObjectId = C.ContactId

Where SI.ObjectId In

(

SELECT

SI.ObjectId

FROM

#SkillInstances SI

JOIN #Skills S1 ON SI.SkillsId = S1.SkillsIdAnd S1.Skill in ('C#')

)

--Using Exists

SELECT

C.DisplayName

FROM

#SkillInstances SIMain

JOIN #Skills S1 ON SIMain.SkillsId = S1.SkillsIdAnd S1.Skill in ('ASP.NET')

INNER JOIN #Contacts C ON SIMain.ObjectId = C.ContactId

Where Exists

(

SELECT

SI.ObjectId

FROM

#SkillInstances SI

JOIN #Skills S1 ON SI.SkillsId = S1.SkillsIdAnd S1.Skill in ('C#')

Where

SIMain.ObjectId = SI.ObjectId

)

--Simple & Faster One using Group By

SELECT

C.DisplayName

FROM

#SkillInstances SIMain

JOIN #Skills S1 ON SIMain.SkillsId = S1.SkillsIdAnd S1.Skill in ('ASP.NET','C#')

INNER JOIN #Contacts C ON SIMain.ObjectId = C.ContactId

Group By

C.DisplayName

Having

Count(Distinct Skill) =2

|||

Just to add that you should probably group by Contacts.ContactID (as in my example above) as you will receive eronous results if you group by only Contacts.DisplayName and your data contains duplicate Contacts.DisplayName values.

Chris

|||

Hi

Thank you for your quick response. Your solution works just fine. Thanks for your help.

Regards

John Wilkie

|||

Thanks to everyone for their prompt responses. All solutions seem to work fine. Thank you all again.

John Wilkie

query two tables to get data

Does anyone know how to query two tables to get data? I need something
like this
select * from registrations where regstatus='R' and select * from
classtop where ClassID equals the value of the first select statement.
So for exmaple:
The first select Statement will give me from registration:
id, ClassID, RegStatus, Date, UserID
I need the second select statement to give me from classtop:
(id=ClassID from first query)
id, ModuleID, Date, location
Thanks in advance for your help
Lisa
peashoe@.yahoo.com wrote:
> Does anyone know how to query two tables to get data? I need something
> like this
> select * from registrations where regstatus='R' and select * from
> classtop where ClassID equals the value of the first select statement.
> So for exmaple:
> The first select Statement will give me from registration:
> id, ClassID, RegStatus, Date, UserID
> I need the second select statement to give me from classtop:
> (id=ClassID from first query)
> id, ModuleID, Date, location
> Thanks in advance for your help
> Lisa
>
SELECT
Reg.ID,
Reg.ClassID,
Reg.RegStatus,
Reg.Date,
Reg.UserID,
Class.ID,
Class.ModuleID,
Class.Date,
Class.Location
FROM dbo.Registration AS Reg
INNER JOIN dbo.ClassTop AS Class
ON Reg.ClassID = Class.ID
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||use inner join
select * form registrations reg inner join classtop cla on cla.ClassID
=reg.ClassID
will get u data fro both table that matches ClassID
vinu
"peashoe@.yahoo.com" wrote:

> Does anyone know how to query two tables to get data? I need something
> like this
> select * from registrations where regstatus='R' and select * from
> classtop where ClassID equals the value of the first select statement.
> So for exmaple:
> The first select Statement will give me from registration:
> id, ClassID, RegStatus, Date, UserID
> I need the second select statement to give me from classtop:
> (id=ClassID from first query)
> id, ModuleID, Date, location
> Thanks in advance for your help
> Lisa
>

query two tables to get data

Does anyone know how to query two tables to get data? I need something
like this
select * from registrations where regstatus='R' and select * from
classtop where ClassID equals the value of the first select statement.
So for exmaple:
The first select Statement will give me from registration:
id, ClassID, RegStatus, Date, UserID
I need the second select statement to give me from classtop:
(id=ClassID from first query)
id, ModuleID, Date, location
Thanks in advance for your help
Lisapeashoe@.yahoo.com wrote:
> Does anyone know how to query two tables to get data? I need something
> like this
> select * from registrations where regstatus='R' and select * from
> classtop where ClassID equals the value of the first select statement.
> So for exmaple:
> The first select Statement will give me from registration:
> id, ClassID, RegStatus, Date, UserID
> I need the second select statement to give me from classtop:
> (id=ClassID from first query)
> id, ModuleID, Date, location
> Thanks in advance for your help
> Lisa
>
SELECT
Reg.ID,
Reg.ClassID,
Reg.RegStatus,
Reg.Date,
Reg.UserID,
Class.ID,
Class.ModuleID,
Class.Date,
Class.Location
FROM dbo.Registration AS Reg
INNER JOIN dbo.ClassTop AS Class
ON Reg.ClassID = Class.ID
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||use inner join
select * form registrations reg inner join classtop cla on cla.ClassID
=reg.ClassID
will get u data fro both table that matches ClassID
vinu
"peashoe@.yahoo.com" wrote:

> Does anyone know how to query two tables to get data? I need something
> like this
> select * from registrations where regstatus='R' and select * from
> classtop where ClassID equals the value of the first select statement.
> So for exmaple:
> The first select Statement will give me from registration:
> id, ClassID, RegStatus, Date, UserID
> I need the second select statement to give me from classtop:
> (id=ClassID from first query)
> id, ModuleID, Date, location
> Thanks in advance for your help
> Lisa
>sql

query two tables to get data

Does anyone know how to query two tables to get data? I need something
like this
select * from registrations where regstatus='R' and select * from
classtop where ClassID equals the value of the first select statement.
So for exmaple:
The first select Statement will give me from registration:
id, ClassID, RegStatus, Date, UserID
I need the second select statement to give me from classtop:
(id=ClassID from first query)
id, ModuleID, Date, location
Thanks in advance for your help
Lisapeashoe@.yahoo.com wrote:
> Does anyone know how to query two tables to get data? I need something
> like this
> select * from registrations where regstatus='R' and select * from
> classtop where ClassID equals the value of the first select statement.
> So for exmaple:
> The first select Statement will give me from registration:
> id, ClassID, RegStatus, Date, UserID
> I need the second select statement to give me from classtop:
> (id=ClassID from first query)
> id, ModuleID, Date, location
> Thanks in advance for your help
> Lisa
>
SELECT
Reg.ID,
Reg.ClassID,
Reg.RegStatus,
Reg.Date,
Reg.UserID,
Class.ID,
Class.ModuleID,
Class.Date,
Class.Location
FROM dbo.Registration AS Reg
INNER JOIN dbo.ClassTop AS Class
ON Reg.ClassID = Class.ID
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||use inner join
select * form registrations reg inner join classtop cla on cla.ClassID
=reg.ClassID
will get u data fro both table that matches ClassID
vinu
"peashoe@.yahoo.com" wrote:
> Does anyone know how to query two tables to get data? I need something
> like this
> select * from registrations where regstatus='R' and select * from
> classtop where ClassID equals the value of the first select statement.
> So for exmaple:
> The first select Statement will give me from registration:
> id, ClassID, RegStatus, Date, UserID
> I need the second select statement to give me from classtop:
> (id=ClassID from first query)
> id, ModuleID, Date, location
> Thanks in advance for your help
> Lisa
>