join or subselect: joins

Showing posts with label joins. Show all posts

Monday, March 26, 2012

Joins??

Hi,
I am stuck with a problem...
I have to query dat from two tables...
PO_hdr and po_addl_cost
now some po's have additional costs and if they do have there will be an entry in po_addl_cost table. They are linked via the PO_GRP_NO.

Now I want to get an extract of data of specific fields..for all po's

I want the extract to show
po_no po_desc po_cost po_addl_costid po_addl_cost_value

The first three fields are from po_hdr and the last two from po_addl_cost

now if there are no entries for that particular po_grp_no i want the two fields blank but still want the other data.

This is my query:
select po.po_no,po.PO_PROJ_NM,po.LOGIN_ID,addl.PO_ADDL_CO ST_TYPE_ID,addl.PO_ADDL_COST_BUY_PRICE from po_hdr po,po_addl_cost_dtl addl
where
po.SITE_ID=41
And po.PO_NO in(287,58)
and po.STATUS_CD=5
and addl.SITE_ID=41
and addl.STATUS = 'A'
and addl.PO_GRP_NO=po.PO_GRP_NO

Pleaseeeeeeeeeeee help!!!select po.PO_NO, po.PO_PROJ_NM, po.LOGIN_ID, addl.PO_ADDL_COST_TYPE_ID, addl.PO_ADDL_COST_BUY_PRICE
from po_hdr po
LEFT OUTER JOIN
po_addl_cost_dtl addl ON
addl.PO_GRP_NO=po.PO_GRP_NO AND
po.SITE_ID=41 AND
po.PO_NO in(287,58) AND
po.STATUS_CD=5 AND
addl.SITE_ID=41 AND
addl.STATUS = 'A';|||Thanks , but I am getting an error when trying to execute this using toad ...it gives ORA-00933 sql comman not properly ended ...highlighting "LEFT"|||just a guess but perhaps your version of oracle does not support LEFT OUTER syntax

you will need to use that silly plus sign in parentheses and i'm sorry i can't remember which side of the equal sign it goes on

(sorry for the sarcasm but the sql standard for JOIN syntax has been out for, what, over a decade? and oracle finally decided to implement it in oracle 9?)|||All sarcasm welcome...
but I amstill having issues...
first of all from what i remember the query with (+) goes like this

select po.PO_NO, po.PO_PROJ_NM, po.LOGIN_ID, addl.PO_ADDL_COST_TYPE_ID, addl.PO_ADDL_COST_BUY_PRICE
from po_hdr po ,po_addl_cost_dtl addl where
addl.PO_GRP_NO=po.PO_GRP_NO (+)
AND po.SITE_ID=41
AND po.PO_NO in(287,58) AND po.STATUS_CD=5
AND addl.SITE_ID=41 AND addl.STATUS = 'A';

I have absolutely no idea of joins...but this doesnt seem to retireve two rows...which is what i want.
it gives just one row po_no of which is present in the addl_cost table.|||Originally posted by r937
you will need to use that silly plus sign in parentheses and i'm sorry i can't remember which side of the equal sign it goes on

(sorry for the sarcasm but the sql standard for JOIN syntax has been out for, what, over a decade? and oracle finally decided to implement it in oracle 9?)
It goes on the "outer" (dark) side:

select po.PO_NO, po.PO_PROJ_NM, po.LOGIN_ID, addl.PO_ADDL_COST_TYPE_ID, addl.PO_ADDL_COST_BUY_PRICE
from po_hdr po,
po_addl_cost_dtl addl
where
addl.PO_GRP_NO(+)=po.PO_GRP_NO AND
po.SITE_ID=41 AND
po.PO_NO in(287,58) AND
po.STATUS_CD=5 AND
addl.SITE_ID(+)=41 AND
addl.STATUS (+)= 'A';

But tell me: what is "LEFT" about an outer join? Especially when if written on one line the "outer" table appears on the right... ;o)|||That worked!!! thanks a lot!!!!!|||dunno which one you'd call the outer table, but it's trivial to decide which one's the left table

here, give it a try --

... FROM FOO LEFT OUTER JOIN BAR

now, you've got FOO on the left, and BAR on the right, right?

so, um, FOO is the left table and BAR is the right table

gee i hope i've got that right :cool:

i know it's probably confusing because when i write sql i never put them on the same line, i always write them on separate lines like this --

FROM FOO
LEFT OUTER
JOIN BAR

but that's because i'm an old keyboard jockey, and when i edit text, for example to replace INNER with LEFT OUTER as sometimes is necessary, then i use the arrow keys to position myself on that line, press the Home key if i'm not at the front of the line, and then while pressing the shift key, arrow down to highlight the entire line, and begin typing the replacement text

i don't use a mouse for text editing, and consequently prefer to have stuff on multiple source lines|||Hmm, maybe I've always had it wrong about what the word "outer" really means in this context. I would have called BAR the "outer" table in your example, because in my warped mind you sort of stick the matching rows from BAR on the "outside" of the FOO records...?

But if LEFT OUTER implies that the "outer" table is on the left (i.e FOO), then perhaps the analogy is more with program logic:

-- Outer query
for foo_row in (select * from foo) loop
-- Inner query
begin
select * into bar_row from bar where ...;
exception
when no_data_found then
bar_row := null;
end;
Display(foo_row, bar_row);
end loop;

Presumably there is a RIGHT OUTER that does the opposite?|||yes, RIGHT OUTER is the opposite of LEFT OUTER

did not really understand your code, there is no looping in sql ;)

i would not get into the semantic morass of which one to call the outer table, since in an outer join, one of the tables brings a few extra rows to the table (if you'll pardon the pun), i.e. extra rows which aren't there in the inner join, so these extra rows would be outside the inner rows, and since in a LEFT join they come from the left table, it might make more sense to call the left table the outer table, if you know what i mean

in any case, like i said, i don't call either of them the outer table, i just use the words left and right, because there's no ambiguity there

sample data:

Pets
1 dog
2 cat
3 bird
4 ferret

People
35 curly
38 larry
39 moe

PeoplePets
35 2
35 3
39 1

list all pets, and their people if any (RIGHT join) --

moe dog
curly cat
curly bird
NULL ferret

see this other thread (http://www.dbforums.com/showthread.php?threadid=976339&postid=3597190#post3597190) for LEFT and INNER joins|||Well, my code was supposed to represent what SQL might be doing "under the covers". Or at least, the procedural code you could write to simulate an outer join.

Yes, I agree there is nothing ambigous about LEFT and RIGHT, but then there is nothing particularly meaningful either:

Originally posted by r937
... FROM FOO LEFT OUTER JOIN BAR

now, you've got FOO on the left, and BAR on the right, right?

so, um, FOO is the left table and BAR is the right table

My response to that is:

Originally posted by me
... FROM FOO RIGHT OUTER JOIN BAR

now, you've got FOO on the left, and BAR on the right, right?

so, um, FOO is the left table and BAR is the right table

What's the difference? ;o)

I am sure that the word OUTER must be intended to convey some meaning, but I am no longer so sure what that meaning is...|||i wrote

... FROM FOO LEFT OUTER JOIN BAR

and you suggested

... FROM FOO RIGHT OUTER JOIN BAR

and then asked "What's the difference?"

well, the difference is, the first is a left outer join, and the second is a right outer join

did my people/pets example not help?

lemme know when you want to get into the FULL OUTER JOIN

:cool:|||oh, and by the way, i never write RIGHT OUTER joins anyway

i always re-write them as LEFT OUTER joins

that's because

... FROM FOO RIGHT OUTER JOIN BAR

is exactly equivalent to

... FROM BAR LEFT OUTER JOIN FOO

helps?|||I have absolutely no problem understanding what LEFT, RIGHT and FULL outer joins do, I just don't quite understand why LEFT and RIGHT are so named!

Joins with XQuery

Hello,
I have a very simple data table:

CREATE TABLE [ALMPayloads]([ID] [int] NOT NULL,[OutputPayload] [xml] NOT NULL)

with the following content:

ID = 1
OutputPayload:

<ReportDocument>
<ALMSimulationResult>
<selectedModelAssets>
<modelAsset ID="8bc798ae-cc15-4807-8805-61ecfc8f3c01" description="Global Bond" internationalCode=" " minimumLimit="0" maximumLimit="1" annualManagementFee="0" annualPerformanceFee="0" initialCostUpFront="0.02" regularCostUpFront="0.015" withdrawingCommission="0" switchCostPercentage="0" switchCostAmount="0" color="#FF00FFD4" stochasticDuration="5">
<models>
<model ID="0e70216f-48ce-4f6c-b2d6-519a5cdfd246" type="corporate grade bond" description="Eurozone Corporate Bond Intermediate (D=5Years)" weight="1" />
</models>
</modelAsset>
<modelAsset ID="eab258b2-57ba-4d67-9f36-ee4e17c10dec" description="America Value Fund" internationalCode=" " minimumLimit="0" maximumLimit="1" annualManagementFee="0" annualPerformanceFee="0.005" initialCostUpFront="0.03" regularCostUpFront="0.03" withdrawingCommission="0" switchCostPercentage="0" switchCostAmount="0" color="#FF3B00FF" stochasticDuration="13.55">
<models>
<model ID="b0817f64-5090-48a3-b58c-aa8f6e5bbdc1" type="equity" description="US Value Style (Eur)" weight="0.9" />
<model ID="677e8aae-7b32-4dc3-88c5-e9302dddad8f" type="conventional bond" description="Euro Cash (TBill)" weight="0.1" />
</models>
</modelAsset>
<modelAsset ID="0e2e95bb-bec0-4dcb-bb13-2032f3ed0978" description="Europa Value Fund" internationalCode=" " minimumLimit="0" maximumLimit="1" annualManagementFee="0.001" annualPerformanceFee="0.001" initialCostUpFront="0.03" regularCostUpFront="0.03" withdrawingCommission="0" switchCostPercentage="0" switchCostAmount="0" color="#FF9D00FF" stochasticDuration="17.5">
<models>
<model ID="86fedd24-2a92-422c-b733-17c60105ff81" type="equity" description="Asia Value Style (Eur)" weight="0.1" />
<model ID="55425529-8adc-47d8-a36d-8cfd9da34880" type="conventional bond" description="Italian Long Term Gov Bond" weight="0.1" />
<model ID="fea29db9-cf0e-4802-bcbe-e2b8d367f0ca" type="cash" description="Euro Cash (Euribor 1m)" weight="0.1" />
<model ID="8e58d785-5fc5-4ede-8ec8-eb1af8e62541" type="equity" description="Eurozone Value Style" weight="0.7" />
</models>
</modelAsset>
</selectedModelAssets>
<savingModelAsset ID="0e2e95bb-bec0-4dcb-bb13-2032f3ed0978" />
<surplusModelAsset ID="0e2e95bb-bec0-4dcb-bb13-2032f3ed0978" />
<modelAssetTimeSeries>
<modelAsset ID="8bc798ae-cc15-4807-8805-61ecfc8f3c01">
<tValues t="0" value="0" annualYield="0" />
<tValues t="1" value="0" annualYield="0.027353" />
<tValues t="2" value="0" annualYield="0.027288" />
<tValues t="3" value="0" annualYield="0.027237" />
<tValues t="4" value="0" annualYield="0.027274" />
<tValues t="5" value="0" annualYield="0.027262" />
<tValues t="6" value="0" annualYield="0.02722" />
<tValues t="7" value="1453" annualYield="0.027258" />
<tValues t="8" value="1457" annualYield="0.027258" />
<tValues t="9" value="1460" annualYield="0.027219" />
<tValues t="10" value="1463" annualYield="0.027259" />
</modelAsset>
<modelAsset ID="eab258b2-57ba-4d67-9f36-ee4e17c10dec">
<tValues t="0" value="0" annualYield="0" />
<tValues t="1" value="0" annualYield="0.065466" />
<tValues t="2" value="0" annualYield="0.063841" />
<tValues t="3" value="0" annualYield="0.063707" />
<tValues t="4" value="0" annualYield="0.063692" />
<tValues t="5" value="0" annualYield="0.062438" />
<tValues t="6" value="0" annualYield="0.064081" />
<tValues t="7" value="0" annualYield="0.063476" />
<tValues t="8" value="0" annualYield="0.064294" />
<tValues t="9" value="0" annualYield="0.062034" />
<tValues t="10" value="0" annualYield="0.065144" />
</modelAsset>
<modelAsset ID="0e2e95bb-bec0-4dcb-bb13-2032f3ed0978">
<tValues t="0" value="830" annualYield="0" />
<tValues t="1" value="1641" annualYield="0.06504" />
<tValues t="2" value="2456" annualYield="0.063229" />
<tValues t="3" value="3278" annualYield="0.062939" />
<tValues t="4" value="4104" annualYield="0.062825" />
<tValues t="5" value="4935" annualYield="0.061233" />
<tValues t="6" value="5772" annualYield="0.063522" />
<tValues t="7" value="5155" annualYield="0.062448" />
<tValues t="8" value="5994" annualYield="0.063548" />
<tValues t="9" value="6837" annualYield="0.061053" />
<tValues t="10" value="7688" annualYield="0.06525" />
</modelAsset>
</modelAssetTimeSeries>
</ALMSimulationResult>
</ReportDocument>

When I run the following command from SQL server:

SELECT
N.ma.value('@.ID', 'uniqueidentifier') as ModelAssetID,
N.ma.value('@.description', 'nvarchar(255)') as ModelAssetDescription,
N.ma.value('@.minimumLimit', 'float') as ModelAssetMinLimit,
N.ma.value('@.maximumLimit', 'float') as ModelAssetMaxLimit,
N.ma.value('@.stochasticDuration', 'float') as ModelAssetDuration,
N.ma.value('@.color', 'char(9)') as Color,
N1.ma1.value('tValues[1]/@.value', 'float') as ActualAssetMix
FROM ALMPayloads A1 CROSS APPLY OutputPayload.nodes('/ReportDocument/ALMSimulationResult/selectedModelAssets/modelAsset') N(ma)
LEFT JOIN ALMPayloads A2 CROSS APPLY OutputPayload.nodes('/ReportDocument/ALMSimulationResult/modelAssetTimeSeries/modelAsset') N1(ma1)
ON N.ma.value('@.ID', 'uniqueidentifier') = N1.ma1.value('@.ID', 'uniqueidentifier')
WHERE A1.ID = 1000
ORDER BY ModelAssetDuration

I get the following result:

ModelAssetID ModelAssetDescription Min Max Dur. Color Value
8BC798AE-CC15-4807-8805-61ECFC8F3C01 Global Bond 0 1 5 #FF00FFD4 0
8BC798AE-CC15-4807-8805-61ECFC8F3C01 Global Bond 0 1 5 #FF00FFD4 0
EAB258B2-57BA-4D67-9F36-EE4E17C10DEC America Value Fund 0 1 13.55 #FF3B00FF 0
EAB258B2-57BA-4D67-9F36-EE4E17C10DEC America Value Fund 0 1 13.55 #FF3B00FF 0
0E2E95BB-BEC0-4DCB-BB13-2032F3ED0978 Europa Value Fund 0 1 17.5 #FF9D00FF 96803
0E2E95BB-BEC0-4DCB-BB13-2032F3ED0978 Europa Value Fund 0 1 17.5 #FF9D00FF 830
0E2E95BB-BEC0-4DCB-BB13-2032F3ED0978 Europa Value Fund 0 1 17.5 #FF9D00FF 830

Instead of

ModelAssetID ModelAssetDescription Min Max Dur. Color Value
8BC798AE-CC15-4807-8805-61ECFC8F3C01 Global Bond 0 1 5 #FF00FFD4 0
EAB258B2-57BA-4D67-9F36-EE4E17C10DEC America Value Fund 0 1 13.55 #FF3B00FF 0
0E2E95BB-BEC0-4DCB-BB13-2032F3ED0978 Europa Value Fund 0 1 17.5 #FF9D00FF 830

Why do I have so much duplicates and some random results (I mean the value where I get 96803) ?

Thanks,
Pierre

Pierre, I just tried running the following query. At first, I got no results. When I changed the AI.ID predicate to AI.ID = 1 then I got three rows back (your expected results).

Are you sure there is not other data in the table? I notice that you are doing a self join on the the table [ALMPayloads] but I don't see any predicate on the A2 table alias. I suspect that this could be the reason why you are seeing the additional rows in your results if in fact there are other rows in that table.|||Hi John,
try to add another record (the same xml content with 2 different IDs) and the result will be duplicated.

How can I make the join (on xml data) with the expected result ?

Thanks,
Pierre|||You need to add "AND A1.ID = A2.ID" to your join condition. If you don't need the LEFT JOIN semantics, you can do away with the self join and add another CROSS APPLY.

ALMPayloads A1
CROSS APPLY OutputPayload.nodes('/ReportDocument/ALMSimulationResult/selectedModelAssets/modelAsset') N(ma)CROSS APPLY OutputPayload.nodes('/ReportDocument/ALMSimulationResult/modelAssetTimeSeries/modelAsset') N1(ma1)

sql

Joins with XQuery

Hello,
I have a very simple data table:

CREATE TABLE [ALMPayloads]([ID] [int] NOT NULL,[OutputPayload] [xml] NOT NULL)

Why do I have so much duplicates and some random results (I mean the value where I get 96803) ?

Thanks,
Pierre

Pierre, I just tried running the following query. At first, I got no results. When I changed the AI.ID predicate to AI.ID = 1 then I got three rows back (your expected results).

How can I make the join (on xml data) with the expected result ?

Thanks,
Pierre|||You need to add "AND A1.ID = A2.ID" to your join condition. If you don't need the LEFT JOIN semantics, you can do away with the self join and add another CROSS APPLY.

Joins Vs Where clause - Performance Query

Hi There !!

To finetune performance for some of our queries,

I have come across suggestions to use

- JOINS instead of WHERE clause wherever possible
- and avoid using Aliases

Although Avoiding aliases looks reasonable I am yet to be convinced about JOINS replacing the WHERE CLAUSE . What is the experts take on this one ??

Also,

I checked the estimated plan in SQL server by running the following 2 queries into my Query Designer

tables : dba ( empid, empname )
project ( project_empid references dba.empid, project_name )

USING A WHERE CLAUSE and Alias
--------
select a.emp_name from dbo.dba a, dbo.project b
where
a.empid =b.project_emp
and b.project_name is not null

USING A JOIN
------
select emp_name from dbo.dba
as
a inner JOIN dbo.project
ON empid = dbo.project.project_emp
AND dbo.project.project_name is not NULL

******

I find from the Estimated plan that both the queries give the same amount of cost ( I/O, CPU, et all ) :shocked:

Any comments/ suggestions.

Thanks,

Have a great time
-Ranjit.

------------
It pays to be honest to your DBAmy experience is that 99% of the time, the optimizer is smart enough to generate the same plan regardless of whether you use the ansi join syntax or not. I prefer the ansi syntax just for purity's sake however.

If you haven't already, you should measure first (using profiler) to find where the bottlenecks are. Only after you have measured can you begin to address perf issues.

Finally, I am fairly certain that changing from one join syntax to another is not going to fix any perf issues you may have.|||Finally, I am fairly certain that changing from one join syntax to another is not going to fix any perf issues you may have.Agreed - ANSI syntax is merely convention (although of course you can do more than an inner join with ANSI).

and avoid using AliasesNope again - this is just a convention too. Some people think aliases make code easier to read, blindman does not. :)

I don't know where you stand in performance tuning experience but everyone of any level can find something of use here:
http://www.sql-server-performance.com/articles_performance.asp

HTH|||avoiding aliases is not "reasonable"

:)|||Some people think aliases make code easier to read, blindman does not. :)
My reputation preceeds me.
But even I don't claim the aliases hurt performance.

Joins vs SubQueries

Hi,

Can any one please let me know which one is better in performance, Joins or sub-queries?

Any other differences between joins and sub-queries plz let me know.

Thanks

Pradeep

pradeepyr:

I would suggest that the best way to answer your question is to take quick benchmarks whenever you have doubt. In general (1) look at the execution plan, (2) query execution time and (3) query IO statistics and judge based on this information. This is all information that is readily obtainable from the Query Analyzer in SQL Server 2000 and SQL Server Management Studio in SQL Server 2005.

Dave

|||

joins and subqueries solve different purposes, and cant/may not be replaced just for the sake of it...

**use a subquery when u may not need the column from the table used the subquery in the result set of outer query.....

join is usually easy to optimize than a subquery... as join joins the whole tables based on a condition, use it when u retrive more data....

all said if u have a confusion, and choice of using both,(both givin same result), check the query plans, and decide, or simply go for joins , excluding the one condition i mentioned**..

Joins versus relationships

If a database has relationships establshed between all of the tables
via primary and foreign key constraints, why isn't is possible to make
a SELECT statement across multiple tables without using a JOIN?

If the system knows the relationsip schema already why are JOINS
required?

Thanks,
HCHi

It is not always that easy! Not every database is fully normalised and there
can be mutiple relationships or missing ones. Doing the extra work to figure
out the relationship is going to take extra time and resource.

Also, how do you declare the different types of JOIN?

John

"H Cohen" <harris_cohen@.yahoo.com> wrote in message
news:1545331c.0408150629.1ffa0575@.posting.google.c om...
> If a database has relationships establshed between all of the tables
> via primary and foreign key constraints, why isn't is possible to make
> a SELECT statement across multiple tables without using a JOIN?
> If the system knows the relationsip schema already why are JOINS
> required?
> Thanks,
> HC|||"H Cohen" <harris_cohen@.yahoo.com> wrote in message
news:1545331c.0408150629.1ffa0575@.posting.google.c om...
> If a database has relationships establshed between all of the tables
> via primary and foreign key constraints, why isn't is possible to make
> a SELECT statement across multiple tables without using a JOIN?
> If the system knows the relationsip schema already why are JOINS
> required?
> Thanks,
> HC

Well, for a start what type of join would it be - inner, outer, cross? And
what would you join on - you might not want to join on col1 = col2, you
might want to join on col1 < col2, or col1-1 = col2 etc. Or you might want
to join on non-key columns. And you would have to assume that every database
is normalised, there is only one possible relationship between each pair of
tables, and all the relationships are enforced correctly, which is unlikely
to be true all the time.

If you specify what you want explicitly then it's clear to others reading
your code what you intended, and it also makes it easier to handle schema
changes and other code changes without the added confusion of the system
'automagically' doing things for you.

I suspect you're thinking mainly of the simplest possible case - join two
tables with an inner join using an equality comparison. While I suppose you
could introduce some kind of meta-syntax to avoid fully typing out the
primary key column names, that would be a false economy compared to the
potential issues, and of course it wouldn't work at all in some of the cases
I mention above.

Simon|||>> If a database has relationships establshed between all of the
tables via primary and foreign key constraints, why isn't is possible
to make a SELECT statement across multiple tables without using a
JOIN? <<

UNH?? A SELECT statement with two or more tables in the FROM clause
has at least a CROSS JOIN in it, even without a WHERE clause.

>> If the system knows the relationship schema already why are JOINS
required? <<

For the same reason you have to do math to get answers from numbers.
This makes no sense. Are you thinking about an old network database
like IMS or IDMS, or whatever that had pointer chains to navigate
along pre-defined acces paths?|||HC,

I believe Oracle and possibly other database systems implement something like NATURAL JOIN which infers a join condition of equality on
all like-named columns, but SQL Server always requires the join condition to be supplied.

Steve Kass
Drew University

H Cohen wrote:

> If a database has relationships establshed between all of the tables
> via primary and foreign key constraints, why isn't is possible to make
> a SELECT statement across multiple tables without using a JOIN?
> If the system knows the relationsip schema already why are JOINS
> required?
> Thanks,
> HC

JOINS to Sub-Queries -vs- JOINS to Tables

SQL Server 2000

Howdy All.

Is it going to be faster to join several tables together and then
select what I need from the set or is it more efficient to select only
those columns I need in each of the tables and then join them together
?

The joins are all Integer primary keys and the tables are all about the
same.

I need the fastest most efficient method to extract the data as this
query is one of the most used in the system.

Thanks,

CraigOn 11 Aug 2005 09:24:08 -0700, csomberg@.dwr.com wrote:

>SQL Server 2000
>Howdy All.
>Is it going to be faster to join several tables together and then
>select what I need from the set or is it more efficient to select only
>those columns I need in each of the tables and then join them together
>?
>The joins are all Integer primary keys and the tables are all about the
>same.
>I need the fastest most efficient method to extract the data as this
>query is one of the most used in the system.
>Thanks,
>Craig

Hi Craig,

I'm not sure I understand your question. Are you asking about the
performance difference between queries like these two?

SELECT A.something, B.otherthing
FROM TableA AS A
INNER JOIN TableB AS B
ON A.xxx = B.xxx
WHERE A.yyy = y
AND B.zzz = z

SELECT A.something, B.otherthing
FROM (SELECT xxx, something
FROM TableA
WHERE A.yyy = y) AS A
INNER JOIN (SELECT xxx, otherthing
FROM TableB
WHERE B.zzz = z) AS B
ON A.xxx = B.xxx

My first guess is that there will be no difference. The optimizer is
free to rearrange the query every way it wants, as long as the end
results are the same. They will probably result in the same execution
plan.

On the other hand, it is very hard to predict what the optimizer will
do. It often does a good job, but there still are situations where it
shows that it's just a program.

If you really want to be sure, then why don't you simply test both
against your system?

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||(csomberg@.dwr.com) writes:
> Is it going to be faster to join several tables together and then
> select what I need from the set or is it more efficient to select only
> those columns I need in each of the tables and then join them together
> ?
> The joins are all Integer primary keys and the tables are all about the
> same.
> I need the fastest most efficient method to extract the data as this
> query is one of the most used in the system.

Your query is open to several interpretations, so the answers you get
may not address your real issue.

If your idea is to first join two tables, get those columns into
a temp table, join that with the next table, then this is generally
not a good idea. Although, when it comes to performance there a few
definitive answers. For a certain query, this could actually be a
good strategy. But as a general approach, it's better to throw in
all tables into one query.

And you should not use SELECT * - only list the columns you actually
need.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> I need the fastest most efficient method to extract the data as this query is one of the most used in the system. <<

Then test them. But my guess is that the optimizer will do them the
same way. Putting all the tables in the FROM clause will be much easier
to read and maintain, however.sql

Joins Question

I have 3 tables. Table1(time,readingA) Table2(time,readingB)
table3(time,readingC)
Now the time can be same and it can be different. Now i want to know
how do i join so that i get the data: time,readingA,readingB,readingC
If the time is same then it is fine, but if the time is not same in
two tables for eg : if table1 has a record for time 12:30 and Table2
and table3 does not have that time then it should show data from
table1 and the readingB and readingC will be blank.
I hope my question is clear.
Thanks for helpHi
If you are not interested in the time then you may not want to truncate
everything to midnight when they are inserted (which if you don't have a tim
e
portion on your date/time will happen anyhow). The isssue then is what will
happen if there are multiple records for each day? If the time is require fo
r
some other reason you can also use the convert function to compare the date
part of the datetime
SELECT CONVERT(char(8),T1.time,112) AS Time, T1.readingA, T2.readingB,
T3.readingC
FROM Table1 T1
JOIN Table2 T2 ON CONVERT(char(8),T1.time,112) = CONVERT(char(8),T2.time,112
)
JOIN Table3 T3 ON CONVERT(char(8),T1.time,112) = CONVERT(char(8),T3.time,112
)
John
"Pradeep" wrote:

> I have 3 tables. Table1(time,readingA) Table2(time,readingB)
> table3(time,readingC)
> Now the time can be same and it can be different. Now i want to know
> how do i join so that i get the data: time,readingA,readingB,readingC
> If the time is same then it is fine, but if the time is not same in
> two tables for eg : if table1 has a record for time 12:30 and Table2
> and table3 does not have that time then it should show data from
> table1 and the readingB and readingC will be blank.
>
> I hope my question is clear.
> Thanks for help
>|||Hi,
if I understand correctly your question, you need to use left join.
Something like
SELECT T1.time AS Time, T1.readingA, T2.readingB,
T3.readingC
FROM Table1 T1
LEFT JOIN Table2 T2
ON T2.time=T2.time
LEFT JOIN Table3 T3
ON T1.time= T3.time
"Pradeep" <agarwalp@.eeism.com> wrote in message
news:364c5b9b.0502012334.5b8f2955@.posting.google.com...
>I have 3 tables. Table1(time,readingA) Table2(time,readingB)
> table3(time,readingC)
> Now the time can be same and it can be different. Now i want to know
> how do i join so that i get the data: time,readingA,readingB,readingC
> If the time is same then it is fine, but if the time is not same in
> two tables for eg : if table1 has a record for time 12:30 and Table2
> and table3 does not have that time then it should show data from
> table1 and the readingB and readingC will be blank.
>
> I hope my question is clear.
> Thanks for help|||It looks like I may have got this mixed up! As Ana says use left JOIN
although you may not want your times to 3/100 of a second, in which
case you will still need to truncate them
SELECT T1.Time, T1.readingA, T2=AD.readingB,
T3.readingC
FROM Table1 T1
LEFT JOIN Table2 T2 ON T1.time =3D T2.time
LEFT JOIN Table3 T2 ON T1.time =3D T3.time
John

Joins query help

Hi all,

Just after some help with a query (Stored Procedure) I've managed to get wrapped round my head.

The DB is as such:

COMPANY
Company_id
Company_name

COMPANY_GROUP
Group_id
Group_name

USER
User_id
User_name

Bridging tables

COMPANY_GROUP_BRIDGE
company_id
group_id

USER_COMPANY_BRIDGE
user_id
company_id

Basically, the only parameter I have for the query is a User_id.

I need to get the Group linked to the User and return all the companies within that group.

I'vetried reading up on all the join types again but have just got thiscompletely wrapped round my neck. I keep thinking along the lines ofSELECT all the companies linked to all the groups linked to all thecompanies linked to the User_id :s I must be able to dothis without using two Company tables...?

Any help much appreciated,

Pete

I don't think the table structure is good. You only need one bridge table in stead of two. In this one table, just put company_id, group_id, and user_id together. Otherwise, you will join too much.

|||

There seems to be a contradiction here. You wrote

"I need to get the Group linked to the User and return all the companies within that group."

But you don't have any table that stores the users assigned to a company group.

In case a user belongs to a group and not a company, create a table that will store user id and group id.

Then you can write a query to get all the companies linked to the group to which the user belongs

|||

Cheers for the replies guys, you've pretty much asserted what I've been dreading all along.

The DB is a complete messed up and looks like I'm gonna have to completely overhaul it :/

(BTW:It is not currently possible for a user to be linked to a group but nota company. The groups are not in there for this purpose - In fact Imnot sure why there are there!)

|||

Hmm OK,

Any reccomendations on what a DB 'should' look like based on this~~very~~extremely light spec:

A Distributor can have many Resellers
A Reseller can have many Groups
A Group can have many Companies
A Company can have many Users
A User may belong to many Companies

(Looking at it - it has been designed so that a Company can belong to many Groups but I dont think that would ever happen )

I think the problem originally was that a User may belong to certaincompanies within a group but not neccessarilly all of them - hence theUser is linked to individual companies rather than a Group.

Also, in the admin backend the problem is which companies to show for options such as edit etc.

A Group Administrator may see all the companies within a group but aCompany Administrator may only see the Companies they are associatedwith in the bridge table. Looks like I'll need the User's'access_level' in all the SProcs.

OK, back to basics. My head hurts.

|||

It is often helpful to think about it this way: every noun is a table, so:

Distributor table
Reseller table
Group table
Company table
User tables

A Distributor can have many Resellers
A Reseller can have many Groups
A Group can have many Companies
A Company can have many Users
A User may belong to many Companies

Every time you write, "can have many" it implies the presence of a foreign key (FK). Many-to-many relationships (company-user) requires a cross reference table. So:

Distributor table
- Distributor ID (probably an identity column)
- Reseller ID (FK to Reseller table)
- other fields

Reseller table
- Reseller ID (probably an identity column)
- Group ID (FK to Group table)
- other fields

Group Table
- Group ID (probably an identity column)
- Company ID (FK to Company table)
- other fields

Company Table
- Company ID (probably an identity column)
- other fields

User Table
- User ID (probably an identity column)
- other fields

Company-User-Cross-Reference Table
- Company ID (FK to Company table)
- User Id (FK to User Table)
NOTE: The Primay Key of this table is Company ID + User ID

|||

Hi David, thanks for that!

That does reassure me that I am on the right track with my structure as that is the way I have gone about things.

I think I rushed into it though and ended up creating astructure where a group could belong to multiple resellers which iswrong (and also that resellers could belong to multiple Distributorswhich is also wrong) So I shall remove the cross-reference tables forDistributors and Resellers.

However, I think I need to make the following minor changes to your suggestion:

Group Table
- Group ID (probably an identity column)
- other fields

Company Table
- Company ID (probably an identity column)
- Group ID (FK to Group table)
- other fields

This is so that a Group can have many Companies. (and a Company can only belong to one group)

(And also changes so Reseller Table contains the DistributorID and the Group table contains the ResellerID)

Could you just clear something up for me though please: Youappear to have placed the CompanyID in the Group table - would this notmean that a Group can only have one company? Is it not correct to putthe GroupID in the Company table. Then each Company can be linked to aparticular group?

What do you think..?

Again, thanks for your advice it is greatly appreciated,

Pete

|||

pete_m:
Could you just clear something up for me though please: You appear to have placed the CompanyID in the Group table - would this not mean that a Group can only have one company? Is it not correct to put the GroupID in the Company table. Then each Company can be linked to a particular group?

Yes, you're right, I was doing this too fast. I think you have the idea now.

JOINs problem

I work for a hospital network (three different hospitals involved), and
I need to generate a result set containing ever combination of
specialty and department applicable for each doctor. The base tables
are as follows...
CREATE TABLE Hospitals
(
HospitalID VARCHAR(10)
)
CREATE TABLE Specialties
(
DoctorID VARCHAR(10),
SpecialtyID VARCHAR(10),
)
CREATE TABLE Departments
(
DoctorID VARCHAR(10),
HospitalID, VARCHAR(10),
DepartmentID VARCHAR(10)
)
INSERT Hospitals VALUES ('HOSP1')
INSERT Hospitals VALUES ('HOSP2')
INSERT Hospitals VALUES ('HOSP3')
INSERT Specialties VALUES ('JONES1', 'CARDIO1')
INSERT Departments VALUES ('JONES1', 'HOSP1', 'CARVASSUR1')
INSERT Departments VALUES ('JONES1', 'HOSP3', 'VASSUR1')
Here's the kicker: specialties apply to all hospitals, while
departments are hospital-specific. Therefore, for Dr. Jones the
following should be produced...
DoctorID HospitalID Specialty DepartmentID
JONES1 HOSP1 CARDIO1 CARVASSUR1
JONES1 HOSP2 CARDIO1 null
JONES1 HOSP3 CARDIO1 VASSURG1
...but if no specialty record existed for Dr. Jones, only the two
relevant hospitals would be represented...
DoctorID HospitalID Specialty DepartmentID
JONES1 HOSP1 null CARVASSUR1
JONES1 HOSP3 null VASSURG1
What would be the simplest way to accomplish this result?how about:
SELECT *
FROM Hospitals as h
LEFT JOIN Departments as d on d.HospitalID = h.HospitalID
LEFT JOIN Specialties as s on s.DoctorID = d.DoctorID
WHERE NOT (s.DoctorID is null AND d.DoctorID is null)|||That would work *except* that where there is no specialty, I only want
a record for each hospital that has a related department record.|||so when you run my example, what output is it that's wrong, could you
give an example input, output, and desired output.|||Well, after discussing it with the client further, it appears that they
*would* like to see a record for each hospital, so your suggestion will
work for me after all. Thank you very much for your time.|||Glad it's all sorted, but I'm still pretty sure that the result will
actually show as you originally specified. You see if there's no
speciality, and no dept in the hospital, both these clauses will be
null, so it will be filtered by that where clause.
Anyway, as long as it's working, it's

- just check that example
before you use it.
Cheers
Will

Joins Position

Hi
Does changing Join sequence in any query effect performance?
I had a problem in a query which was performing very poorly but after I
changed the positions it is performing very well.
Any reason for this.
Lalit
Can you provide the whole SQL statement? (before and after)
Lalit wrote:
> Hi
> Does changing Join sequence in any query effect performance?
> I had a problem in a query which was performing very poorly but after
> I changed the positions it is performing very well.
> Any reason for this.
> Lalit
|||This is SQL Statement that performed fine.
I interchanged second and third JOINs. Both are Linked
with the first one.
SELECT DISTINCT GMTranSuper.GTS_VrNo [Name],
GMTranSuper.GTS_Id [Id]
FROM (SELECT GTS_Id, GTS_VrNo, GTS_Wt
FROM GMTranSuper
WHERE GTS_AllowMulRcpts = 0
AND ISNULL(GTS_RetdGTSId,
0) = 0) AS GMTranSuper
LEFT JOIN (SELECT GTIW_GTSId, SUM
(GTIWD_IssWt) AS IssWt,
SUM
(GTIWD_RetdWt) AS RetdWt
FROM GMTranIssWkr
INNER JOIN
GMTranIssWkrDet ON GMTranIssWkr.GTIW_Id =
GMTranIssWkrDet.GTIWD_GTIWId
--WHERE GTIW_GTSId
IS NOT NULL
GROUP BY
GTIW_GTSId) AS IssWkr
ON GMTranSuper.GTS_Id =
IssWkr.GTIW_GTSId
LEFT JOIN (SELECT GTS_RetdGTSId,
SUM(GTS_Wt) AS RetdWt
FROM GMTranSuper
--WHERE
GTS_RetdGTSId IS NOT NULL
GROUP BY
GTS_RetdGTSId) AS RetSuper
ON GMTranSuper.GTS_Id =
RetSuper.GTS_RetdGTSId
WHERE(GMTranSuper.GTS_Wt - (ISNULL
(RetSuper.RetdWt, 0) +
ISNULL(IssWkr.IssWt, 0) -
ISNULL(IssWkr.RetdWt, 0))) > 0
ORDER BY GMTranSuper.GTS_VrNo DESC
Following SQL Statement is that one that had the problem
SELECT DISTINCT GMTranSuper.GTS_VrNo [Name],
GMTranSuper.GTS_Id [Id]
FROM (SELECT GTS_Id, GTS_VrNo, GTS_Wt
FROM GMTranSuper
WHERE GTS_AllowMulRcpts = 0
AND ISNULL(GTS_RetdGTSId,
0) = 0) AS GMTranSuper
LEFT JOIN (SELECT GTS_RetdGTSId,
SUM(GTS_Wt) AS RetdWt
FROM GMTranSuper
--WHERE
GTS_RetdGTSId IS NOT NULL
GROUP BY
GTS_RetdGTSId) AS RetSuper
ON GMTranSuper.GTS_Id =
RetSuper.GTS_RetdGTSId
LEFT JOIN (SELECT GTIW_GTSId, SUM
(GTIWD_IssWt) AS IssWt,
SUM
(GTIWD_RetdWt) AS RetdWt
FROM GMTranIssWkr
INNER JOIN
GMTranIssWkrDet ON GMTranIssWkr.GTIW_Id =
GMTranIssWkrDet.GTIWD_GTIWId
--WHERE GTIW_GTSId
IS NOT NULL
GROUP BY
GTIW_GTSId) AS IssWkr
ON GMTranSuper.GTS_Id =
IssWkr.GTIW_GTSId
WHERE(GMTranSuper.GTS_Wt - (ISNULL
(RetSuper.RetdWt, 0) +
ISNULL(IssWkr.IssWt, 0) -
ISNULL(IssWkr.RetdWt, 0))) > 0
ORDER BY GMTranSuper.GTS_VrNo DESC
Lalit
|||Theoretically the answer is no but you seem to have found a situation
when it does. Post the query and let everyone see what has happened.
Lalit wrote:

> Hi
> Does changing Join sequence in any query effect performance?
> I had a problem in a query which was performing very poorly but after I
> changed the positions it is performing very well.
> Any reason for this.
> Lalit
>
>
sql

Joins Position

Joins Performance Problem

Hello,
It is taking too long to run the following query:
Note: I have indexes on all of the columns in conditions. My temp db size
is 18 GB. Pds_txn table size is 165 GB.
The execution plan showing:
Table pool/easer spool operation â'
Row Count: 9 M
Disk i/o: 11k
Row size: 1089
Estimated cost: 11 k (57%)
CPU cost: 3.3
Sub tree cost: 20 K
Any help/hint will be appreciated.
Thanks,
Alim
-----
FROM
dbo.pds_txn T1
INNER JOIN
dbo.GROUPS T2 ON
T1.GROUP_ID = T2.ID_200
INNER JOIN
dbo.DIVISIONS T3 ON
T1.DIVISION = T3.ID_102
INNER JOIN
dbo.BILLING_AREAS T4 ON
T1.BILLING_AREA = T4.ID_202
INNER JOIN
dbo.PROVIDERS T6 ON
T1.PROVIDER = T6.ID_3
INNER JOIN
dbo.LOCATIONS T7 ON
T1.LOCATION = T7.ID_100
INNER JOIN
dbo.PROCEDURES T8 ON
T1.[PROCEDURE] = T8.ID_1
INNER JOIN
dbo.FSC T9 ON
T1.ORIG_FSC = T9.ID_19
INNER JOIN
dbo.DIAGNOSIS T10 ON
T1.TXN_DX_1 = T10.ID_36
INNER JOIN
dbo.pds_invoice T11 ON
T1.INVOICE_NUM = T11.INVOICE_NUM AND
T1.GROUP_ID = T11.GROUP_ID
LEFT OUTER JOIN
dbo.PROVIDERS T6A ON
T11.PERFORMING_PHYS = T6A.ID_3
WHERE
T1.POSTING_PD_DTE >= '05/01/2003' AND
T1.PAY_CODE = 21 AND
T2.EXCLUSION_FLAG = 0 AND
T3.DIV_NUM <> '2901'Hi Alim,
It is hard to guess what the issue might be without more information. Could
you attach the output of "statistics profile" or "statistics xml" (if you
are using SQL Server 2005) ?
Regards,
Leo
"alim" <alim@.discussions.microsoft.com> wrote in message
news:3873F521-EEF2-4A62-9C66-00C035E1A9CD@.microsoft.com...
> Hello,
> It is taking too long to run the following query:
> Note: I have indexes on all of the columns in conditions. My temp db size
> is 18 GB. Pds_txn table size is 165 GB.
> The execution plan showing:
> Table pool/easer spool operation -
> Row Count: 9 M
> Disk i/o: 11k
> Row size: 1089
> Estimated cost: 11 k (57%)
> CPU cost: 3.3
> Sub tree cost: 20 K
> Any help/hint will be appreciated.
> Thanks,
> Alim
> -----
>
> FROM
> dbo.pds_txn T1
> INNER JOIN
> dbo.GROUPS T2 ON
> T1.GROUP_ID = T2.ID_200
> INNER JOIN
> dbo.DIVISIONS T3 ON
> T1.DIVISION = T3.ID_102
> INNER JOIN
> dbo.BILLING_AREAS T4 ON
> T1.BILLING_AREA = T4.ID_202
> INNER JOIN
> dbo.PROVIDERS T6 ON
> T1.PROVIDER = T6.ID_3
> INNER JOIN
> dbo.LOCATIONS T7 ON
> T1.LOCATION = T7.ID_100
> INNER JOIN
> dbo.PROCEDURES T8 ON
> T1.[PROCEDURE] = T8.ID_1
> INNER JOIN
> dbo.FSC T9 ON
> T1.ORIG_FSC = T9.ID_19
> INNER JOIN
> dbo.DIAGNOSIS T10 ON
> T1.TXN_DX_1 = T10.ID_36
> INNER JOIN
> dbo.pds_invoice T11 ON
> T1.INVOICE_NUM = T11.INVOICE_NUM AND
> T1.GROUP_ID = T11.GROUP_ID
> LEFT OUTER JOIN
> dbo.PROVIDERS T6A ON
> T11.PERFORMING_PHYS = T6A.ID_3
> WHERE
> T1.POSTING_PD_DTE >= '05/01/2003' AND
> T1.PAY_CODE = 21 AND
> T2.EXCLUSION_FLAG = 0 AND
> T3.DIV_NUM <> '2901'
>
>|||Alim,
Need to provide the table/index structure and the query that you are
trying to run..
Jayesh
"Leo Giakoumakis [MS]" <leogia_removethis_@.microsoft.com> wrote in message
news:e8OMTRyiGHA.3848@.TK2MSFTNGP04.phx.gbl...
> Hi Alim,
> It is hard to guess what the issue might be without more information.
> Could you attach the output of "statistics profile" or "statistics xml"
> (if you are using SQL Server 2005) ?
> Regards,
> Leo
>
> "alim" <alim@.discussions.microsoft.com> wrote in message
> news:3873F521-EEF2-4A62-9C66-00C035E1A9CD@.microsoft.com...
>> Hello,
>> It is taking too long to run the following query:
>> Note: I have indexes on all of the columns in conditions. My temp db
>> size
>> is 18 GB. Pds_txn table size is 165 GB.
>> The execution plan showing:
>> Table pool/easer spool operation -
>> Row Count: 9 M
>> Disk i/o: 11k
>> Row size: 1089
>> Estimated cost: 11 k (57%)
>> CPU cost: 3.3
>> Sub tree cost: 20 K
>> Any help/hint will be appreciated.
>> Thanks,
>> Alim
>> -----
>>
>> FROM
>> dbo.pds_txn T1
>> INNER JOIN
>> dbo.GROUPS T2 ON
>> T1.GROUP_ID = T2.ID_200
>> INNER JOIN
>> dbo.DIVISIONS T3 ON
>> T1.DIVISION = T3.ID_102
>> INNER JOIN
>> dbo.BILLING_AREAS T4 ON
>> T1.BILLING_AREA = T4.ID_202
>> INNER JOIN
>> dbo.PROVIDERS T6 ON
>> T1.PROVIDER = T6.ID_3
>> INNER JOIN
>> dbo.LOCATIONS T7 ON
>> T1.LOCATION = T7.ID_100
>> INNER JOIN
>> dbo.PROCEDURES T8 ON
>> T1.[PROCEDURE] = T8.ID_1
>> INNER JOIN
>> dbo.FSC T9 ON
>> T1.ORIG_FSC = T9.ID_19
>> INNER JOIN
>> dbo.DIAGNOSIS T10 ON
>> T1.TXN_DX_1 = T10.ID_36
>> INNER JOIN
>> dbo.pds_invoice T11 ON
>> T1.INVOICE_NUM = T11.INVOICE_NUM AND
>> T1.GROUP_ID = T11.GROUP_ID
>> LEFT OUTER JOIN
>> dbo.PROVIDERS T6A ON
>> T11.PERFORMING_PHYS = T6A.ID_3
>> WHERE
>> T1.POSTING_PD_DTE >= '05/01/2003' AND
>> T1.PAY_CODE = 21 AND
>> T2.EXCLUSION_FLAG = 0 AND
>> T3.DIV_NUM <> '2901'
>>
>

Joins Performance Problem

Hello,
It is taking too long to run the following query:
Note: I have indexes on all of the columns in conditions. My temp db size
is 18 GB. Pds_txn table size is 165 GB.
The execution plan showing:
Table pool/easer spool operation –
Row Count: 9 M
Disk i/o: 11k
Row size: 1089
Estimated cost: 11 k (57%)
CPU cost: 3.3
Sub tree cost: 20 K
Any help/hint will be appreciated.
Thanks,
Alim
----
--
FROM
dbo.pds_txn T1
INNER JOIN
dbo.GROUPS T2 ON
T1.GROUP_ID = T2.ID_200
INNER JOIN
dbo.DIVISIONS T3 ON
T1.DIVISION = T3.ID_102
INNER JOIN
dbo.BILLING_AREAS T4 ON
T1.BILLING_AREA = T4.ID_202
INNER JOIN
dbo.PROVIDERS T6 ON
T1.PROVIDER = T6.ID_3
INNER JOIN
dbo.LOCATIONS T7 ON
T1.LOCATION = T7.ID_100
INNER JOIN
dbo.PROCEDURES T8 ON
T1.[PROCEDURE] = T8.ID_1
INNER JOIN
dbo.FSC T9 ON
T1.ORIG_FSC = T9.ID_19
INNER JOIN
dbo.DIAGNOSIS T10 ON
T1.TXN_DX_1 = T10.ID_36
INNER JOIN
dbo.pds_invoice T11 ON
T1.INVOICE_NUM = T11.INVOICE_NUM AND
T1.GROUP_ID = T11.GROUP_ID
LEFT OUTER JOIN
dbo.PROVIDERS T6A ON
T11.PERFORMING_PHYS = T6A.ID_3
WHERE
T1.POSTING_PD_DTE >= '05/01/2003' AND
T1.PAY_CODE = 21 AND
T2.EXCLUSION_FLAG = 0 AND
T3.DIV_NUM <> '2901'Hi Alim,
It is hard to guess what the issue might be without more information. Could
you attach the output of "statistics profile" or "statistics xml" (if you
are using SQL Server 2005) ?
Regards,
Leo
"alim" <alim@.discussions.microsoft.com> wrote in message
news:3873F521-EEF2-4A62-9C66-00C035E1A9CD@.microsoft.com...
> Hello,
> It is taking too long to run the following query:
> Note: I have indexes on all of the columns in conditions. My temp db size
> is 18 GB. Pds_txn table size is 165 GB.
> The execution plan showing:
> Table pool/easer spool operation -
> Row Count: 9 M
> Disk i/o: 11k
> Row size: 1089
> Estimated cost: 11 k (57%)
> CPU cost: 3.3
> Sub tree cost: 20 K
> Any help/hint will be appreciated.
> Thanks,
> Alim
> ----
--
>
> FROM
> dbo.pds_txn T1
> INNER JOIN
> dbo.GROUPS T2 ON
> T1.GROUP_ID = T2.ID_200
> INNER JOIN
> dbo.DIVISIONS T3 ON
> T1.DIVISION = T3.ID_102
> INNER JOIN
> dbo.BILLING_AREAS T4 ON
> T1.BILLING_AREA = T4.ID_202
> INNER JOIN
> dbo.PROVIDERS T6 ON
> T1.PROVIDER = T6.ID_3
> INNER JOIN
> dbo.LOCATIONS T7 ON
> T1.LOCATION = T7.ID_100
> INNER JOIN
> dbo.PROCEDURES T8 ON
> T1.[PROCEDURE] = T8.ID_1
> INNER JOIN
> dbo.FSC T9 ON
> T1.ORIG_FSC = T9.ID_19
> INNER JOIN
> dbo.DIAGNOSIS T10 ON
> T1.TXN_DX_1 = T10.ID_36
> INNER JOIN
> dbo.pds_invoice T11 ON
> T1.INVOICE_NUM = T11.INVOICE_NUM AND
> T1.GROUP_ID = T11.GROUP_ID
> LEFT OUTER JOIN
> dbo.PROVIDERS T6A ON
> T11.PERFORMING_PHYS = T6A.ID_3
> WHERE
> T1.POSTING_PD_DTE >= '05/01/2003' AND
> T1.PAY_CODE = 21 AND
> T2.EXCLUSION_FLAG = 0 AND
> T3.DIV_NUM <> '2901'
>
>|||Alim,
Need to provide the table/index structure and the query that you are
trying to run..
Jayesh
"Leo Giakoumakis [MS]" <leogia_removethis_@.microsoft.com> wrote in messa
ge
news:e8OMTRyiGHA.3848@.TK2MSFTNGP04.phx.gbl...
> Hi Alim,
> It is hard to guess what the issue might be without more information.
> Could you attach the output of "statistics profile" or "statistics xml"
> (if you are using SQL Server 2005) ?
> Regards,
> Leo
>
> "alim" <alim@.discussions.microsoft.com> wrote in message
> news:3873F521-EEF2-4A62-9C66-00C035E1A9CD@.microsoft.com...
>

Joins on UPDATE

I need some help understanding what's happening on a join when updating
tables. Assume 2 tables with identical structures:

UPDATE
Table1
SET
Field1 = Table2.Field1,
Field2 = Table2.Field2
FROM
Table2
WHERE
Table1.Field3 = Table2.Field3
AND Table1.Field4 = Table2.Field4

Indexes exist on Field3 and Field4 on both tables. So why does SQL
Server choose a hash join?

Thanks in advance.(andrewbb@.gmail.com) writes:
> I need some help understanding what's happening on a join when updating
> tables. Assume 2 tables with identical structures:
> UPDATE
> Table1
> SET
> Field1 = Table2.Field1,
> Field2 = Table2.Field2
> FROM
> Table2
> WHERE
> Table1.Field3 = Table2.Field3
> AND Table1.Field4 = Table2.Field4
>
> Indexes exist on Field3 and Field4 on both tables. So why does SQL
> Server choose a hash join?

Are those indexes on (Field3, Field4) or indexes on the individual
fields?

In any case, one of the tables will have to be scanned. Say that would
be Table2. Now for each row, we should look for a matching row in Table1.
Now, assume that only a few rows match. In this case, using a nested loop
and look up the row in Table1 is a good idea.

But what if all rows match? In this case, the pages in Table1 would be
accessed many times, and that would be expensive. Better then to scan
Table1 once. If there is a clustered index on (Field3, Field4), SQL
Server should be able to do a merge join, and scan both tables in
parallel. But if the index is non-clustered, then it's not of much
use, so instead SQL Server builds the hash table.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Fields 3 and 4 are individual indexes (not clustered) and the unique
key for the record.

There is exactly a one to one relationship between the two tables, so
how should I structure this to update quickly?|||(andrewbb@.gmail.com) writes:
> Fields 3 and 4 are individual indexes (not clustered) and the unique
> key for the record.
> There is exactly a one to one relationship between the two tables, so
> how should I structure this to update quickly?

You should have a clustered index on (field3, field4).

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

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

Joins on uniqueidentifier columns

I have recently introduced Microsoft'suser/roles facility by running aspnet_regsql.exe against my database so that Iwould be able to link activities with staff members.

In testing I was able to

Join 2 tables (Reservations and aspnet_Users) with the join fields being of type uniqueidentifier, using SQL Server Manager Studio Express diagram facility;
Create a record in aspnet_Users using Microsoft's Website Admin tool
Create a record in Reservations and paste in the contents of uniqueidentifier field in aspnet_Users using SQL Server Manager Studio Express

All the basic tests had been fine so I created a web pagewith a detailsview of the Reservations table and made a templated field (in theinsertitemtemplate), replaced the text box with a dropdown and did a bind ofthat dropdown to the relevant records in the aspnet_Users table i.e. UserNameand UserId columns with UserName being displayed. The source is as follows:

<asp:TemplateFieldHeaderText="Taken By"SortExpression="RES_Taken_By_Staff_ID">

<asp:TextBoxID="TextBox5"runat="server"Text='<%#Bind("RES_Taken_By_Staff_ID")%>'></asp:TextBox>

</EditItemTemplate>

<asp:DropDownListID="DropDownList5"runat="server"DataSourceID="SDSStaff"DataTextField="UserName"

DataValueField="UserId">

</asp:DropDownList><br/>

<asp:SqlDataSourceID="SDSStaff"runat="server"ConnectionString="<%$ ConnectionStrings:ReservationsDBConnectionString%>"

SelectCommand="SELECT[UserId], [UserName] FROM [vw_aspnet_Users]"></asp:SqlDataSource>

</InsertItemTemplate>

<asp:LabelID="Label5"runat="server"Text='<%#Bind("RES_Taken_By_Staff_ID")%>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

Everything looks OK, the correct data appears in the dropdownbut when I hit the INSERT button I receive the following failure message:

Implicit conversion from data type sql_variant to uniqueidentifier isnot allowed. Use the CONVERT function to run this query.

Description:An unhandled exceptionoccurred during the execution of the current web request. Please review thestack trace for more information about the error and where it originated in thecode.

Exception Details:System.Data.SqlClient.SqlException: Implicitconversion from data type sql_variant to uniqueidentifier is not allowed. Usethe CONVERT function to run this query.

I can't quite see what I should do so I would begrateful for any help.

I hopeInserting with a SqlDataSource Using uniqueidentifier Parameters can help you.

|||

Hi,

Thank you for this. The references did not cover Template Fields where SqlDataSource does not have a type attribute so I'm still stymied!

|||

Me,too. There is clearly something strange about inserting a uniqueidentifier that is not your primary key column into SQL from ASP.net (like a foreign key).

I followed the same advice quoted in the article above. It worked for my select & update statements, but not for my insert statements.

I cannot insert a foreign key into a table from ASP.net at all. Please help if you found a fix or workaround for this problem.

|||

Hi,

Yes, very odd! I've reported this as a bug so maybe confirmation/correction may be available from MS

|||

My workaround for now is to convert the uniqueidentifier foreign key to an integer.

I managed to get it working, but it's not ideal

- Roger

|||

Please try delete all TemplateField filds in grid.

Bag in grid:

I haved

<asp:GridView ID="GridViewMain" runat="server" AllowPaging="True" AllowSorting="True"
AutoGenerateColumns="False" DataKeyNames="FAQID" DataSourceID="SqlDataSourceMain"
EmptyDataText="There are no data records to display.">
<Columns>
<asp:BoundField DataField="LotName" HeaderText="LotName" SortExpression="LotName" />
<asp:TemplateField HeaderText="From User" SortExpression="fromUser">
<ItemTemplate>
<asp:HyperLink ID="LinkFromUser" runat="server" Text='<%# Bind("[fromUser]") %>'
NavigateUrl='<%# "~/UserManagment/UserView.aspx?Filter=" + Eval("[From]") %>'>
</asp:HyperLink>
</ItemTemplate>
<EditItemTemplate>
</EditItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>

Warning !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
<EditItemTemplate>
</EditItemTemplate>

without EditItemTemplate i have error

Implicit conversion from data type sql_variant to uniqueidentifier isnot allowed. Use the CONVERT function to run this query.

sql

Joins on same table

I'm having two general problems trying to do a JOIN. I have a table with
three fields {Code, Date, Amount}. Code+Date is a unique key. I'm trying
to get a rowset with 1) one row for each unique Code+Date pair, 2) and
with each row containing, {Code, Amount for Date-A and Amount for
Date-B}. Basically, I want to create two temp tables with the Amounts for
a specified Date and then Join them.

The problems are
1) I'm trying to do this in SQL-Server 7 with a single stantment, and
2) If a Code+Date pair doesn't have any Amounts, I'd still like a row
returned with NULLs.

Anybody have any wisdom on this??
ThanksThe following gets me what I want, using Temp tables. I'm just trying to
figure out how to combine the Selects into a single statment.

Thanks

----------------

--temp with each Code
Drop Table #T0;
Select Code
Into #T0
From tblSearch
Order by Code;

--temp with amounts for 1st date
Drop Table #T1;
Select Code, Date, Amount
Into #T1
From tblSearch
Where Date = 20031102
Order by Code, Date;

-- amounts for 2nd date
Drop Table #T2;
Select Code, Date, Amount
Into #T2
From tblSearch
Where Date = 20031103
Order by Code, Date;

--put everything together
Select Distinct #T0.Code, #T1.Date, #T1.Amount 'd1', #T2.Date, #T2.Amount
'd2' from #T0
Left Outer Join #T1
On #T0.Code = #T1.Code
Left Outer Join #T2
On #T0.Code = #T2.Code
Order By #T0.Code|||[posted and mailed, please reply in news]

Chris (chris@.hicom.net) writes:
> I'm having two general problems trying to do a JOIN. I have a table
> with three fields {Code, Date, Amount}. Code+Date is a unique key.
> I'm trying to get a rowset with 1) one row for each unique Code+Date
> pair, 2) and with each row containing, {Code, Amount for Date-A and
> Amount for Date-B}. Basically, I want to create two temp tables with
> the Amounts for a specified Date and then Join them.
> The problems are
> 1) I'm trying to do this in SQL-Server 7 with a single stantment, and
> 2) If a Code+Date pair doesn't have any Amounts, I'd still like a row
> returned with NULLs.

Just rewriting the temp-table thing you had with derived tables
gives:

SELECT DISTINCT #T0.Code, #T1.Date, #T1.Amount 'd1',
#T2.Date, #T2.Amount 'd2'
FROM tblSearch #T0
LEFT JOIN (SELECT Code, Date, Amount
FROM tblSearch
WHERE Date = '20031102') AS #T1
ON #T0.Code = #T1.Code
LEFT JOIN (SELECT Code, Date, Amount
FROM tblSearch
WHERE Date = '20031103') AS #T2
ON #T0.Code = #T2.Code
ORDER BY #T0.Code

But if I understand this correctly, it seems that you could get away with:

SELECT Code = coalesce(a.Code, b.Code), a.Date, d1 = a.Amount,
b.Date, d2 = b.Amount
FROM tblSearch a
FULL JOIN tblSearch b ON a.Code = b.Code
AND a.Date = b.Date
AND a.Date = '20031102'
AND b.Date = '20031103'

All this works on SQL7.

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

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

You can replace temp tables with derived tables. Alternatively, as
the temp tables are selecting from the same table tblSearch, you can
also re-write the query using CASE. Note I'm using a mssqlserver
non-standard syntax. I just find it easier to read.

"Date=CASE when Date = 20031102 then Date else null end"
instead of
"CASE when Date = 20031102 then Date else null end as Date"

SELECT
Distinct
Code,
Date=CASE when Date = 20031102 then Date else null end,
Amount=CASE when Date = 20031102 then Amount else null end,
Date=CASE when Date = 20031103 then Date else null end,
Amount=CASE when Date = 20031103 then Amount else null end,
FROM tblSearch
ORDER BY code

> --temp with each Code
> Drop Table #T0;
> Select Code
> Into #T0
> From tblSearch
> Order by Code;
> --temp with amounts for 1st date
> Drop Table #T1;
> Select Code, Date, Amount
> Into #T1
> From tblSearch
> Where Date = 20031102
> Order by Code, Date;
> -- amounts for 2nd date
> Drop Table #T2;
> Select Code, Date, Amount
> Into #T2
> From tblSearch
> Where Date = 20031103
> Order by Code, Date;
> --put everything together
> Select Distinct #T0.Code, #T1.Date, #T1.Amount 'd1', #T2.Date, #T2.Amount
> 'd2' from #T0
> Left Outer Join #T1
> On #T0.Code = #T1.Code
> Left Outer Join #T2
> On #T0.Code = #T2.Code
> Order By #T0.Code|||The derived table approach gets me what I want -- one row per Code.

It seems that Coalesce doesn't help reduce the normal number of rows from
the Join.

Thanks very much for looking for a solution.|||The derived tables gets both Amounts into the same row, while the Case
still results in two (Distinct) rows.

I need to get a better SQL reference -- the book I'm using does not cover
derived tables.

Thanks very much.|||"Chris" <chris@.hicom.net> wrote in message news:<2bcNb.32425$G04.6661104@.news4.srv.hcvlny.cv.net>...
> The derived tables gets both Amounts into the same row, while the Case
> still results in two (Distinct) rows.
> I need to get a better SQL reference -- the book I'm using does not cover
> derived tables.
> Thanks very much.

Use GROUP if you want combine them into the same row. DISTINCT only
filters the rows.

SELECT
Code,
Date=max(CASE when Date = 20031102 then Date else null end),
Amount=max(CASE when Date = 20031102 then Amount else null end),
Date=max(CASE when Date = 20031103 then Date else null end),
Amount=max(CASE when Date = 20031103 then Amount else null end)
FROM tblSearch
GROUP BY code
ORDER BY code|||Chris (chris@.hicom.net) writes:
> It seems that Coalesce doesn't help reduce the normal number of rows
> from the Join.

That's right. The coalesce() function takes a list of values as parameters,
and return the first value in the list that is not NULL. Since the second
query included a full join, any of a.code and b.code could be NULL, so be
sure that we had a value here, I used coalesce(a.Code, b.Code).

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

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

-- Thanks|||louis nguyen (louisducnguyen@.hotmail.com) writes:
> SELECT
> Code,
> Date=max(CASE when Date = 20031102 then Date else null end),
> Amount=max(CASE when Date = 20031102 then Amount else null end),
> Date=max(CASE when Date = 20031103 then Date else null end),
> Amount=max(CASE when Date = 20031103 then Amount else null end)
> FROM tblSearch
> GROUP BY code
> ORDER BY code

Note that date literals requires quotes. 20031103 is a number, and
attempt to convert it to datetime results in overflow.

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

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

Joins on 3 tables

Hi All,

I require to perfom a join on 3 tables within the same query . To explain myself better i have 3 tables

Main table

Label table

textbox table

The Main table contains the common fields in both the label and textbox table. While the label and textox table contain the fields that are sepcfic to them .

MAIN Table

pk Moduleid ItemName itemtype

36 372 test1 4 37 372 test2 4 38 372 test3 4 39 372 test4 6 40 372 test5 4

label

pk Main_fk labeltext

4 36 labeltext1 5 37 labeltext2 6 38 labeltext3 7 40 labeltext4

Textbox

pk Main_fk textboxtext

1 39 textbox1

I did infact manage to perform a join on these these tables.

Select * From tb_Main

inner join tb_Label

on tb_Main.pk = tb_Label.main_fk

where moduleID = @.moduleID

Select * From tb_Main

inner join tb_textbox

on tb_Main.pk = tb_textbox.main_fk

where moduleID = @.moduleID

The problem is that it returns two separate results . I require a join on the label and textbox table within the same query to return one result.

Is what im asking possible? I would appreciate if some exmaples are posted

I have no control on the design of the tables as i didnt create them but still if anyone has a suggestion on improving them please do ,so i can tell my colleague that they aren't designed well !!!!

Thanks in advance

Matt

Hai,

You can try this query, hope this will work.

DECLARE @.ModuleID int

SET @.ModuleID = 372

SELECT

*

FROM tb_Main AS M

JOIN tb_Label AS L

ON M.pk = L.main_fK

JOIN tb_textbox AS T

ON M.pk = T.main_fk

WHERE ModuleID = @.ModuleID

Regards,

Kiran.Y

|||

Thanks Y.Kiran for your suggestion.

I had already tried that out, it returns no rows.

Regards,

Matt

|||

Hai,

I didn't change any thing in the given query. Now I'm giving you the table definitions of all tables, Main, Label, TextBox. Check this one.

-- Create Main, Label, TextBox Tables.

CREATE TABLE tb_Main

( PK int,

Moduleid int,

ItemName varchar(50),

itemtype int,

CONSTRAINT PK_tb_Main_PK PRIMARY KEY CLUSTERED

(

PK ASC

)

CREATE TABLE tb_Label

(

PK int,

Main_fk int CONSTRAINT FK_tb_Main_Main_fk REFERENCES tb_Main(PK),

labeltext varchar(50),

CONSTRAINT PK_tb_Label_PK PRIMARY KEY CLUSTERED

(

PK ASC

)

CREATE TABLE tb_TextBox

(

PK int,

Main_fk int CONSTRAINT FK_tb_TextBox_Main_fk REFERENCES tb_Main(pk),

TextBoxText varchar(50),

CONSTRAINT PK_tb_TextBox_PK PRIMARY KEY CLUSTERED

(

PK ASC

)

-- Insert data into Main, Label, TextBox tables.

INSERT INTO tb_Main(pk , Moduleid , ItemName, itemtype) VALUES(36,372,'test1',4)

INSERT INTO tb_Main(pk , Moduleid , ItemName, itemtype) VALUES(37,372,'test2',4)

INSERT INTO tb_Main(pk , Moduleid , ItemName, itemtype) VALUES(38,372,'test3',4)

INSERT INTO tb_Main(pk , Moduleid , ItemName, itemtype) VALUES(39,372,'test4',6)

INSERT INTO tb_Main(pk , Moduleid , ItemName, itemtype) VALUES(40,372,'test5',4)

INSERT INTO tb_Label(pk,Main_fk, labeltext) VALUES(4,36,'labeltext1')

INSERT INTO tb_Label(pk,Main_fk, labeltext) VALUES(5,37,'labeltext2')

INSERT INTO tb_Label(pk,Main_fk, labeltext) VALUES(6,38,'labeltext3')

INSERT INTO tb_Label(pk,Main_fk, labeltext) VALUES(7,40,'labeltext4')

INSERT INTO tb_Label(pk,Main_fk, labeltext) VALUES(8,39,'labeltext5')

INSERT INTO tb_textbox(pk, Main_fk, textboxtext) VALUES(1,39,'textbox1')

-- Get the records based on the @.ModuleID variable.

DECLARE @.ModuleID int

SET @.ModuleID = 372

SELECT

FROM tb_Main AS M

JOIN tb_Label AS L

ON M.pk = L.main_fK

JOIN tb_textbox AS T

ON M.pk = T.main_fk

WHERE ModuleID = @.moduleID

Let me know, If I did any wrong.

Regards,

Kiran.Y

|||

You should be able to run the following statement to get what you need.

SELECT
tb_Main.pk, tb_ModuleID, tb_ItemName, tb_ItemType,
tb_Label.LabelText, tb_Textbox.Textboxtext
FROM
tb_Main
INNER_JOIN tb_Label ON tb_Main.pk = tb_Label.FK
INNER_JOIN tb_Textbox ON tb_Main.pk = tb_Textbox
WHERE tb_Main.ModuleID = @.ModuleID

The problem that I see is in your data. If this is a true representation of you data then an INNER JOIN is not going to return anything because when you join to the third table the only match you will find is 39, which is not in the second table, hence you get no results. If you were to do a LEFT OUTER JOIN, you could get all of the results with NULL values also represented. Try the query below to attempt to get results.

SELECT
tb_Main.pk, tb_ModuleID, tb_ItemName, tb_ItemType,
tb_Label.LabelText, tb_Textbox.Textboxtext
FROM
tb_Main
LEFT OUTER_JOIN tb_Label ON tb_Main.pk = tb_Label.FK
LEFT OUTER_JOiN tb_Textbox ON tb_Main.pk = tb_Textbox
WHERE tb_Main.ModuleID = @.ModuleID

Results:

pk ModuleID ItemName ItemType LabelText TextBox
-- -- -- -- -
36 372 test1 4 labeltext1 NULL
37 372 test2 4 labeltext2 NULL
38 372 test3 4 labeltext3 NULL
39 372 test4 6 NULL textbox1
40 372 test5 4 labeltext4 NULL

Hope this helps. Anyone feel free to correct if there are any inaccuracies. I'm relatively new to SQL Server.

|||

No Data was returned from the query that Kumar provided, BECAUSE there is NO common data between all three tables.

MAIN Table

pk Moduleid ItemName itemtype

36 372 test1 4 37 372 test2 4 38 372 test3 4 39 372 test4 6 40 372 test5 4

label

pk Main_fk labeltext

4 36 labeltext1 5 37 labeltext2 6 38 labeltext3 7 40 labeltext4

Textbox

pk Main_fk textboxtext

1 39 textbox1

There is NO [Mail_fk] for 39 in the table [Label], therefore no matching link between [Textbox], [Label] and [Main]

You 'could' use LEFT JOIN in both of the joins to have a resultset that includes ALL rows from [MAIN] even if there is NO matching links in the other tables.

|||

DBaker,

Excellent explanation and corrected query!

|||

Thanks guys for your help it worked great !

Matt

Joins issue

Sorry if this is the wrong group but..
I have a query that still has a few minor issues the main problem i had with
nulls is sorted however i am joining 5 tables together and if a row doesnt
exist in a table i dont get a row at all, i have a table that i know a
record always exists in and i am using left outer joins to join it to other
tables. I thought that a left join would get a record regardless of whether
or not there is a matching record. My query is posted below so you can
maybe let me know whats wrong with it, i am sorry for the lack of aliases
and probably readibility but i havent really had time to sort it.
SELECT dbo.DM_LoanDetails.FK_ApplicationID,
dbo.DM_Mortgage.MortgageBalance, dbo.DM_Mortgage.Redemption,
dbo.DM_OtherCredit.BALANCESEC +
dbo.DM_OtherCredit.redemtionsecured AS Secured_Borrowing,
dbo.DM_OtherCredit.Balance,
dbo.DM_LoanDetails.EXTRAFUNDS,
dbo.DM_LoanDetails.RulesArrangementfee, dbo.DM_LoanDetails.RulesLegals,
dbo.DM_Payout.BrokerAdminFee,
dbo.DM_Payout.ASUFee, dbo.DM_Valuation.Cost,
dbo.DM_OtherCredit.ToClear, dbo.DM_OtherCredit.FieldIdent,
dbo.DM_Payout.ProcFee
FROM dbo.DM_LoanDetails LEFT OUTER JOIN
dbo.DM_Valuation ON
dbo.DM_LoanDetails.FK_ApplicationID = dbo.DM_Valuation.FK_ApplicationID LEFT
OUTER JOIN
dbo.DM_Payout ON dbo.DM_LoanDetails.FK_ApplicationID =
dbo.DM_Payout.FK_ApplicationID LEFT OUTER JOIN
dbo.DM_Mortgage ON dbo.DM_LoanDetails.FK_ApplicationID
= dbo.DM_Mortgage.FK_ApplicationID LEFT OUTER JOIN
dbo.DM_OtherCredit ON
dbo.DM_LoanDetails.FK_ApplicationID = dbo.DM_OtherCredit.FK_ApplicationID
WHERE (dbo.DM_Mortgage.FieldIdent = '1:1') AND
(dbo.DM_LoanDetails.FieldIdent = '1:1') AND (dbo.DM_Valuation.FieldIdent =
'1:1') AND
(dbo.DM_Payout.FieldIdent = '1:1') AND
(dbo.DM_OtherCredit.FieldIdent = '1:1' OR
dbo.DM_OtherCredit.FieldIdent = '1:2' OR
dbo.DM_OtherCredit.FieldIdent = '1:3' OR
dbo.DM_OtherCredit.FieldIdent = '1:4' OR
dbo.DM_OtherCredit.FieldIdent = '1:5' OR
dbo.DM_OtherCredit.FieldIdent = '1:6' OR
dbo.DM_OtherCredit.FieldIdent = '1:7' OR
dbo.DM_OtherCredit.FieldIdent = '1:8' OR
dbo.DM_OtherCredit.FieldIdent = '1:9' OR
dbo.DM_OtherCredit.FieldIdent = '1:10')
Thanks in advanceA LEFT OUTER JOIN will always return rows, provided that your WHERE
criteria doesn't limit the results of your query using a column from
the inner side of the join. In your case, the criteria :
(dbo.DM_Mortgage.FieldIdent = '1:1')
tells SQL Server to limit the results to include data from both
DM_LoanDetails (all rows) and DM_Mortgage (only those rows where
FieldIDent = '1:1'). Basically, you've nullified your OUTER JOIN.
HTH,
Stu|||So I guess that your always existing row is stored in the
DM_LoanDetails table, right ? (You didn=B4t mentioned that). If so the
query is right. Try to eliminate the conditions at the end step by step
to see if these are chopping your result in any way.
HTH, jens Suessmeyer.|||Yeah the query is right the 1:1 condition needs to be there otherwise it
returns other iterations of the record and you end up with dupes i didnt
design the database its software that was ourchased a few years before i
started here, its hard to explain why the iterations are there and why they
work, I do need that clause in there though i have tried it without and
still get the same results.
I can better explain my problem now i think. The sql i have given is used
in another view that performs some calculations and basically if the value
is null makes it zero, the problem lies in the dm_payout and dm_valuation
tables, basically the case has died before anyone has been able to complete
the fields i need from those tables.
However i need to show what the value of the deal was regardless of whether
or not we got to add our fees on top, so if they wanted 100k but no other
fields were completed then it should show 100k
As i have mentioned this calculation is done in another view, the problem
lies in the fact that no record exists in the payout or valuation table so
it is for some unknown reason causing it not to get any results at all.
This other view (main view) is as follows
We have a table of phone numbers of people who have called in on a certain
number that we got from our dialler database this is joined to a table in
the database that has the phone number so that we can get the
fk_applicationID, this is present in all the tables as it is the unique
identifier. We then join this table to another table to get the persons
surname and 2 views, one of the views tells us what the applications status
is of the record, the 2nd view accesses the information in the view which is
the SQL i posted. Basically this view only pulls the required information
that is needed from the view i posted and if the value is null sets it to
zero. I then in this (main view) add the fields together i need.
I get all the records i would expect but i get null where the value of the
calculation should be because in the view i posted no row is returned.
I hope this is making sense. Maybe i wont be able to have a value here and
null is all i can expect but as i said a left join should as far as i know
just give me the rest of the information which then shouldnt mess up my
calc.
thanks for the help so far
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1132142679.094129.141370@.g14g2000cwa.googlegroups.com...
So I guess that your always existing row is stored in the
DM_LoanDetails table, right ? (You didnt mentioned that). If so the
query is right. Try to eliminate the conditions at the end step by step
to see if these are chopping your result in any way.
HTH, jens Suessmeyer.|||On Wed, 16 Nov 2005 10:46:25 -0000, Steven Scaife wrote:

>Sorry if this is the wrong group but..
>I have a query that still has a few minor issues the main problem i had wit
h
>nulls is sorted however i am joining 5 tables together and if a row doesnt
>exist in a table i dont get a row at all, i have a table that i know a
>record always exists in and i am using left outer joins to join it to other
>tables. I thought that a left join would get a record regardless of whethe
r
>or not there is a matching record. My query is posted below so you can
>maybe let me know whats wrong with it, i am sorry for the lack of aliases
>and probably readibility but i havent really had time to sort it.
Hi Stevan,
A quick visit to http://www.sqlinform.com/ was all it took to get the
SQL a whole lot more readable. Here's a better formatted version of your
query:
SELECT
dbo.DM_LoanDetails.FK_ApplicationID,
dbo.DM_Mortgage.MortgageBalance,
dbo.DM_Mortgage.Redemption,
dbo.DM_OtherCredit.BALANCESEC + dbo.DM_OtherCredit.redemtionsecured
AS Secured_Borrowing,
dbo.DM_OtherCredit.Balance,
dbo.DM_LoanDetails.EXTRAFUNDS,
dbo.DM_LoanDetails.RulesArrangementfee,
dbo.DM_LoanDetails.RulesLegals,
dbo.DM_Payout.BrokerAdminFee,
dbo.DM_Payout.ASUFee,
dbo.DM_Valuation.Cost,
dbo.DM_OtherCredit.ToClear,
dbo.DM_OtherCredit.FieldIdent,
dbo.DM_Payout.ProcFee
FROM dbo.DM_LoanDetails
LEFT OUTER JOIN
dbo.DM_Valuation
ON dbo.DM_LoanDetails.FK_ApplicationID =
dbo.DM_Valuation.FK_ApplicationID
LEFT OUTER JOIN
dbo.DM_Payout
ON dbo.DM_LoanDetails.FK_ApplicationID =
dbo.DM_Payout.FK_ApplicationID
LEFT OUTER JOIN
dbo.DM_Mortgage
ON dbo.DM_LoanDetails.FK_ApplicationID =
dbo.DM_Mortgage.FK_ApplicationID
LEFT OUTER JOIN
dbo.DM_OtherCredit
ON dbo.DM_LoanDetails.FK_ApplicationID =
dbo.DM_OtherCredit.FK_ApplicationID
WHERE (dbo.DM_Mortgage.FieldIdent = '1:1')
AND (dbo.DM_LoanDetails.FieldIdent = '1:1')
AND (dbo.DM_Valuation.FieldIdent = '1:1')
AND (dbo.DM_Payout.FieldIdent = '1:1')
AND (dbo.DM_OtherCredit.FieldIdent = '1:1'
OR dbo.DM_OtherCredit.FieldIdent = '1:2'
OR dbo.DM_OtherCredit.FieldIdent = '1:3'
OR dbo.DM_OtherCredit.FieldIdent = '1:4'
OR dbo.DM_OtherCredit.FieldIdent = '1:5'
OR dbo.DM_OtherCredit.FieldIdent = '1:6'
OR dbo.DM_OtherCredit.FieldIdent = '1:7'
OR dbo.DM_OtherCredit.FieldIdent = '1:8'
OR dbo.DM_OtherCredit.FieldIdent = '1:9'
OR dbo.DM_OtherCredit.FieldIdent = '1:10')
Now, it is immediately clear that the reason for your query not working,
is that you build WHERE clauses on columns from all outer-join'ed
tables. Stu already explained why that is bad - but it seems that he
only catched one of the culprits.
If you really need these joins to be outer joins, then you'll have to
move all selections from the WHERE clause to the ON clauses:
SELECT
dbo.DM_LoanDetails.FK_ApplicationID,
dbo.DM_Mortgage.MortgageBalance,
dbo.DM_Mortgage.Redemption,
dbo.DM_OtherCredit.BALANCESEC + dbo.DM_OtherCredit.redemtionsecured
AS Secured_Borrowing,
dbo.DM_OtherCredit.Balance,
dbo.DM_LoanDetails.EXTRAFUNDS,
dbo.DM_LoanDetails.RulesArrangementfee,
dbo.DM_LoanDetails.RulesLegals,
dbo.DM_Payout.BrokerAdminFee,
dbo.DM_Payout.ASUFee,
dbo.DM_Valuation.Cost,
dbo.DM_OtherCredit.ToClear,
dbo.DM_OtherCredit.FieldIdent,
dbo.DM_Payout.ProcFee
FROM dbo.DM_LoanDetails
LEFT OUTER JOIN
dbo.DM_Valuation
ON dbo.DM_LoanDetails.FK_ApplicationID =
dbo.DM_Valuation.FK_ApplicationID
AND dbo.DM_Valuation.FieldIdent = '1:1'
LEFT OUTER JOIN
dbo.DM_Payout
ON dbo.DM_LoanDetails.FK_ApplicationID =
dbo.DM_Payout.FK_ApplicationID
AND dbo.DM_Payout.FieldIdent = '1:1'
LEFT OUTER JOIN
dbo.DM_Mortgage
ON dbo.DM_LoanDetails.FK_ApplicationID =
dbo.DM_Mortgage.FK_ApplicationID
AND dbo.DM_Mortgage.FieldIdent = '1:1'
LEFT OUTER JOIN
dbo.DM_OtherCredit
ON dbo.DM_LoanDetails.FK_ApplicationID =
dbo.DM_OtherCredit.FK_ApplicationID
AND dbo.DM_OtherCredit.FieldIdent IN ('1:1', '1:2', '1:3', '1:4',
'1:5', '1:6', '1:7', '1:8', '1:9', '1:10')
WHERE dbo.DM_LoanDetails.FieldIdent = '1:1'
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Monday, March 26, 2012

Joins??

Joins with XQuery

Joins with XQuery

Joins Vs Where clause - Performance Query

Joins vs SubQueries

Joins versus relationships

JOINS to Sub-Queries -vs- JOINS to Tables

Joins Question

Joins query help

JOINs problem

Joins Position

Joins Position

Joins Performance Problem

Joins Performance Problem

Joins on UPDATE

Joins on uniqueidentifier columns

Implicit conversion from data type sql_variant to uniqueidentifier isnot allowed. Use the CONVERT function to run this query.

Implicit conversion from data type sql_variant to uniqueidentifier isnot allowed. Use the CONVERT function to run this query.

Joins on same table

Joins on 3 tables

Joins issue

join or subselect

Blog Archive

About Me