Stuart Felenstein wrote:
<query reformatted so I could read it>I'm hoping I can present this correctly. I'm trying to determine how to set up my where condition as, 1 way has already failed me. While I continue to figure this out (i'm a noob), I hope asking for some advice here won't be too awful.
There is one main table where data is inserted and that I'm querying against, but this main table is comprised of ID's from other "static tables".
VendorJobs is the main table, here is the select and from's:
------------------------------------------------------
SELECT VJ.JobID, VJ.Entered,
VSU.CompanyName,
StaIndTypes.CareerCategories,
StaUSCities.City,
USStates.States,
VJ.AreaCode,
staTaxTerm.TaxTerm,
VJ.PayRate,
staTravelReq.TravelReq,
VendorJobDetails.JobTitle,
VendorJobDetails.Details,
VJ.PostStart
FROM
VendorJobs VJ
INNER JOIN VendorSignUp VSU ON VJ.VendorID = VSU.VendorID
INNER JOIN StaIndTypes ON VJ.Industry = StaIndTypes.CareerIDs
LEFT JOIN StaUSCities ON VJ.LocationCity = StaUSCities.CityID
LEFT JOIN USStates ON VJ.LocationState = USStates.StateID
LEFT JOIN staTaxTerm ON VJ.TaxTerm = staTaxTerm.TaxTermID
INNER JOIN staTravelReq ON VJ.TravelReq = staTravelReq.TravelReqID
INNER JOIN VendorJobDetails ON VJ.JobID = VendorJobDetails.JobID
----------------------------------------------
The where condition is going to have multiple "AND"s (I've considered UNION but don't think they are appropriate here)
AND and UNION are opposites. ANDs narrow your results, because only rows which match all AND conditions are selected. UNION, like OR, increases your result set, because rows only have to match any one of the conditions. That is,
SELECT * FROM atable WHERE a = 1 OR b = 2;
is equivalent to
SELECT * FROM atable WHERE a = 1 UNION SELECT * FROM atable WHERE b = 2;
See the manual for details <http://dev.mysql.com/doc/mysql/en/UNION.html>.
First I should say that run as a complete dump, it returns all the records correctly , with all the id's translated into the correct lable. i.e State, CA is stored in VendorJobs as CA, but in the return (and this isn't the greatest example) it's California.
So now I want to add the wheres but doing:
where `VendorJobs`.`CareerCategories` = Finance is
returning an error. I think because VendorJobs only
knows Finance by Fin.
`VendorJobs`.`CareerCategories` = Finance gives you an *error* (as opposed to no match), because you have no column named Finance. You (mysql) can tell Finance is the name of a column because it has no quotes. Of course, you meant to compare VendorJobs.CareerCategories to the constant string 'Finance', so you should use
WHERE `VendorJobs`.`CareerCategories` = 'Finance' ...
But, your condition should be
WHERE column_name = 'a value in that column' ...
So, if the string 'Fin' is what is actually stored in the CareerCategories column, you need
WHERE `VendorJobs`.`CareerCategories` = 'Fin' ...
I think what it should be :
StaIndTypes`.`CareerCategories` = Finance I could be wrong, about to try it.
OK, now I'm confused. Which column of which table contains the string 'Finance' or 'Fin'? Oh, is 'Fin' an ID for the row in StaIndTypes where the full string 'Finance' resides? If that's the case, since you are joining to StaIndTypes, either match (VendorJobs.CareerCategories` = 'Fin' or StaIndTypes = 'Finance') should work.
What's confusing me is the join, and how the join maintains the integrity of the record. If that makes sense, cause what I just said, doesn't to me. Yet, that's the only way I could say it for now.
Can you elaborate? I could answer the question I *think* you're asking, but that might be a waste. What about joins is confusing you?
Alrighty, flame away!!!!
No flames. I originally learned mysql by reading the manual, reading the list, working on a mysql/php/apache project, and asking questions. There's nothing wrong with that. The trick is not to let your development race too far ahead of your understanding, so as to minimize the rewriting you have to do whenever you say, "Aha! Now I get it."
Stuart
Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
