►
From YouTube: TTEOT Training Video - S1E9 - SQL: Coalesce
Description
You can't get something out of nothing - unless perhaps you understand the essence of coalesce, more or less. This is good stuff.
Referenced code: https://wiki.koha-community.org/wiki/SQL_Reports_Library
Music: https://www.bensound.com
A
A
Every
other
week,
videos
we've
been
talking
about
tips
and
tricks
with
sql
and
kohar
reports.
Lately
and
right
now
on
your
screen,
you
should
see
the
gohan
us
home
page
and
you
can
always
find
our
videos
if
you
go
over
here,
to
learn
from
us,
learn
from
kaha
us
and
there's
a
link
here.
That'll,
take
you
to
the
bottom
of
the
page
where
the
videos
are
and
we're
recording
ahead
for
the
holidays.
I'm
not
sure
what
number
video
this
would
be.
You
probably,
I
think,
maybe
number
eight.
A
Yeah
by
the
time
you
see
this
one
there'll
be
there'll,
be
three
or
four
there'll,
be
at
least
three
more
in
this
in
this
row.
So
but
there's
all
kinds
of
useful
stuff.
You
can
find
here
on
the
quahog
us
education
page,
and
this
is
another
time
we're
going
to
be
talking
about
sql
reports,
so
I'll
also
hop
over
here
to
the
coho
wiki.
A
It
says
wiki.coha-community.org
and
if
you
look
for
sql
reports
library,
there
are
a
ton
of
reports
here
and
what
we've
been
working
on
for
the
last
few
weeks,
we're
kind
of
going
through
some
of
these
tips
and
tricks
of
how
to
write
better
reports
and
what
I'm
going
to
show
you
today.
A
A
Because
we
don't
have
enough
data
in
the
koha
us
demo
system,
but
actually
let
me
edit
this
report
first
and
show
you
what
I'm
trying
to
do
so.
This
is
something
that
happens
to
me
from
time
to
time
where
somebody
will
want
to
know
information
about
an
item.
That's
already
been
deleted
and
they
will
usually
have
a
barcode
number
for
an
item.
A
A
So
that's
all
that's
really
happening
when
you
delete
an
item.
Is
it's
just
taking
the
data
out
of
the
items
table
and
it's
moving
it
over
into
another
table?
A
A
Been
purged
in
a
while,
then
it's
possible
that
the
data
is
still
there
in
the
deleted
items.
A
A
Otherwise,
we
would
have
a
really
bloated
deleted
address
right.
A
This
report
is:
I'm
getting
biblio
number,
the
item
number
barcode
home
branch
holding
branch,
I'm
just
getting
some
really
basic
information
from
the
deleted
items,
and
so
I'm
going
to
run
the
report
and
it's
going
to
ask
me
for
a
barcode
number.
A
So
those
are
two
different
barcode
numbers
and
they
give
me
some
basic
information
about
the
about
the
item.
I
don't
have
any
author
or
data
author
or
title
data,
though,
and
that's
because
in
this
report
I'm
only
looking
at
the
deleted
items
table
and
the
items
table
and
the
deleted
items
table.
Don't
include
any
of
that
bibliographic
information
like
author,
title,
etc.
A
A
A
A
A
B
A
Biblio
has
been
deleted,
so
this
report
will
get
the
data,
but
the
it
won't
get
it
for
the
brian
jake's
book
because
the
item
has
been
deleted,
but
the
bibliography
has
not
been
deleted.
So
I've
got
a
situation
where
you
know
to.
I
can't
use
the
same
report
for
just
any
old
barcode.
If
I
wanted
to.
If
I-
and
if
I
don't
know
if
the
if
the
biblio.
A
Deleted
or
not,
I
don't
know
which
report
to
run
so
a
next
logical
step,
then,
would
be
to
work
out
a
way.
I
can
run
a
report
and
get
the
result
by
looking
at
either
of
those
tables
and
that's
what
this
one
does.
But
it's
awkward
because
I
get
two
author
titles,
author
columns
and
two
title
columns.
A
Why
is
that?
Well?
And
let
me
show
you
so
that's
for
the
sesame
street
book
and
if
I
run
it
for
this
other
book,
one
I
get
the
author
over.
A
A
A
So
it's
showing
me
all
of
the
information
from
it's
always
showing
me
all
of
the
information
from
the
items
take
from
the
deleted
items
stable,
but
it's
only
showing
me
the
deleted
biblio
information
if
biblio's
been
deleted-
and
it's
only
showing
me
the
biblio
information
if
the
biblio
has
not
been
deleted.
A
So
the
way
to
solve
that
problem,
the
way
to
get
it
so
that
you've
got
the
information
in
one
column,
no
matter
which
barcode
number
you
run.
A
Is
to
do
coalesce
is
one
way
to
do
it
very
nice.
Coalesce
is
the
easiest
way.
I
know
how
to
so.
What
we're
doing
here
is
we're
saying,
give
me
all
the
information
from
deleted
items
and
join
it
with
biblio
and
deleted
biblio
and
then
coalesce
instead
of
giving
me
both
both
the
author
from
biblio
and
elite
biblio,
it's
saying
take
these
two
coalesce
them
combine
them
so
that
if
one
is
blank,
you
get
the
data
from
the
other
one
if
the
other
one's
playing,
keep
it
data
from
the
other
one.
A
So
that
way,
I
only
end
up
with
one
column
for
author
and
one
column
for
the
title
now.
This
is
a
real,
simple
report
that
I
wrote
to
to
do
this
one
example,
but
you
know
in
the
in
the
video
that
we
did
two
weeks
ago.
Well,
it's
two
weeks
for
everybody
else.
It
was
like
a
half
an
hour
ago
for
the
two
of
us.
A
That
you
know
from
the
data
that's
similar
from
in
this
case
item
to
biblio
and
deleted
biblio.
You
can
call
us
those
columns
or
you
could
coalesce
in
the
one
that
we
did
just
a
little
while
ago
we
did,
I
think,
was
number
eight.
A
A
Here
we
said
if,
if
this
value,
if
there's
no
value
here,
plug
in
a
string
that
just
has
a
zero
in
it,
that
way
we
could
put
a
put
up
essentially,
essentially
that
way
we
didn't
have
a
blank
space
in
that.
A
Yeah,
because
that
would
that
would
drive
christopher
nuts,
if
there's,
but
by
using
coalesce
there,
so
we
we
could,
we
could
put
any
column
there.
We
could
say
join.
You
know,
combine
the
data
from
this
one
and
this
one
if
this
one's
empty
put
the
other
one
there
it's
kind
of
like
an
if
then
statement,
but
you
don't
have
to,
and
that's
actually
what
you
said
you
used
in
some
of
those
cases
here
right
then
statement,
but
this
coalesce
you
don't
have
to
do.
A
A
So
coalesce
is
a
really
useful
sql
function
that
I
find
it
particularly
useful
when
you're
trying
to
get
item
information,
particularly
from
biblio
and
right
right,
because
that
is
you
know
with
the
system
as
big
as
ours.
It's
not
uncommon
for
me
to
get
those
questions
about.
You
know.
I've
got
a
book,
I
scan
it.
Nothing
happens
this
way.
I
can
just
scan
that
barcode
and
they'll.
Give
me
information
about
that
and
I
don't
have
to
go
well.
B
A
B
That's
great,
well,
george,
thank
you
very
much.
I
you
know
I
I
appreciate
it
when
I
when
I
learn
a
new
feature.
I've
I've
not
worked
with
coalesce
before,
but
it
certainly
is
something
that
would
come
in
handy
with
reports
and
especially
filling
in
the
gaps
where
there's
no
data
or
you
know
pulling
from
a
couple
different
tables.
That's
very,
very
handy
learning
about
that.
So
thank
you
very
much
for
sharing
that
no
problem
so,
like
I
said
this,
video
is
a
lot
shorter
than
the
last
minutes.