MySQL Query

[brett]

Man, this is killing me. I am having a problem returning results based on specific criteria. I have a database that has Employment information. One of the catagories is by property. I can return all results for all properties, and I can return all results for one property, but I can't return all results for just two or more specific properties. I have about 30 properties.
Here is a sample of what I am trying to do.

if ($EMprop != "Deer Valley Lodging")
{
$EMpropList = "jobs.prop = '$EMprop' AND";
$filter = "jobs.prop = property.propMain";
}
else

---

{
$EMpropList = "jobs.prop = 'Deer Valley Lodging' OR jobs.prop = 'Premier Resorts Corporate' AND";
$filter ="property.propMain = 'Deer Valley Lodging', property.propMain = 'Premier Resorts Corportate'";
}

---

$query = "SELECT jobs.jobNum, jobs.datePost, jobs.jobNam, jobs.prop, property.propMain, property.state, jobs.jobCat, jobs.dateClos, jobs.live FROM jobs, property WHERE live = '1' AND " . $EMpropList . " " . $filter . " ORDER BY prop AND datePost";

$resultID = mysql_query("$query", $linkID) or die(mysql_error());

The problem lies inbetween the to long lines above. To be honest, I am sure there are several problems in that query. I have tried several things and now I just don't know where I am at anymore.

HELP PLEASE!
Brett

[chads2k2]

Can you show how your table is set up? Such as fields and their properties? That would help with the coding of it....also give us an example of what you are attempting to do...no coding just how you would like the database to function and respond.

[brett]

This is going to be ugly, this is a table dump of a table called jobs

Field Type Attributes Null Default Extra Action
jobNum tinyint(11) No auto_increment Change Drop Primary Index Unique
jobCode mediumtext Yes Change Drop Primary Index Unique
jobCat tinytext Yes Change Drop Primary Index Unique
jobNam text Yes Change Drop Primary Index Unique
datePost int(11) Yes Change Drop Primary Index Unique
dateClos tinytext Yes Change Drop Primary Index Unique
prop tinytext Yes Change Drop Primary Index Unique
hours tinytext Yes Change Drop Primary Index Unique
yearRnd tinytext Yes Change Drop Primary Index Unique
wint tinytext Yes Change Drop Primary Index Unique
sum tinytext Yes Change Drop Primary Index Unique
jobReq text Yes Change Drop Primary Index Unique
jobDesc text Yes Change Drop Primary Index Unique
wage mediumtext Yes Change Drop Primary Index Unique
hourSal tinytext Yes Change Drop Primary Index Unique
contNam text Yes Change Drop Primary Index Unique
contPhon tinytext Yes Change Drop Primary Index Unique
inqEmail text Yes Change Drop Primary Index Unique
live tinyint(4) No 0 Change Drop Primary Index Unique
addInfo longtext Yes Change Drop Primary Index Unique
########################
Now here is a dump of the table called property
########################
Field Type Attributes Null Default Extra Action
id tinyint(4) No auto_increment Change Drop Primary Index Unique
propMain mediumtext No Change Drop Primary Index Unique
state tinytext No Change Drop Primary Index Unique
########################

The relation the two have is jobs.prop = property.propMain

The reason for the seperat tables is the property table populates a dropdown in the tool they use to create the job listings so that it is easy to add or remove properties via another tool.

I have been able to get returned results for the listings for two properties but it creates a listing for every record in the property.state row. For example say the result I want returned looks like this;

Golf Caddie - Premier Resorts, UT
Server- Deer Valley Lodging, UT

Those are fictional but in essence that is the result I want. Well the closest I have been able to get is either a SQL error or this;

Golf Caddie - Premier Resorts, UT
Server- Deer Valley Lodging, UT
Golf Caddie - Premier Resorts, CA
Server- Deer Valley Lodging, CA
Golf Caddie - Premier Resorts, ID
Server- Deer Valley Lodging, ID
Golf Caddie - Premier Resorts, HI
Server- Deer Valley Lodging, HI
Golf Caddie - Premier Resorts, CO
Server- Deer Valley Lodging, CO

Make sense?