Well I feel like maybe I wasted some bandwidth here. I think what I'm looking for is a square peg in a round hole. That won't work. More to the point :) , I do not having a problem with the AND / OR / IN / NOT / etc. What I think I was attempting was to come up with a SQL statement that will work with an unknown factor. I'm now thinking that this isn't the path to take. Here is where I'm at, and as this goes into web dev, I am trying to figure out if I have any choices strictly using SQL. I have 3 choices (3 seperate fields to query) a user can submit, but none are strictly required. Chances are only 1 will be used. If I do a "where x = 1 or y = 2 or z = 3" then regardless of the y or z, x is coming back. It's not even looking at y or z. All "AND" requires all three conditions are met. Lastly I was interested in NOT, since by default there is an assigned value to the non used form field. Yet, all of my where conditions are = "resset1..." which is the variable that gets passed over. Not sure how to say "where LocationState NOT XXX" and leave the "resset" in place. Sorry if this is all confusing. I am taking my time and wouldn't think of just throwing something up unless it works in the 999,999,999 ways it should.
Thank you, Stuart --- Michael Stassen <[EMAIL PROTECTED]> wrote: > > Stuart Felenstein wrote: > > > 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: > <query reformatted so I could read it> > > > ------------------------------------------------------ > > 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]
