List:MySQL on Win32« Previous MessageNext Message »
From:Kathy Mazur Worden Date:September 9 2005 4:29pm
Subject:RE: Records Not In A Table
View as plain text  
Yes, that worked.  Thank You!

Kathy


________________________________

	From: SGreen@stripped [mailto:SGreen@stripped] 
	Sent: Wednesday, September 07, 2005 1:55 PM
	To: Mazur Worden, Kathy
	Cc: win32@stripped
	Subject: RE: Records Not In A Table
	
	

	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" <kathymw@stripped> 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@stripped [mailto:SGreen@stripped] 
	>    Sent: Wednesday, September 07, 2005 12:01 PM
	>    To: Mazur Worden, Kathy
	>    Cc: win32@stripped
	>    Subject: Re: Records Not In A Table
	>    
	>    
	> 
	> 
	>    "Mazur Worden, Kathy" <kathymw@stripped> 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 
	>    
	> 
	


Thread
Records Not In A TableKathy Mazur Worden7 Sep
  • Re: Records Not In A TableSGreen7 Sep
RE: Records Not In A TableKathy Mazur Worden7 Sep
  • RE: Records Not In A TableSGreen7 Sep
RE: Records Not In A Tablejbonnett8 Sep
RE: Records Not In A TableKathy Mazur Worden9 Sep