blank cells cause trouble on join

I have a few tables that I am joining together and one of the joins is on a
field that almost always has a code in it. Sometimes it is a blank (not a
null) if the clerk didn't type anything into it. The table that I am joining
to this one, has many cells in the join field thhat are blank and those rows
are used for commenting the other records in the table (I know it is stupid
but I didn't design it).
If I do a regular join whenever it comes upon one of these blanks it joins
to all of the comment records in the 2nd table.
What would be the easiest way to get around this problem?
Below is the statement as far as I got befor the last join messed me up.
cdockets.cdevnttyp has the occasional blank in it and ccodes.codevent is
blank in all rows where they are being used as comments.
SELECT cdcaseid as caseID, cdevtno as eventNum, replace()cdevtdt as
eventDate, cdevtcgpy as ChargedParty, cdcasetyp as caseType,
cdevtjm as Judge, cdevtattny as Attorney,cpcaseid, cpcasetyp,
cpartynum,cpname,cpattny, cgcur9, cgcur3, codeshort, codelong,
codentry
from osmccsdb.cdocket
left outer join osmccsdb.cparty
on osmccsdb.cparty.cpcaseid=osmccsdb.cdocket.cdcaseid
left outer join osmccsdb.ccharge
on osmccsdb.ccharge.cgcaseID = osmccsdb.cdocket.cdcaseid
left outer join osmccsdb.ccodes
on osmccsdb.cdocket.cdevttyp = osmccsdb.ccodes.codentry
order by osmccsdb.cdocket.cdevtdt descLinda,
A LEFT JOIN will return all of the records from the table on left side of
the JOIN keyword and any matching records on the right. I don't have any
sample data to work with but you might look at the NULLIF function and see
if it might help you in your situation.
HTH
Jerry
"Linda Ibarra" <LindaIbarra@.discussions.microsoft.com> wrote in message
news:8953B911-7B98-4A20-BD74-C9FFA335B9B2@.microsoft.com...
>I have a few tables that I am joining together and one of the joins is on a
> field that almost always has a code in it. Sometimes it is a blank (not a
> null) if the clerk didn't type anything into it. The table that I am
> joining
> to this one, has many cells in the join field thhat are blank and those
> rows
> are used for commenting the other records in the table (I know it is
> stupid
> but I didn't design it).
> If I do a regular join whenever it comes upon one of these blanks it joins
> to all of the comment records in the 2nd table.
> What would be the easiest way to get around this problem?
> Below is the statement as far as I got befor the last join messed me up.
> cdockets.cdevnttyp has the occasional blank in it and ccodes.codevent is
> blank in all rows where they are being used as comments.
> SELECT cdcaseid as caseID, cdevtno as eventNum, replace()cdevtdt as
> eventDate, cdevtcgpy as ChargedParty, cdcasetyp as caseType,
> cdevtjm as Judge, cdevtattny as Attorney,cpcaseid, cpcasetyp,
> cpartynum,cpname,cpattny, cgcur9, cgcur3, codeshort, codelong,
> codentry
> from osmccsdb.cdocket
> left outer join osmccsdb.cparty
> on osmccsdb.cparty.cpcaseid=osmccsdb.cdocket.cdcaseid
> left outer join osmccsdb.ccharge
> on osmccsdb.ccharge.cgcaseID = osmccsdb.cdocket.cdcaseid
> left outer join osmccsdb.ccodes
> on osmccsdb.cdocket.cdevttyp = osmccsdb.ccodes.codentry
> order by osmccsdb.cdocket.cdevtdt desc|||I see this with some tools and it is kind of a pain. First thing I would
note is that it is usually a bad idea to have user inputted values being
what you are joining on (but I know you said you didn't design it)
I would probaby just add a condition to your join that says something like:
AND ccodes.codevent <> '' AND the other column too
This will eliminate them from the join. Or if you want the '' rows from one
side, but not the other, then change the '' in the join:
case when ccodes.codevent = '' then 'NOT POSSIBLE' else ccodes.codevent end
Or you could use NULL instead of 'NOT POSSIBLE'
It might not be great for performance, so you might have to do some
trickiness if you have scads of data, but that is just the price you pay :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Linda Ibarra" <LindaIbarra@.discussions.microsoft.com> wrote in message
news:8953B911-7B98-4A20-BD74-C9FFA335B9B2@.microsoft.com...
>I have a few tables that I am joining together and one of the joins is on a
> field that almost always has a code in it. Sometimes it is a blank (not a
> null) if the clerk didn't type anything into it. The table that I am
> joining
> to this one, has many cells in the join field thhat are blank and those
> rows
> are used for commenting the other records in the table (I know it is
> stupid
> but I didn't design it).
> If I do a regular join whenever it comes upon one of these blanks it joins
> to all of the comment records in the 2nd table.
> What would be the easiest way to get around this problem?
> Below is the statement as far as I got befor the last join messed me up.
> cdockets.cdevnttyp has the occasional blank in it and ccodes.codevent is
> blank in all rows where they are being used as comments.
> SELECT cdcaseid as caseID, cdevtno as eventNum, replace()cdevtdt as
> eventDate, cdevtcgpy as ChargedParty, cdcasetyp as caseType,
> cdevtjm as Judge, cdevtattny as Attorney,cpcaseid, cpcasetyp,
> cpartynum,cpname,cpattny, cgcur9, cgcur3, codeshort, codelong,
> codentry
> from osmccsdb.cdocket
> left outer join osmccsdb.cparty
> on osmccsdb.cparty.cpcaseid=osmccsdb.cdocket.cdcaseid
> left outer join osmccsdb.ccharge
> on osmccsdb.ccharge.cgcaseID = osmccsdb.cdocket.cdcaseid
> left outer join osmccsdb.ccodes
> on osmccsdb.cdocket.cdevttyp = osmccsdb.ccodes.codentry
> order by osmccsdb.cdocket.cdevtdt desc|||I would prefer updating those columns with some value,say string and
eliminate that in the where clause. and make it default so that problem wil
l
not come again
--
Regards
R.D
--Knowledge gets doubled when shared
"Linda Ibarra" wrote:

> I have a few tables that I am joining together and one of the joins is on
a
> field that almost always has a code in it. Sometimes it is a blank (not a
> null) if the clerk didn't type anything into it. The table that I am joini
ng
> to this one, has many cells in the join field thhat are blank and those ro
ws
> are used for commenting the other records in the table (I know it is stupi
d
> but I didn't design it).
> If I do a regular join whenever it comes upon one of these blanks it joins
> to all of the comment records in the 2nd table.
> What would be the easiest way to get around this problem?
> Below is the statement as far as I got befor the last join messed me up.
> cdockets.cdevnttyp has the occasional blank in it and ccodes.codevent is
> blank in all rows where they are being used as comments.
> SELECT cdcaseid as caseID, cdevtno as eventNum, replace()cdevtdt as
> eventDate, cdevtcgpy as ChargedParty, cdcasetyp as caseType,
> cdevtjm as Judge, cdevtattny as Attorney,cpcaseid, cpcasetyp,
> cpartynum,cpname,cpattny, cgcur9, cgcur3, codeshort, codelong,
> codentry
> from osmccsdb.cdocket
> left outer join osmccsdb.cparty
> on osmccsdb.cparty.cpcaseid=osmccsdb.cdocket.cdcaseid
> left outer join osmccsdb.ccharge
> on osmccsdb.ccharge.cgcaseID = osmccsdb.cdocket.cdcaseid
> left outer join osmccsdb.ccodes
> on osmccsdb.cdocket.cdevttyp = osmccsdb.ccodes.codentry
> order by osmccsdb.cdocket.cdevtdt desc|||You are a freaking genius!!! I am not sure that I will be allowed to do tha
t
but if I can what a relief! These are court records and I am not sure how
weird they will be about me changing data.
"R.D" wrote:
> I would prefer updating those columns with some value,say string and
> eliminate that in the where clause. and make it default so that problem w
ill
> not come again
> --
> Regards
> R.D
> --Knowledge gets doubled when shared
>
> "Linda Ibarra" wrote:
>|||It was also pointed out to me that if I just created a view of this data
where the records with blank fields were not included and used that for the
join, that that would also eliminate this problem.
"Linda Ibarra" wrote:

> I have a few tables that I am joining together and one of the joins is on
a
> field that almost always has a code in it. Sometimes it is a blank (not a
> null) if the clerk didn't type anything into it. The table that I am joini
ng
> to this one, has many cells in the join field thhat are blank and those ro
ws
> are used for commenting the other records in the table (I know it is stupi
d
> but I didn't design it).
> If I do a regular join whenever it comes upon one of these blanks it joins
> to all of the comment records in the 2nd table.
> What would be the easiest way to get around this problem?
> Below is the statement as far as I got befor the last join messed me up.
> cdockets.cdevnttyp has the occasional blank in it and ccodes.codevent is
> blank in all rows where they are being used as comments.
> SELECT cdcaseid as caseID, cdevtno as eventNum, replace()cdevtdt as
> eventDate, cdevtcgpy as ChargedParty, cdcasetyp as caseType,
> cdevtjm as Judge, cdevtattny as Attorney,cpcaseid, cpcasetyp,
> cpartynum,cpname,cpattny, cgcur9, cgcur3, codeshort, codelong,
> codentry
> from osmccsdb.cdocket
> left outer join osmccsdb.cparty
> on osmccsdb.cparty.cpcaseid=osmccsdb.cdocket.cdcaseid
> left outer join osmccsdb.ccharge
> on osmccsdb.ccharge.cgcaseID = osmccsdb.cdocket.cdcaseid
> left outer join osmccsdb.ccodes
> on osmccsdb.cdocket.cdevttyp = osmccsdb.ccodes.codentry
> order by osmccsdb.cdocket.cdevtdt desc|||yup, you can always have a view. yet what if you want to use data in another
sproc or function or in another view. To solve this permanently have
amechanism where either you dont allow such values(?) or such blanks are
automatically replace with something like 'No Data'
--
Regards
R.D
--Knowledge gets doubled when shared
"Linda Ibarra" wrote:
> It was also pointed out to me that if I just created a view of this data
> where the records with blank fields were not included and used that for th
e
> join, that that would also eliminate this problem.
> "Linda Ibarra" wrote:
>