Problem with left join, please help !

I am doing a left join in this query, but only the records that have a join
are displayed.
There is a record in "cases" with no "casecomments" but it is not displayed
.. please help:
SELECT *
FROM cases a
left join casecomments as b on a.id = b.caseid AND b.lastupdate = (SELECT
MAX(x.lastupdate) FROM casecomments x WHERE x.caseid=a.id)Please provide DDL, sample data and desired results, so we don't have to
guess about these things and so that we provide the right solution.
http://www.aspfaq.com/
(Reverse address to reply.)
"Aleks" <arkark2004@.hotmail.com> wrote in message
news:u0z2W0KKFHA.3928@.TK2MSFTNGP09.phx.gbl...
> I am doing a left join in this query, but only the records that have a
join
> are displayed.
> There is a record in "cases" with no "casecomments" but it is not
displayed
> .. please help:
> SELECT *
> FROM cases a
> left join casecomments as b on a.id = b.caseid AND b.lastupdate = (SELECT
> MAX(x.lastupdate) FROM casecomments x WHERE x.caseid=a.id)
>|||Well, I thought it was a fairly simple issue with the structure of the
query.
SELECT *
FROM cases a
left join casecomments as b on a.id = b.caseid AND b.lastupdate = (SELECT
MAX(x.lastupdate) FROM casecomments x WHERE x.caseid=a.id)
Seems like the left join has some issue because if I have a record in the
cases table and there is no record on the casecomments table with the same
caseid then the record is not displayed, that usually happens with 'inner
join', why would it happen with the statement above if it is a left join ?
A
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:O2O9F5KKFHA.2728@.TK2MSFTNGP10.phx.gbl...
> Please provide DDL, sample data and desired results, so we don't have to
> guess about these things and so that we provide the right solution.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Aleks" <arkark2004@.hotmail.com> wrote in message
> news:u0z2W0KKFHA.3928@.TK2MSFTNGP09.phx.gbl...
> join
> displayed
>|||> Well, I thought it was a fairly simple issue with the structure of the
> query.
Too many assumptions. If you can't be bothered to provide DDL and something
other than a word problem describing the solution you're after, I guess that
solution isn't too important to you. Next thread for me.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||Aleks,
Looking at your query, there is no reason why every record from the cases
table should not be displayed... Are you sure the record you are expecting t
o
see is in there? And that the sql you posted is exacttly what you ran ?
Sorry to question that, but what posted seems to me to be inconsistent with
the results you got...
"Aleks" wrote:

> I am doing a left join in this query, but only the records that have a joi
n
> are displayed.
> There is a record in "cases" with no "casecomments" but it is not displaye
d
> ... please help:
> SELECT *
> FROM cases a
> left join casecomments as b on a.id = b.caseid AND b.lastupdate = (SELECT
> MAX(x.lastupdate) FROM casecomments x WHERE x.caseid=a.id)
>
>|||Is not that I can't be bothered, is that I don't know how to do it.
A
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ee0XGPLKFHA.3064@.TK2MSFTNGP12.phx.gbl...
> Too many assumptions. If you can't be bothered to provide DDL and
> something
> other than a word problem describing the solution you're after, I guess
> that
> solution isn't too important to you. Next thread for me.
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>|||You are right, it was something else, another inner join that should have
been left join, sorry, but thanks for the tip.
A
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:ADE383C9-0E7A-4B45-A73A-C849472AD2D6@.microsoft.com...
> Aleks,
> Looking at your query, there is no reason why every record from the
> cases
> table should not be displayed... Are you sure the record you are expecting
> to
> see is in there? And that the sql you posted is exacttly what you ran ?
> Sorry to question that, but what posted seems to me to be inconsistent
> with
> the results you got...
> "Aleks" wrote:
>|||> Is not that I can't be bothered, is that I don't know how to do it.
Did you even LOOK AT http://www.aspfaq.com/5006 ''|||Thank you, I was not aware of that page and you don't have to be so
agressive.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23w97lrLKFHA.2396@.TK2MSFTNGP12.phx.gbl...
> Did you even LOOK AT http://www.aspfaq.com/5006 ''
>sql