mySQL JOIN query....I think

[netwrkgod]

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.

[sublimatica]

Hmmm. I just tried writing this query out, and it\'s harder than it looks. Well, for me it is. The barrier is that the first line of the AllDepartments table matches the criteria that it\'s not a Produce department, which means that Store 1 would be included as a Store that doesn\'t have that match.

 

I await someone\'s (beautifully simple) answer.... :? :wink:

[netwrkgod]

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

[effigy]

select * from alldepartments left join allstores on alldepartments.department!=\'Produce\' and allstores.stores=alldepartments.store group by department having department!=\'Produce\'

 

i think it\'s buggy though :x

 

i think this will need some php assistance...

[effigy]

can you...

 

select distinct store from alldeparments where department=\'Produce\';

 

then assemble a query to that finds all stores not in that query?

 

select distinct store from alldepartments where store not in (\'Store 1\');

 

store one being pulled from the first query...

[netwrkgod]

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.

[effigy]

something like this:

 

[php:1:9f66359ae4]<?php

// run query to find stores with produce

$has_produce = mysql_query(\"select distinct store from alldeparments where department=\'Produce\'\");

 

// setup first part of no produce query

$does_not_have_produce = \"select distinct store from alldepartments where store not in (\";

 

// loop through produce results and add to no produce query

for($i=0;$i<mysl_num_rows($has_produce);$i++)

{

$row = mysql_fetch_row($has_produce);

$does_not_have_produce.=\"\'$row[0]\'\";

if($i!=mysql_num_rows($has_produce)-1)

{

$does_not_have_produce.=\",\";

} else {

$does_not_have_produce.=\")\";

}

}

?>[/php:1:9f66359ae4]

 

i typed this without any testing. please patch up what i missesd, otherwise i think this should do it.

[netwrkgod]

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!!

[netwrkgod]

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!!

[effigy]

neat

i\'m sure there is some way to accomplish it with one query, but that is beyond me. sql is definately something i need to work on. but after trying to one-query it for hours i decided to give in.

[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

[pallevillesen]

SELECT DISTINCT Stores FROM AllStores 

WHERE Stores NOT IN

(SELECT Store FROM AllDepartments WHERE Department=\'Produce\')

 

Inner select gives you a list of stores with a produce department

Outer select gives you a list of all stores except the one present in the inner select...

 

P., denmark