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)