Jump to content

Archived

This topic is now archived and is closed to further replies.

netwrkgod

mySQL JOIN query....I think

Recommended Posts

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.

Share this post


Link to post
Share on other sites

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:

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

neat :D

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites


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

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.