netwrkgod

Yeah, a friend of mine said that it is almost impossible with one query. Then he said it would be possible, but I would have to do several self joins and things like that, so I should redesign my db instead ! Now I don\'t have to redesign it! Also, I edited the script above since you posted. Notice where the $row[0] is? I had to use \" instead of \' because the store name could have an apostrophe in it-just found that out during some testing. Also, I changed the query to get it from the AllStores table because if someone add\'s a store after the depts are configured the new store won\'t show up in the list. Now it works perfect now though

Here\'s a major chunk of the page that this query is used on. On the previous page, the user selected a Department that they wanted to assign to one or more stores. Now we are presenting the user with a multiple select box that only shows stores that do not already have the selected department assigned to them. <form action="assigndept.php" method="POST"> <select STYLE="color: #FFFFFF; background-color: #9999FF;" size="6" name="selectedstores[]" multiple> [php:1:861ca18f8c]<?php /*Need to get stores that don\'t have the selected dept already in them */ // run query to find stores with the selected department $has_department = mysql_query(\"SELECT DISTINCT store FROM alldepartments WHERE department=\"$newdepartment\";\"); // setup first part of $does_not_have_department query $does_not_have_department = \"SELECT DISTINCT store FROM \" . $AllStores . \" WHERE store NOT IN (\"; // loop through results and add to $does_not_have_department query for($i=0;$i<mysql_num_rows($has_department);$i++) { $row = mysql_fetch_row($has_department); $does_not_have_department.=\"\"$row[0]\"\"; if($i!=mysql_num_rows($has_department)-1) { $does_not_have_department.=\",\"; } else { $does_not_have_department.=\")\"; } } //Run the finished query to get the stores that don\'t have the selected dept. $sql = $does_not_have_department; $result = mysql_query($does_not_have_department); if (!$result) { $errmysql = \"<font class=mysqlerror>ERROR: \" . mysql_errno(). \": \" . mysql_error() . \"</font>\"; $log = \"<font class=loguser>$username</font> ran query: <font class=logsql>$sql</font>\"; write_to_log(\"$log<br>$errmysql\"); $relative_url = \"error.php\"; header(\"Location: http://\".$_SERVER[\'HTTP_HOST\'] .dirname($_SERVER[\'PHP_SELF\']) .$relative_url .\"?\" .SID); exit; } if(mysql_num_rows($result)==0) { $relative_url = \"deptalreadyassigned.php\"; header(\"Location: http://\".$_SERVER[\'HTTP_HOST\'] .dirname($_SERVER[\'PHP_SELF\']) .$relative_url .\"?\" .SID); exit; } $log = \"<font class=loguser>$username</font> ran query: <font class=logsql>$sql</font>\"; write_to_log($log); $log = \"\"; if(mysql_num_rows($result)) { while($row = mysql_fetch_row($result)) { print(\"<option value=\"$row[0]\">$row[0]</option>\"); } } else { print(\"<option value=\"\"></option>\"); } ?>[/php:1:861ca18f8c] </select> Thanks again for the help-it works great!!

IT WORKS!!!! I\'d have to say that was pretty amazing. Thank you very much. You only had a couple of typos which I found quickly. I\'ll finish the script later on today and post it here. Thanks again!!

I\'ll try what you suggested in the morning...well later this morning. I\'m not at all against using php to solve the problem if you have some suggestions along those lines too. I was thinking along the same lines. The SELECT DISTINCT works fine for the first query, but I dont know how to compare the results of two queries against one another. If someone could walk me through it, I think that\'s the way to go. The other alternative would be to add possibly hundereds of other tables into the db, and I\'d rather not do that.

Yeah, it\'s got my brain fried. I had to take a break from it for a while. Hope someone has the answer!!

I\'m fine with the simple queries, but this one seems to be out of my league. I have two tables that I need to get information from. The AllStores table has a field called Stores with data in it like: -Store 1 -Store 2 -Store 3 -etc. (unique store names) The AllDepartments table has two fields called Store and Department with rows like: Store Department Store 1 Bakery Store 1 Produce Store 2 Bakery Store 2 Meat Store 3 Bakery Store 3 Meat Notice that Store 1 has a Produce dept. and no other store does. What I need the query to do is: Find all stores that don\'t have a Produce department in them. Which, in this case would be Store 2 and Store 3. Please help! Thanks.