Jump to content

mySQL JOIN query....I think


netwrkgod

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.

Link to comment
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:

Link to comment
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...

Link to comment
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...

Link to comment
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.

Link to comment
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.

Link to comment
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!!

Link to comment
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

Link to comment
Share on other sites

  • 3 weeks later...


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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.