Jump to content


Photo

mySQL JOIN query....I think


  • Please log in to reply
11 replies to this topic

#1 netwrkgod

netwrkgod
  • Members
  • Pip
  • Newbie
  • 6 posts
  • LocationMichigan, USA

Posted 19 December 2002 - 05:52 PM

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.
All your base are belong to us.

#2 sublimatica

sublimatica
  • New Members
  • Pip
  • Newbie
  • 1 posts
  • LocationSussex, UK

Posted 20 December 2002 - 02:47 AM

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:
Ben [br]Sussex, UK

#3 netwrkgod

netwrkgod
  • Members
  • Pip
  • Newbie
  • 6 posts
  • LocationMichigan, USA

Posted 20 December 2002 - 05:43 AM

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

#4 effigy

effigy
  • Staff Alumni
  • Advanced Member
  • 3,600 posts
  • LocationIL

Posted 21 December 2002 - 12:04 AM

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...
Regexp | Unicode Article | Letter Database
/\A(e)?((1)?ff(?:(?:ig)?y)?|f(?:ig)?)\z/

#5 effigy

effigy
  • Staff Alumni
  • Advanced Member
  • 3,600 posts
  • LocationIL

Posted 21 December 2002 - 06:36 AM

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...
Regexp | Unicode Article | Letter Database
/\A(e)?((1)?ff(?:(?:ig)?y)?|f(?:ig)?)\z/

#6 netwrkgod

netwrkgod
  • Members
  • Pip
  • Newbie
  • 6 posts
  • LocationMichigan, USA

Posted 21 December 2002 - 08:28 AM

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.
All your base are belong to us.

#7 effigy

effigy
  • Staff Alumni
  • Advanced Member
  • 3,600 posts
  • LocationIL

Posted 21 December 2002 - 06:45 PM

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.
Regexp | Unicode Article | Letter Database
/\A(e)?((1)?ff(?:(?:ig)?y)?|f(?:ig)?)\z/

#8 netwrkgod

netwrkgod
  • Members
  • Pip
  • Newbie
  • 6 posts
  • LocationMichigan, USA

Posted 21 December 2002 - 07:22 PM

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!!
All your base are belong to us.

#9 netwrkgod

netwrkgod
  • Members
  • Pip
  • Newbie
  • 6 posts
  • LocationMichigan, USA

Posted 21 December 2002 - 08:12 PM

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:&nbsp;&nbsp;\" . 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!!
All your base are belong to us.

#10 effigy

effigy
  • Staff Alumni
  • Advanced Member
  • 3,600 posts
  • LocationIL

Posted 21 December 2002 - 08:31 PM

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.
Regexp | Unicode Article | Letter Database
/\A(e)?((1)?ff(?:(?:ig)?y)?|f(?:ig)?)\z/

#11 netwrkgod

netwrkgod
  • Members
  • Pip
  • Newbie
  • 6 posts
  • LocationMichigan, USA

Posted 21 December 2002 - 08:38 PM

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
All your base are belong to us.

#12 pallevillesen

pallevillesen
  • Members
  • PipPipPip
  • Advanced Member
  • 135 posts
  • LocationDenmark

Posted 08 January 2003 - 11:16 AM


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
Palle Villesen, www.birc.dk [br]Bioinformatics Research Center




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users