JOINS and Integers vs. Indexed "Strings".

I was told that, when possible, use integer fields for the equality comparison in INNER JOINS. Today someone suggested that using character fields that are indexed should be just as efficient.What do you think?

TIA,

barkingdog

It is the size of the index key which is most important. The larger your index key is, the more pages should be processed (and probably read from the hard disk: this is the slowest operation) while executing query. And, of course, there are some overhead comparing to strings in terms of collation. So, integer field is more effective in most cases.

joins

hi dis is kalaivanan,
which one of inner join or left join is efficient and in what way.Hi,
Inner join is always the best; but the purpose of Left and Right outer joins
serves different purpose. If you need all the data immeterial of join
from left most table participate in the join then use LEFT OUTER and if it
is vice versa use RIGHT OUTER join. INNER JOIN gives
only the matching records of the join.
Read the below infor from books online:-
Join conditions can be specified in either the FROM or WHERE clauses;
specifying them in the FROM clause is recommended. WHERE and HAVING clauses
can also contain search conditions to further filter the rows selected by
the join conditions.
Joins can be categorized as:
a.. Inner joins (the typical join operation, which uses some comparison
operator like = or <>). These include equi-joins and natural joins.
Inner joins use a comparison operator to match rows from two tables based
on the values in common columns from each table. For example, retrieving all
rows where the student identification number is the same in both the
students and courses tables.
b.. Outer joins. Outer joins can be a left, a right, or full outer join.
Outer joins are specified with one of the following sets of keywords when
they are specified in the FROM clause:
a.. LEFT JOIN or LEFT OUTER JOIN
The result set of a left outer join includes all the rows from the left
table specified in the LEFT OUTER clause, not just the ones in which the
joined columns match. When a row in the left table has no matching rows in
the right table, the associated result set row contains null values for all
select list columns coming from the right table.
b.. RIGHT JOIN or RIGHT OUTER JOIN.
A right outer join is the reverse of a left outer join. All rows from
the right table are returned. Null values are returned for the left table
any time a right table row has no matching row in the left table.
c.. FULL JOIN or FULL OUTER JOIN.
A full outer join returns all rows in both the left and right tables.
Any time a row has no match in the other table, the select list columns from
the other table contain null values. When there is a match between the
tables, the entire result set row contains data values from the base tables.
c.. Cross joins.
Cross joins return all rows from the left table, each row from the left
table is combined with all rows from the right table. Cross joins are also
called Cartesian products.
Thanks
Hari
SQL Server MVP
"kalaivanan" <mail2kalai@.gmail.com> wrote in message
news:1156571058.708245.325900@.m79g2000cwm.googlegroups.com...
> hi dis is kalaivanan,
> which one of inner join or left join is efficient and in what way.
>

joins

hi dis is kalaivanan,
which one of inner join or left join is efficient and in what way.Hi,
Inner join is always the best; but the purpose of Left and Right outer joins
serves different purpose. If you need all the data immeterial of join
from left most table participate in the join then use LEFT OUTER and if it
is vice versa use RIGHT OUTER join. INNER JOIN gives
only the matching records of the join.
Read the below infor from books online:-
Join conditions can be specified in either the FROM or WHERE clauses;
specifying them in the FROM clause is recommended. WHERE and HAVING clauses
can also contain search conditions to further filter the rows selected by
the join conditions.
Joins can be categorized as:
a.. Inner joins (the typical join operation, which uses some comparison
operator like = or <> ). These include equi-joins and natural joins.
Inner joins use a comparison operator to match rows from two tables based
on the values in common columns from each table. For example, retrieving all
rows where the student identification number is the same in both the
students and courses tables.
b.. Outer joins. Outer joins can be a left, a right, or full outer join.
Outer joins are specified with one of the following sets of keywords when
they are specified in the FROM clause:
a.. LEFT JOIN or LEFT OUTER JOIN
The result set of a left outer join includes all the rows from the left
table specified in the LEFT OUTER clause, not just the ones in which the
joined columns match. When a row in the left table has no matching rows in
the right table, the associated result set row contains null values for all
select list columns coming from the right table.
b.. RIGHT JOIN or RIGHT OUTER JOIN.
A right outer join is the reverse of a left outer join. All rows from
the right table are returned. Null values are returned for the left table
any time a right table row has no matching row in the left table.
c.. FULL JOIN or FULL OUTER JOIN.
A full outer join returns all rows in both the left and right tables.
Any time a row has no match in the other table, the select list columns from
the other table contain null values. When there is a match between the
tables, the entire result set row contains data values from the base tables.
c.. Cross joins.
Cross joins return all rows from the left table, each row from the left
table is combined with all rows from the right table. Cross joins are also
called Cartesian products.
Thanks
Hari
SQL Server MVP
"kalaivanan" <mail2kalai@.gmail.com> wrote in message
news:1156571058.708245.325900@.m79g2000cwm.googlegroups.com...
> hi dis is kalaivanan,
> which one of inner join or left join is efficient and in what way.
>

joins

hi dis is kalaivanan,
which one of inner join or left join is efficient and in what way.kalaivanan wrote:

Quote:

Originally Posted by

hi dis is kalaivanan,
which one of inner join or left join is efficient and in what way.

Inner joins and outer joins achieve different results so in general it
won't make sense to compare them on performance.

In cases where an inner and outer join are guaranteed to give the same
result I suggest you test it out for yourself. Performance is too
heavily dependent on other factors for there to a be a single, simple
answer.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:

Quote:

Originally Posted by

In cases where an inner and outer join are guaranteed to give the same
result I suggest you test it out for yourself.

And in that case, I expect the optimizer to see that the left join is
really an inner join, and thus you would get the same query plan.

The question is simply not meaningful, since, as David pointed, the
difference lies in functionality.

--
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.mspx

Joining two SQL queries with different conditions

Ok I am stumped on this. i tried an inner join where posted_jrnl_line as a
and posted_jrnl_line as b, also tried subqueries where (select jrnlId [then
conditions]) to no avail...
I have two queries that execute perfectly. the two queries are retrieving
data from one table but the conditions vary between the queries.
I would like to roll the two queries into one queries so I can create a
report for users.
Query #1
select
jrnl_id as 'System Jrnl',
jrnl_user_alpha_fld_3 as 'Proj Jrnl',
SUM(trans_amt) as 'Proj Jrnl Total'
from
DBSjepc.dbo.posted_jrnl_line
where
dbs_gl_account = '3475'
and jrnl_origin_code = 'PJ'
and posting_pd = '11'
and posting_yr = '2006'
and jrnl_user_alpha_fld_3 like 'TBC%'
Group by
jrnl_id, jrnl_user_alpha_fld_3)
So the output of Query 1 is
System Jrnl | Proj Jrnl | Proj Jrnl Total
Query #2
select jrnl_id as 'Ldr Jrnl',
jrnl_user_alpha_fld_3 as 'Ldr None',
SUM(trans_amt) as 'Ldr Jrnl Total'
from DBSjepc.dbo.posted_jrnl_line
where dbs_gl_account = '3475'
and not jrnl_origin_code in ('PJ','PR')
and posting_pd = '11'
and posting_yr = '2006'
and jrnl_id like 'TBC%'
Group by jrnl_id,jrnl_user_alpha_fld_3
So the output of Query 2 is
Ldr Jrnl | Ldr None | Ldr Jrnl Total
I would like the output to be
System Jrnl | Proj Jrnl | Proj Jrnl Total | Ldr Jrnl | Ldr None | Ldr Jrnl
Total
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200606/1I noticed that the WHERE criteria is not the same. Which criteria you you
want?
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"jsheldon via webservertalk.com" <u2880@.uwe> wrote in message
news:621d8e5ca05d2@.uwe...
> Ok I am stumped on this. i tried an inner join where posted_jrnl_line as
> a
> and posted_jrnl_line as b, also tried subqueries where (select jrnlId
> [then
> conditions]) to no avail...
> I have two queries that execute perfectly. the two queries are retrieving
> data from one table but the conditions vary between the queries.
> I would like to roll the two queries into one queries so I can create a
> report for users.
> Query #1
> select
> jrnl_id as 'System Jrnl',
> jrnl_user_alpha_fld_3 as 'Proj Jrnl',
> SUM(trans_amt) as 'Proj Jrnl Total'
> from
> DBSjepc.dbo.posted_jrnl_line
> where
> dbs_gl_account = '3475'
> and jrnl_origin_code = 'PJ'
> and posting_pd = '11'
> and posting_yr = '2006'
> and jrnl_user_alpha_fld_3 like 'TBC%'
> Group by
> jrnl_id, jrnl_user_alpha_fld_3)
> So the output of Query 1 is
> System Jrnl | Proj Jrnl | Proj Jrnl Total
>
> Query #2
> select jrnl_id as 'Ldr Jrnl',
> jrnl_user_alpha_fld_3 as 'Ldr None',
> SUM(trans_amt) as 'Ldr Jrnl Total'
> from DBSjepc.dbo.posted_jrnl_line
> where dbs_gl_account = '3475'
> and not jrnl_origin_code in ('PJ','PR')
> and posting_pd = '11'
> and posting_yr = '2006'
> and jrnl_id like 'TBC%'
> Group by jrnl_id,jrnl_user_alpha_fld_3
> So the output of Query 2 is
> Ldr Jrnl | Ldr None | Ldr Jrnl Total
> I would like the output to be
> System Jrnl | Proj Jrnl | Proj Jrnl Total | Ldr Jrnl | Ldr None | Ldr Jrnl
> Total
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200606/1|||Hi arnie,
that is correct the where clauses are different by the jrnl_origin field.
Query #1 must contain PJ for its output and Query #2 must NOT contain PJ and
PR for its output
Arnie Rowland wrote:
>I noticed that the WHERE criteria is not the same. Which criteria you you
>want?
>
>[quoted text clipped - 50 lines]
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200606/1|||You have in effect two distinctly different queries. Your desired output pro
vides nothing in common between the two queries.
Is all the data on a single line somehow related?
Are you trying for:
System Jrnl | Proj Jrnl | Proj Jrnl Total | Ldr Jrnl | Ldr None | Ldr Jrnl
-- -- -- -- -- --
data data data NULL NULL NULL
NULL NULL NULL data data data
OR
System Jrnl | Proj Jrnl | Proj Jrnl Total | Ldr Jrnl | Ldr None | Ldr Jrnl
-- -- -- -- -- --
data data data data data data
If you hope for the later, you best bet is to create the report in a client
application -like a reporting tool such as SQL Reporting Services -or even A
ccess.
From the limited information, I don't see how you will get to the second exa
mple. If you would send in the table DDL and some represtentative sample dat
a in the form of INSERT statements -you may be able to get more help.
Regards,
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"jsheldon via webservertalk.com" <u2880@.uwe> wrote in message news:621dd08c3fcbe@.uwe...darkred">
> Hi arnie,
>
> that is correct the where clauses are different by the jrnl_origin field.
> Query #1 must contain PJ for its output and Query #2 must NOT contain PJ a
nd
> PR for its output
>
> Arnie Rowland wrote:
>
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200606/1|||Hi Arnie,
Actually a mix but Option #1 is what is what I am trying to strive for. the
output does have a relation but not a database one. Users enter a project
journal must use the same journal ID when making their ledger journal.
This is desired.
System Jrnl | Proj Jrnl | Proj Jrnl Total | Ldr Jrnl | Ldr None | Ldr Jrnl
>-- -- -- -- -- --
data data data data NULL data
However if placed on two lines that is desirable as well: see below
System Jrnl | Proj Jrnl | Proj Jrnl Total | Ldr Jrnl | Ldr None | Ldr Jrnl
>-- -- -- -- -- --
data data data NULL NULL NULL
NULL NULL NULL data data data
Arnie Rowland wrote:
>You have in effect two distinctly different queries. Your desired output pr
ovides nothing in common between the two queries.
>Is all the data on a single line somehow related?
>Are you trying for:
>System Jrnl | Proj Jrnl | Proj Jrnl Total | Ldr Jrnl | Ldr None | Ldr Jrnl
>-- -- -- -- -- --
>data data data NULL NULL NULL
>NULL NULL NULL data data data
>OR
>System Jrnl | Proj Jrnl | Proj Jrnl Total | Ldr Jrnl | Ldr None | Ldr Jrnl
>-- -- -- -- -- --
>data data data data data data
>If you hope for the later, you best bet is to create the report in a client
application -like a reporting tool such as SQL Reporting Services -or even
Access.
>From the limited information, I don't see how you will get to the second ex
ample. If you would send in the table DDL and some represtentative sample da
ta in the form of INSERT statements -you may be able to get more help.
>Regards,
>
>[quoted text clipped - 10 lines]
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200606/1

Joining a query (inner/outer)

I have this current sql query which works great, but it only returns
the rows that exist in all tables.
I need to return all rows from po_cfg_Budget B and simply fill in 0 if
there are no totals.
Any help would be greatly appreciated.
select S.*,
b.Budget,
B.Description,
D.DeptID as DeptDescription,
(B.Budget - NotApproved - Approved) as AmountRemaining,
(B.Budget - Approved) as AmountApprovedRemaining
from BudgetStatus S,
(select * from po_cfg_budget) B,
(Select * from po_cfg_dept) D
where S.Years = B.BudgetYear
and S.Months = B.BudgetMonth
and S.DeptID = B.DeptID
and S.AccountID = B.AccountID
and S.DeptID = D.BudgetDeptID
order by B.DeptIDThis might work for you (passes syntax check but not actually
tested)...
select S.*,
b.Budget,
B.Description,
D.DeptID as DeptDescription,
(B.Budget - NotApproved - Approved) as AmountRemaining,
(B.Budget - Approved) as AmountApprovedRemaining
from BudgetStatus S
left outer join po_cfg_budget B on S.Years = B.BudgetYear
and S.Months = B.BudgetMonth
and S.DeptID = B.DeptID
and S.AccountID = B.AccountID
left outer join po_cfg_dept D on S.DeptID = D.BudgetDeptID
order by B.DeptID
If you have null dollar amounts you can wrap those in the ISNULL or
COALESCE function.
Bryce|||Not entirely sure what you are looking for but try this.
select S.*,
b.Budget,
B.Description,
D.DeptID as DeptDescription,
coalesce((B.Budget - NotApproved - Approved), 0) as AmountRemaining,
coalesce((B.Budget - Approved),0) as AmountApprovedRemaining
from po_cfg_budget B
left outer join BudgetStatus S
on S.Years = B.BudgetYear
and S.Months = B.BudgetMonth
and S.DeptID = B.DeptID
and S.AccountID = B.AccountID
left outer join po_cfg_dept D
on D.BudgetDeptID = S.DeptID
order by B.DeptID
The inline views that you were using were redundant.
"Dave" wrote:

> I have this current sql query which works great, but it only returns
> the rows that exist in all tables.
> I need to return all rows from po_cfg_Budget B and simply fill in 0 if
> there are no totals.
> Any help would be greatly appreciated.
> select S.*,
> b.Budget,
> B.Description,
> D.DeptID as DeptDescription,
> (B.Budget - NotApproved - Approved) as AmountRemaining,
> (B.Budget - Approved) as AmountApprovedRemaining
> from BudgetStatus S,
> (select * from po_cfg_budget) B,
> (Select * from po_cfg_dept) D
> where S.Years = B.BudgetYear
> and S.Months = B.BudgetMonth
> and S.DeptID = B.DeptID
> and S.AccountID = B.AccountID
> and S.DeptID = D.BudgetDeptID
> order by B.DeptID
>

join vs where

1.
select ...
from T1 inner join T2 on T1.c=T2.c
where T2.c1 = '...'
2.
select ...
from T1 inner join T2 on T1.c=T2.c and T2.c1 = '...'
Any reason not use the approach 2?nick wrote:
> 1.
> select ...
> from T1 inner join T2 on T1.c=T2.c
> where T2.c1 = '...'
> 2.
> select ...
> from T1 inner join T2 on T1.c=T2.c and T2.c1 = '...'
> Any reason not use the approach 2?
>
Compare the execution plans and query stats for each and see which looks
better.|||nick,
No differences when applying an inner join, except when grouping and using
"group by all". This behaviour is caused by the way SQL Server process the
query. Another different meaning when using outer join operators.
Example:
use northwind
go
select
a.orderid,
count(*)
from
orders as a
inner join
[order details] as b
on a.orderid = b.orderid
where
a.orderdate between '19980101' and '19981231'
group by all
a.orderid
select
a.orderid,
count(*)
from
orders as a
inner join
[order details] as b
on a.orderid = b.orderid
and a.orderdate between '19980101' and '19981231'
group by all
a.orderid
select
a.*
from
orders as a
left join
[order details] as b
on a.orderid = b.orderid
where
b.productid = 10
select
a.*
from
orders as a
left join
[order details] as b
on a.orderid = b.orderid
and b.productid = 10
go
AMB
"nick" wrote:

> 1.
> select ...
> from T1 inner join T2 on T1.c=T2.c
> where T2.c1 = '...'
> 2.
> select ...
> from T1 inner join T2 on T1.c=T2.c and T2.c1 = '...'
> Any reason not use the approach 2?
>|||For an equi-join, I don't think it matters; however, a LEFT JOIN will
give you very different results (it would return all the rows from T1,
and only the rows from T2 that it could match and have valid data in
T2.c1).
Does that help?
Stu
nick wrote:
> 1.
> select ...
> from T1 inner join T2 on T1.c=T2.c
> where T2.c1 = '...'
> 2.
> select ...
> from T1 inner join T2 on T1.c=T2.c and T2.c1 = '...'
> Any reason not use the approach 2?|||nick wrote:
> 1.
> select ...
> from T1 inner join T2 on T1.c=T2.c
> where T2.c1 = '...'
> 2.
> select ...
> from T1 inner join T2 on T1.c=T2.c and T2.c1 = '...'
> Any reason not use the approach 2?
From a technical point of view, they specify the same resultset.
However, I consider it a good practice to use the primary key/foreign
key columns in the JOIN clause, and all filtering conditions in the
WHERE clause.
This makes the query easier to read and understand, because it is in
line with the mental model of the query processing: the tables are
joined first. Only then are irrelevant rows filtered out. Also, it is
easier to understand, because the joins are usually pretty standard,
which means the real "work" is done in the WHERE clause.
So, asuming that the relation between table T1 and T2 is based on column
T1.c and T2.c, then I would never use syntax 2. I would change the
question: is there any reason not to use approach 1?
Gert-Jan

Join two table from different database. URGENT!

Hi,

anyone could help me on how to do the inner join of two table from two different database. Below is my scripts:

Dim sqlconnection1 As New SqlConnection(ConfigurationSettings.AppSettings("appDSN"))

Dim sqlCommand2 As New SqlCommand("", SqlConnection1)

Dim sqlconnection2 As New SqlConnection(ConfigurationSettings.AppSettings("smDSN"))

Dim sqlCommandSM2 as new sqlCommand("", sqlconnectionSM2)

Sub Page_Load(Source as Object, E as EventArgs)

sqlCommand2.CommandText = "Select * from invitation inner join Guest on invitation.guestid = Guest.Guestidinner join Department on Department.departmentcode = Guest.deptCode where eventcode = '" & ecode & "'"

sqlconnection1.Open()

.....................

The invitation table is from SqlConnection1 and the Department table is from the sqlconnection2. How to do this . Pls .. pls help me.

Ann123

In SQL Server, you can refer to tables in a different database inside the SQL using the appropriate syntax.

DatabaseName.Owner.TableName

For instance:

MyOtherDatabase.dbo.Guest

This presumes that the user specified has rights to both the default database in the connection as well as the second database.

|||

thanks so much douglas for your reply.

I know what you mean. In fact, i've done that in asp and its working well. However, i don't know how to do the syntax inasp.NET since i have to do 2 different connection and join them together in select statement using the inner join.

I ever try below syntax, but still got error.

Dim sqlconnection1 As New SqlConnection(ConfigurationSettings.AppSettings("appDSN"))

Dim sqlCommand2 As New SqlCommand("", SqlConnection1)

Dim sqlconnectionSM2 As New SqlConnection(ConfigurationSettings.AppSettings("smDSN"))

Dim sqlCommandSM2 as new sqlCommand("", sqlconnectionSM2)

sqlCommand2.CommandText = "Select * from invitation inner join Guest on invitation.guestid = Guest.Guestid inner joinsqlCommandSM2.SM2 .dbo.Department on Department.departmentcode = Guest.deptCode where eventcode = '" & ecode & "'"

TheSM2 is my Database andDepartment is refer to the table in SM2 Db.

Anybody know how to do the correct syntax??

thnks

Ann123

|||

Inside your SQL statement, you cannot reference you sqlCommandSM2, because that is a VB.NET variable, and the SQL Server getting the command will not know what that is. A simplified SQL statement using two databases is (presuming SM2 is the name of your other database):

sqlCommand2.CommandText = "Select * from invitation inner join Guest on invitation.guestid = Guest.Guestid inner joinSM2.dbo.Department onSM2.dbo.Department.departmentcode = Guest.deptCode where eventcode = '" & ecode & "'"

|||

hi douglas.. thanks once again for your reply.

i've done that but the problem now is,how to bindthe 2 sqlcommand when it executed since it connected to different sqlconnection and sqlcommand.

sqlCommand2.CommandText = "Select * from invitation inner join Guest on invitation.guestid = Guest.Guestid inner joinSM2.dbo.DepartmentonSM2.dbo.Department.departmentcode = Guest.deptCode where eventcode = '" & ecode & "'"

sqlconnection1.Open()

Dim employee2 As SqlDataReader =sqlCommand2.ExecuteReader()

.....

The sqlCommand2 is refer to sqlconnection1 whereas the second sqlCommandSM is refer to sqlconnection2 (connection for the SM2).

i've tried to do like this (below), so that it execute the same connection concurrently but got error.

Dim employee2 As SqlDataReader =sqlCommand2.ExecuteReader()

Dim employee2 As SqlDataReader =sqlCommandSM2.ExecuteReader()

How should i do the syntax ?

thanks.

Ann123

JOIN two Datasets?

I would like to perform an inner join on two seperate datasets. One dataset
gets its data from SQL server, the other gets its data from a XML web
service.Can't do this. You might consider using a sub report. A sub report can be
put into the table object cells.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jeff Richardson" <BobcatRidge@.newsgroups.nospam> wrote in message
news:O63%23UQuxGHA.3464@.TK2MSFTNGP03.phx.gbl...
>I would like to perform an inner join on two seperate datasets. One
>dataset gets its data from SQL server, the other gets its data from a XML
>web service.
>|||Thanks.
I have not tried the sub-report route because I assumed that you would get
all rows from the parent dataset even if the sub-report contained no rows.
Is there a way to that the parent rows are only displayed when the
sub-report has rows?
Thanks
Jeff.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:u1qBZauxGHA.3456@.TK2MSFTNGP03.phx.gbl...
> Can't do this. You might consider using a sub report. A sub report can be
> put into the table object cells.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Jeff Richardson" <BobcatRidge@.newsgroups.nospam> wrote in message
> news:O63%23UQuxGHA.3464@.TK2MSFTNGP03.phx.gbl...
>>I would like to perform an inner join on two seperate datasets. One
>>dataset gets its data from SQL server, the other gets its data from a XML
>>web service.
>|||Hi Jeff,
Although you could not control the visibility of a textbox with the
Subreport, you could add another dataset to control the visibility.
The dataset should be the same as the the one you use in the sub report.
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
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.

Join Translation in SQL Server

Hi
Can any one please translate these oracle inner join and outer join in SQL
Server. Any help will be highly appreciate.

SELECT V1.DB_VENDOR V1.NAME1,1,21)||DECODE(B1.JOINT_DESC,NULL,'',
'(JV)')||
DECODE(B1.NON_RESP,'Y','(NR)',
'N','') V1_NAME1,
B2.DB_CONTRACT B2_DB_CONTRACT, B2.BID_VENDOR B2_BID_VENDOR, B2.RANK_NUMB B2_RANK_NUMB,
V2.DB_VENDOR V2_DB_VENDOR,
SUBSTR(V2.NAME1,1,21)||DECODE(B2.JOINT_DESC,NULL,' ',
'(JV)')||
DECODE(B2.NON_RESP,'Y','(NR)',
'N','') V2_NAME1
FROM BID_TOTAL B1, BID_TOTAL B2, VENDOR V1, VENDOR V2
WHERE V1.DB_VENDOR = B1.BID_VENDOR
AND V2.DB_VENDOR (+) = B2.BID_VENDOR
AND B1.DB_CONTRACT = B2.DB_CONTRACT (+)
AND B1.RANK_NUMB > 1
AND MOD(B1.RANK_NUMB,2) = 0
AND B2.RANK_NUMB (+) = B1.RANK_NUMB + 1
AND B2.DB_CONTRACT IS NOT NULL
and b2.db_Contract=39624
ORDER BY B1.RANK_NUMBThey are all INNER JOINed. The (+) means (I think) the same as (INDEX=) hint. I'm having bigger problems with DECODE, don't have anything on Horacle handy at this point ;)|||any more help from some on on join?|||SELECT SUBSTR(V1.DB_VENDOR V1.NAME1,1,21)
+ CASE WHEN B1.JOINT_DESC IS NULL THEN '' ELSE '(JV)' END
+ CASE WHEN B1.NON_RESP = 'Y' THEN '(NR)'
WHEN B1.NON_RESP = 'N' THEN ''
END AS V1_NAME1
, B2.DB_CONTRACT AS B2_DB_CONTRACT
, B2.BID_VENDOR AS B2_BID_VENDOR
, B2.RANK_NUMB AS B2_RANK_NUMB
, V2.DB_VENDOR AS V2_DB_VENDOR
, SUBSTR(V2.NAME1,1,21)
+ CASE WHEN B2.JOINT_DESC IS NULL THEN '' ELSE '(JV)' END
+ CASE WHEN B2.NON_RESP = 'Y' THEN '(NR)'
WHEN B2.NON_RESP = 'N' THEN ''
END AS V2_NAME1
FROM BID_TOTAL B1
LEFT JOIN BID_TOTAL B2 ON B1.DB_CONTRACT = B2.DB_CONTRACT (+)
INNER JOIN VENDOR V1 ON V1.DB_VENDOR = B1.BID_VENDOR
RIGHT JOIN VENDOR V2 ON V2.DB_VENDOR (+) = B2.BID_VENDOR
WHERE B1.RANK_NUMB > 1
AND MOD(B1.RANK_NUMB,2) = 0
AND B2.DB_CONTRACT IS NOT NULL
AND b2.db_Contract=39624
ORDER BY B1.RANK_NUMB|||Hi Brett,

the mod function line is giving me error. I chanaged to this:
AND B1.RANK_NUMB % '2' = '0'
but it is still giving me error i.e.

Operand type clash: varchar is incompatible with void type
Server: Msg 8117, Level 16, State 1, Line 1
Operand data type numeric is invalid for modulo operator.

Thank you very much for immediate help.

waiting for response|||I forget what MOD does...do you know?|||Hi Brett.

Mod return the remainder of a division|||Hi Brett.

Mod return the remainder of a division

Well then that's a bizarre predicate

AND B1.RANK_NUMB/2.00-FLOOR(B1.RANK_NUMB/2.00) = 0|||AND cast(B1.RANK_NUMB as int) % 2 = 0|||What does this do again?

SELECT cast(100.00/20.00 as int) % 2|||Hi Brett,

Thank you very much...it worked

Join tables from different Db's

Hi,
I want to use a table from one db and do an inner join on a table which is
present in the other Db. Is it possible to do this in reporting Services? And
if yes, then how?
Thanks
--
pmuddoes a query like this doesn't works?:
select * from tableA inner join DatabaseB.dbo.TableB on ....
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:CE7B6ED2-9668-4B3D-BB9A-A40D3C422C57@.microsoft.com...
> Hi,
> I want to use a table from one db and do an inner join on a table which is
> present in the other Db. Is it possible to do this in reporting Services?
> And
> if yes, then how?
> Thanks
> --
> pmud

join table with procedure?

Is it possible to join the results of a stored procedure with a table?
SELECT field1, field2, field3 FROM tblCustomer INNER JOIN (exec spTest
'inputvar' AS SP) ON tblCustomer.CustID = SP.CustID
Thanks,
KeithKeith G Hicks,
You need to grab the result of the sp into a table (permanent or temporal)
and use the table in the join, or you can re-write the sp as a table functio
n.
create table #t1(c1 ...)
insert into #t1(c1, ...)
exec spTest 'inputvar'
SELECT field1, field2, field3
FROM tblCustomer INNER JOIN #t1 AS SP ON tblCustomer.CustID = SP.CustID
drop table #t1
go
How to share data between stored procedures
http://www.sommarskog.se/share_data.html
AMB
"Keith G Hicks" wrote:

> Is it possible to join the results of a stored procedure with a table?
> SELECT field1, field2, field3 FROM tblCustomer INNER JOIN (exec spTest
> 'inputvar' AS SP) ON tblCustomer.CustID = SP.CustID
> Thanks,
> Keith
>
>|||Cool. That's what I'm doing. I just wasn't sure if there was a way to join
directly to the procedure. Thanks for the info.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:8D7D36E8-8EF7-4E4B-8613-59F8BC64148C@.microsoft.com...
Keith G Hicks,
You need to grab the result of the sp into a table (permanent or temporal)
and use the table in the join, or you can re-write the sp as a table
function.
create table #t1(c1 ...)
insert into #t1(c1, ...)
exec spTest 'inputvar'
SELECT field1, field2, field3
FROM tblCustomer INNER JOIN #t1 AS SP ON tblCustomer.CustID = SP.CustID
drop table #t1
go
How to share data between stored procedures
http://www.sommarskog.se/share_data.html
AMB
"Keith G Hicks" wrote:

> Is it possible to join the results of a stored procedure with a table?
> SELECT field1, field2, field3 FROM tblCustomer INNER JOIN (exec spTest
> 'inputvar' AS SP) ON tblCustomer.CustID = SP.CustID
> Thanks,
> Keith
>
>

Join Styles

Hi,
Are there any difference(in performance) between these two type of join:
SELECT Customers.CustomerID,orders.orderid FROM Customers
INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
&
select c.customerid,o.orderid from customers c,orders o where
c.customerid=o.customerid
I tried them many times while STATISTICS IO/TIME were set on and couldn't
find considerable difference.
Thanks,
LeilaLeila...
These are simply two different ANSI standards. One is older than the other
one. In general SQL Server will build the exact same execution plan for
either of them. The only difference between the two is when there are OUTER
JOINS. The newer method of using the JOIN keyword is preferred and is part
of the SQL 99 standard.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"Leila" <leilas@.hotpop.com> wrote in message
news:%2348398bvEHA.2876@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Are there any difference(in performance) between these two type of join:
> SELECT Customers.CustomerID,orders.orderid FROM Customers
> INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
> &
> select c.customerid,o.orderid from customers c,orders o where
> c.customerid=o.customerid
> I tried them many times while STATISTICS IO/TIME were set on and couldn't
> find considerable difference.
> Thanks,
> Leila
>
>|||I find the INNER JOIN syntax more readable, it also allows you to separate
join criteria from filter criteria.
Try as you might, for inner joins with simple join criteria, I don't think
you'll ever see performance differences, because internally the engine sees
them as equivalent. Now if you have complex join and filter criteria, you
might be able to see differences if the filter takes place in the join as
opposed to after.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Leila" <leilas@.hotpop.com> wrote in message
news:#48398bvEHA.2876@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Are there any difference(in performance) between these two type of join:
> SELECT Customers.CustomerID,orders.orderid FROM Customers
> INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
> &
> select c.customerid,o.orderid from customers c,orders o where
> c.customerid=o.customerid
> I tried them many times while STATISTICS IO/TIME were set on and couldn't
> find considerable difference.
> Thanks,
> Leila
>
>|||Leila:
The ANSI style (INNER JOIN) is better because for complex queries, the
optimizer can more easily select a better execution plan than for the old
style query; you won't find differences in simple queries though.
You might as well stop cross-posting.
--
Regards,
Eric Garza
AMIGE
"Leila" <leilas@.hotpop.com> wrote in message
news:%2348398bvEHA.2876@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Are there any difference(in performance) between these two type of join:
> SELECT Customers.CustomerID,orders.orderid FROM Customers
> INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
> &
> select c.customerid,o.orderid from customers c,orders o where
> c.customerid=o.customerid
> I tried them many times while STATISTICS IO/TIME were set on and couldn't
> find considerable difference.
> Thanks,
> Leila
>
>|||http://www.microsoft.com/sql/techinfo/tips/development/july23.asp
AMB
"Leila" wrote:
> Hi,
> Are there any difference(in performance) between these two type of join:
> SELECT Customers.CustomerID,orders.orderid FROM Customers
> INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
> &
> select c.customerid,o.orderid from customers c,orders o where
> c.customerid=o.customerid
> I tried them many times while STATISTICS IO/TIME were set on and couldn't
> find considerable difference.
> Thanks,
> Leila
>
>

join results from multiple EXEC calls

I would like to make inner join with results from different exec %procedureName% calls.

thanks.

insert into #temp
exec Proc1

insert into #temp2
exec Proc2

insert into #temp3
exec Proc3

Then join or union the tables (depending on the data)
If the reultsets are the same you can insert into 1 table instead of multiple tables

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

You might also consider rewriting the procedures as functions if it is possible, then the join of the sets would be very natural.

select *
from function (parm) as set1
join function2(parm) as set2
on set1.value = set2.value

Join on a third table

I currently have a join on 2 tables. An abbreviation of the code is as follow:

sql = "SELECT pd.RaceID, ir.EventPl, ir.RacePl FROM PartData pd INNER JOIN IndResults ir ON pd.PartID = ir.PartID WHERE pd.RaceID = " & lRaceID & " ORDER By EventPl"

Here's the problem: I need to change the WHERE clause to a field value from another table. How do I incorporate a join with a third table?

Thanks!select T1.*, T3.*
from T1 join T2 on T1.id=T2.id join T3 on T2.altid = T3.id|||...do I need to use JOIN as opposed to INNER JOIN? Also, do I need to give T2 and altid?

Thanks!|||Have you gone thru JOINS topic in books online, has got code examples so.|||I will do that!