Records Not In A Table

Hello,

I am trying to retrieve records in a table that are not associated with
records in another table. Three tables of data are involved:

Table 1 - Building Information
Column Using - BuildingID
Table 2 - Staff Information
Columns Using - StaffID, BuildingID
Table 3 - Staff Details
Colums Using - StaffID, DetailID

My goal is to create list of building id's that have not had any details
entered into table 3. I've read
http://dev.mysql.com/doc/mysql/en/join.html but I'm not clear how to use
a join for my purposes.

Any help is appreciated.=20

Thank You

Kathy Mazur Worden
Senior Associate Web Services
Prairie Area Library System=20

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dgcdmw-win32@m.gmane.org

Re: Records Not In A Table

--=_alternative 005DDD4385257075_=
Content-Type: text/plain; charset="US-ASCII"

"Mazur Worden, Kathy" wrote on 09/07/2005 12:54:08
PM:

> Hello,
>
> I am trying to retrieve records in a table that are not associated with
> records in another table. Three tables of data are involved:
>
> Table 1 - Building Information
> Column Using - BuildingID
> Table 2 - Staff Information
> Columns Using - StaffID, BuildingID
> Table 3 - Staff Details
> Colums Using - StaffID, DetailID
>
> My goal is to create list of building id's that have not had any details
> entered into table 3. I've read
> http://dev.mysql.com/doc/mysql/en/join.html but I'm not clear how to use
> a join for my purposes.
>
> Any help is appreciated.
>
> Thank You
>
> Kathy Mazur Worden
> Senior Associate Web Services
> Prairie Area Library System
>

What you need to use is one of the OUTER JOIN predicates. I prefer LEFT
JOIN but you can use either. The outer join predicates allow you to answer
queries like: list all of the rows from one table (the one on the "left"
side of the LEFT JOIN) and optionally some rows from a second table (the
"right" side of a LEFT JOIN) where the two tables match up on some kind of
criteria.

So you could say

SELECT b.*, i.*, d.*
FROM `Building Information` b
LEFT JOIN `Staff Information` s
ON b.BuildingID = s.BuildingID
LEFT JOIN `Staff Details` d
ON s.StaffID = d.StaffID;

and see now many details have been entered for each building. Thanks to
the LEFT JOINs, all of the Building Information rows are listed at least
once. For each Staff Information record that matches a Building
Information record, there will be values in the columns that come from
that table, otherwize every value in a non-matching row from that table
will be NULL. The same thing goes for the Staff Details columns.

What you wanted to find is just a list of BuildingIDs that nobody has
listed any Staff Details for. That query would look like:

SELECT DISTINCT b.BuildingID
FROM `Building Information` b
LEFT JOIN `Staff Information` s
ON b.BuildingID = s.BuildingID
LEFT JOIN `Staff Details` d
ON s.StaffID = d.StaffID
WHERE d.DetailID is NULL;

That last line, "WHERE d.DetailID is NULL", is the one that isolates the
non-matched rows. The DISTINCT predicate de-duplicates your results. Make
better sense now?

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

--=_alternative 005DDD4385257075_=--

RE: Records Not In A Table

------_=_NextPart_001_01C5B3D8.22278B54
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Thank you. Your explanation really helped.
=20
I tried your query suggestion out but it returns a list that includes
the building id if any of the building's staff don't have a record in
the staff details table. The building id is excluded only if all staff
at the building have filled in details. Is there a way around that?

Kathy Mazur Worden
Senior Associate Web Services
Prairie Area Library System=20

=20


________________________________

From: SGreen@unimin.com [mailto:SGreen@unimin.com]=20
Sent: Wednesday, September 07, 2005 12:01 PM
To: Mazur Worden, Kathy
Cc: win32@lists.mysql.com
Subject: Re: Records Not In A Table
=09
=09


"Mazur Worden, Kathy" wrote on 09/07/2005
12:54:08 PM:
=09
> Hello,
>=20
> I am trying to retrieve records in a table that are not
associated with
> records in another table. Three tables of data are involved:
>=20
> Table 1 - Building Information
> Column Using - BuildingID
> Table 2 - Staff Information
> Columns Using - StaffID, BuildingID
> Table 3 - Staff Details
> Colums Using - StaffID, DetailID
>=20
> My goal is to create list of building id's that have not had
any details
> entered into table 3. I've read
> http://dev.mysql.com/doc/mysql/en/join.html but I'm not clear
how to use
> a join for my purposes.
>=20
> Any help is appreciated.=20
>=20
> Thank You
>=20
> Kathy Mazur Worden
> Senior Associate Web Services
> Prairie Area Library System=20
>=20
=09
What you need to use is one of the OUTER JOIN predicates. I
prefer LEFT JOIN but you can use either. The outer join predicates allow
you to answer queries like: list all of the rows from one table (the one
on the "left" side of the LEFT JOIN) and optionally some rows from a
second table (the "right" side of a LEFT JOIN) where the two tables
match up on some kind of criteria.=20
=09
So you could say=20
=09
SELECT b.*, i.*, d.*=20
FROM `Building Information` b=20
LEFT JOIN `Staff Information` s=20
ON b.BuildingID =3D s.BuildingID=20
LEFT JOIN `Staff Details` d=20
ON s.StaffID =3D d.StaffID;=20
=09
and see now many details have been entered for each building.
Thanks to the LEFT JOINs, all of the Building Information rows are
listed at least once. For each Staff Information record that matches a
Building Information record, there will be values in the columns that
come from that table, otherwize every value in a non-matching row from
that table will be NULL. The same thing goes for the Staff Details
columns.=20
=09
What you wanted to find is just a list of BuildingIDs that
nobody has listed any Staff Details for. That query would look like:=20
=09
SELECT DISTINCT b.BuildingID=20
FROM `Building Information` b=20
LEFT JOIN `Staff Information` s=20
ON b.BuildingID =3D s.BuildingID=20
LEFT JOIN `Staff Details` d=20
ON s.StaffID =3D d.StaffID=20
WHERE d.DetailID is NULL;=20
=09
That last line, "WHERE d.DetailID is NULL", is the one that
isolates the non-matched rows. The DISTINCT predicate de-duplicates your
results. Make better sense now?=20
=09
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine=20
=09


------_=_NextPart_001_01C5B3D8.22278B54--

RE: Records Not In A Table

--=_alternative 006DC58585257075_=
Content-Type: text/plain; charset="US-ASCII"

OK, then I needed to look at this another way. What if we counted how many
detail records each building has associated with it? Then those buildings
with zero records are the ones you are worried about, right?

SELECT b.BuildingID, count(d.DetailID) as detailCount
FROM `Building Information` b
LEFT JOIN `Staff Information` s
ON b.BuildingID = s.BuildingID
LEFT JOIN `Staff Details` d
ON s.StaffID = d.StaffID
GROUP BY b.BuildingID
HAVING detailCount = 0;

That should isolate all of the buildings for which NOBODY has entered any
details. Sorry about the last query, I was in a rush and didn't think it
all the way through.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


"Mazur Worden, Kathy" wrote on 09/07/2005 02:15:31
PM:

> Thank you. Your explanation really helped.
>
> I tried your query suggestion out but it returns a list that includes
> the building id if any of the building's staff don't have a record in
> the staff details table. The building id is excluded only if all staff
> at the building have filled in details. Is there a way around that?
>
> Kathy Mazur Worden
> Senior Associate Web Services
> Prairie Area Library System
>
>
>
>
> ________________________________
>
> From: SGreen@unimin.com [mailto:SGreen@unimin.com]
> Sent: Wednesday, September 07, 2005 12:01 PM
> To: Mazur Worden, Kathy
> Cc: win32@lists.mysql.com
> Subject: Re: Records Not In A Table
>
>
>
>
> "Mazur Worden, Kathy" wrote on 09/07/2005
> 12:54:08 PM:
>
> > Hello,
> >
> > I am trying to retrieve records in a table that are not
> associated with
> > records in another table. Three tables of data are involved:
> >
> > Table 1 - Building Information
> > Column Using - BuildingID
> > Table 2 - Staff Information
> > Columns Using - StaffID, BuildingID
> > Table 3 - Staff Details
> > Colums Using - StaffID, DetailID
> >
> > My goal is to create list of building id's that have not had
> any details
> > entered into table 3. I've read
> > http://dev.mysql.com/doc/mysql/en/join.html but I'm not clear
> how to use
> > a join for my purposes.
> >
> > Any help is appreciated.
> >
> > Thank You
> >
> > Kathy Mazur Worden
> > Senior Associate Web Services
> > Prairie Area Library System
> >
>
> What you need to use is one of the OUTER JOIN predicates. I
> prefer LEFT JOIN but you can use either. The outer join predicates allow
> you to answer queries like: list all of the rows from one table (the one
> on the "left" side of the LEFT JOIN) and optionally some rows from a
> second table (the "right" side of a LEFT JOIN) where the two tables
> match up on some kind of criteria.
>
> So you could say
>
> SELECT b.*, i.*, d.*
> FROM `Building Information` b
> LEFT JOIN `Staff Information` s
> ON b.BuildingID = s.BuildingID
> LEFT JOIN `Staff Details` d
> ON s.StaffID = d.StaffID;
>
> and see now many details have been entered for each building.
> Thanks to the LEFT JOINs, all of the Building Information rows are
> listed at least once. For each Staff Information record that matches a
> Building Information record, there will be values in the columns that
> come from that table, otherwize every value in a non-matching row from
> that table will be NULL. The same thing goes for the Staff Details
> columns.
>
> What you wanted to find is just a list of BuildingIDs that
> nobody has listed any Staff Details for. That query would look like:
>
> SELECT DISTINCT b.BuildingID
> FROM `Building Information` b
> LEFT JOIN `Staff Information` s
> ON b.BuildingID = s.BuildingID
> LEFT JOIN `Staff Details` d
> ON s.StaffID = d.StaffID
> WHERE d.DetailID is NULL;
>
> That last line, "WHERE d.DetailID is NULL", is the one that
> isolates the non-matched rows. The DISTINCT predicate de-duplicates your
> results. Make better sense now?
>
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
>
>

--=_alternative 006DC58585257075_=--

RE: Records Not In A Table

SELECT DISTINCT BuildingID
FROM StaffInformation AS i LEFT JOIN StaffDetails AS d=20
ON d.StaffID =3D i.StaffID
WHERE d.StaffID IS NULL

Is probably what you want.

John B.

-----Original Message-----
From: Mazur Worden, Kathy [mailto:kathymw@palsnet.info]=20
Sent: Thursday, 8 September 2005 2:24 AM
To: win32@lists.mysql.com
Subject: Records Not In A Table

Hello,

I am trying to retrieve records in a table that are not associated with
records in another table. Three tables of data are involved:

Table 1 - Building Information
Column Using - BuildingID
Table 2 - Staff Information
Columns Using - StaffID, BuildingID
Table 3 - Staff Details
Colums Using - StaffID, DetailID

My goal is to create list of building id's that have not had any details
entered into table 3. I've read
http://dev.mysql.com/doc/mysql/en/join.html but I'm not clear how to use
a join for my purposes.

Any help is appreciated.=20

Thank You

Kathy Mazur Worden
Senior Associate Web Services
Prairie Area Library System=20


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dgcdmw-win32@m.gmane.org

RE: Records Not In A Table

------_=_NextPart_001_01C5B55B.97A068F2
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Yes, that worked. Thank You!

Kathy


________________________________

From: SGreen@unimin.com [mailto:SGreen@unimin.com]=20
Sent: Wednesday, September 07, 2005 1:55 PM
To: Mazur Worden, Kathy
Cc: win32@lists.mysql.com
Subject: RE: Records Not In A Table
=09
=09

OK, then I needed to look at this another way. What if we
counted how many detail records each building has associated with it?
Then those buildings with zero records are the ones you are worried
about, right?=20
=09
SELECT b.BuildingID, count(d.DetailID) as detailCount
FROM `Building Information` b=20
LEFT JOIN `Staff Information` s=20
ON b.BuildingID =3D s.BuildingID=20
LEFT JOIN `Staff Details` d=20
ON s.StaffID =3D d.StaffID=20
GROUP BY b.BuildingID=20
HAVING detailCount =3D 0;=20
=09
That should isolate all of the buildings for which NOBODY has
entered any details. Sorry about the last query, I was in a rush and
didn't think it all the way through.=20
=09
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine=20
=09
=09
"Mazur Worden, Kathy" wrote on 09/07/2005
02:15:31 PM:
=09
> Thank you. Your explanation really helped.
> =20
> I tried your query suggestion out but it returns a list that
includes
> the building id if any of the building's staff don't have a
record in
> the staff details table. The building id is excluded only if
all staff
> at the building have filled in details. Is there a way around
that?
>=20
> Kathy Mazur Worden
> Senior Associate Web Services
> Prairie Area Library System=20
>=20
> =20
>=20
>=20
> ________________________________
>=20
> From: SGreen@unimin.com [mailto:SGreen@unimin.com]=20
> Sent: Wednesday, September 07, 2005 12:01 PM
> To: Mazur Worden, Kathy
> Cc: win32@lists.mysql.com
> Subject: Re: Records Not In A Table
> =20
> =20
>=20
>=20
> "Mazur Worden, Kathy" wrote on
09/07/2005
> 12:54:08 PM:
> =20
> > Hello,
> >=20
> > I am trying to retrieve records in a table that are not
> associated with
> > records in another table. Three tables of data are
involved:
> >=20
> > Table 1 - Building Information
> > Column Using - BuildingID
> > Table 2 - Staff Information
> > Columns Using - StaffID, BuildingID
> > Table 3 - Staff Details
> > Colums Using - StaffID, DetailID
> >=20
> > My goal is to create list of building id's that have not
had
> any details
> > entered into table 3. I've read
> > http://dev.mysql.com/doc/mysql/en/join.html but I'm not
clear
> how to use
> > a join for my purposes.
> >=20
> > Any help is appreciated.=20
> >=20
> > Thank You
> >=20
> > Kathy Mazur Worden
> > Senior Associate Web Services
> > Prairie Area Library System=20
> >=20
> =20
> What you need to use is one of the OUTER JOIN predicates. I
> prefer LEFT JOIN but you can use either. The outer join
predicates allow
> you to answer queries like: list all of the rows from one
table (the one
> on the "left" side of the LEFT JOIN) and optionally some rows
from a
> second table (the "right" side of a LEFT JOIN) where the two
tables
> match up on some kind of criteria.=20
> =20
> So you could say=20
> =20
> SELECT b.*, i.*, d.*=20
> FROM `Building Information` b=20
> LEFT JOIN `Staff Information` s=20
> ON b.BuildingID =3D s.BuildingID=20
> LEFT JOIN `Staff Details` d=20
> ON s.StaffID =3D d.StaffID;=20
> =20
> and see now many details have been entered for each
building.
> Thanks to the LEFT JOINs, all of the Building Information rows
are
> listed at least once. For each Staff Information record that
matches a
> Building Information record, there will be values in the
columns that
> come from that table, otherwize every value in a non-matching
row from
> that table will be NULL. The same thing goes for the Staff
Details
> columns.=20
> =20
> What you wanted to find is just a list of BuildingIDs that
> nobody has listed any Staff Details for. That query would look
like:=20
> =20
> SELECT DISTINCT b.BuildingID=20
> FROM `Building Information` b=20
> LEFT JOIN `Staff Information` s=20
> ON b.BuildingID =3D s.BuildingID=20
> LEFT JOIN `Staff Details` d=20
> ON s.StaffID =3D d.StaffID=20
> WHERE d.DetailID is NULL;=20
> =20
> That last line, "WHERE d.DetailID is NULL", is the one that
> isolates the non-matched rows. The DISTINCT predicate
de-duplicates your
> results. Make better sense now?=20
> =20
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine=20
> =20
>=20
=09


------_=_NextPart_001_01C5B55B.97A068F2--