MySQL Forums
Forum List  »  Newbie

Re: Accessing data from 3 tables in one query
Posted by: John Weicher
Date: August 08, 2005 08:47AM

Now, I admit that I am not an expert in MySQL query formation, but let me offer my two cents regarding at least one of the output scenarios you are experiencing.

First a question though: In your application, is it always required that a user who has a record in your JL_SchoolsFromResumes table MUST also have an entry in JL_LawSchools table? In other words, are they required to also choose an item from the drop-down list, or is this optional? If not, then I think that might be causing some problems with the way you are currently constructing your SELECT statement. Because then your first output case makes perfect sense.

Your selection statement is as follows (shortened for the sake of typing, and minus the unnecessary extra parens for readability):

SELECT * FROM JL_SFRs, Degrees, JL_LSs WHERE ;
JL_SFRs.DegreeID == Degrees.DegreeID AND ;
JL_SFRs.LocalLawSchool == JL_LSs.SchoolID AND ;
JL_SFRs.UserID == %s

Because your three filter criteria are all connect by an explicit AND, if ANY of the three filters fails, absolutely nothing is going to be returned. Therefore only users who do have a local law school selected from the DDL can satisfy the second of your filter criteria. All the records in your JL_LawSchools table have a mandatory ID(i.e. not null), presumably, so unless a record in the SchoolsFromResumes table has something other than NULL in its LocalLawSchool field, the comparison is guaranteed to fail, causing the whole SELECT to fail (return empty I mean). NULL == <Anything NOT NULL> always returns false.

So while not to beat it to death, as you may have already figured this out yourself (if so I apologize for the unnecessary explanation), it actually makes perfect sense given the logic of your select statement, that only users who do have a local law school assigned are ever going to return any records at all from this SELECT statement.

I admit that just looking quickly I don't know why you'd sometimes get ALL the schools being returned regardless of user (your second scenario), but I would not be surprised if it's a similar issue. I think you are either going to need to enforce that all the fields used for filtering and joining are not NULL (i.e. always have values that make sense for use in comparisons), or write a more robust SELECT statement to handle cases for when they don't.

I hope this helps a little, and isn't just a regurgitation of what you already know.

Options: ReplyQuote


Subject
Written By
Posted
Re: Accessing data from 3 tables in one query
August 08, 2005 08:47AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.