[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 307
  • Last Modified:

NOT IN query

OK
A student takes one Program.  The Program ID is in the Students table.
We have a table that assigns courses to a program.
A Program can have many courses.
We also have a table that assigns courses to a student(Course ID and Student ID), the courses that are assigned are limited to only the courses that are assigned to the Program that the student is taking.  

We are constructing 2 queries, one to show all the courses that a student is taking.  This is easy and is working.

The next query to show the list of course that the student is YET to take which belongs to the Program he is taking.  

We have an sql statement but there are errors round about the NOT IN syntax.

SELECT programcourses.courseid FROM programcourses WHERE programcourses.programid = ('$programid') AND programcourses.courseid NOT IN(SELECT studentcourses.courseid FROM studentcourses WHERE studentid='".$_GET['id']."')

Any suggestions??
0
kephillips
Asked:
kephillips
  • 16
  • 8
  • 7
  • +2
3 Solutions
 
madwaxCommented:
Hi kephillips,

When I run the query:

SELECT programcourses.courseid
FROM programcourses
WHERE programcourses.programid = '1' AND programcourses.courseid NOT
IN (
SELECT studentcourses.courseid
FROM studentcourses
WHERE studentid = '1'
) LIMIT 0 , 30

in mine MySQL 4.1.1-alpha-nt it works fine without errors with the following two tables:

programcourses(programid,courseid)
studentcourses(studentid,courseid)

maybe you could post a short dump of the tables so that I can try on your design?


//jan
0
 
kephillipsAuthor Commented:
madwax,
we have version mysql 3.23. Could this be the problem??
0
 
madwaxCommented:
yes it most certainly is since subqueries where introduced in version 4.x

sorry,
//jan
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
jkna_gunnCommented:
dont worry im still back in 3.23 too.
so like me you will have to do the query in two parts then use php to work out the difference.
shouldn't be too hard.

$courses1 = array();
$courses2 = array();

$query1 = "SELECT programcourses.courseid FROM programcourses WHERE programcourses.programid = ('$programid')";

$query2= "SELECT studentcourses.courseid FROM studentcourses WHERE studentid='".$_GET['id']."')";

$result = mysql_query($query1);
while ($row = mysql_fetch_array($result)) {
    array_push($courses1,$row[0]);
}

$result = mysql_query($query2);
while ($row = mysql_fetch_array($result)) {
    array_push($courses2,$row[0]);
}

$finalcourses = array_diff($courses1,$courses2);

// well something like that anyway!
0
 
snoyes_jwCommented:
SELECT * FROM student, programcourses
  LEFT JOIN studentcourses
    ON studentcourses.studentid = student.studentid AND programcourses.courseid = studentcourses.courseid
  WHERE student.programid = programcourses.programid AND studentcourses.courseid IS NULL
  ORDER BY student.studentid, programcourses.courseid;
0
 
kephillipsAuthor Commented:
hi again,
here's what I have so far:
////////////VARIABLES/////////////
$programid = $rowed['programid'];

/////////////QUERY/////////////////
$resulted2 = mysql_query("SELECT courseid FROM studentcourses
WHERE studentid='".$_GET['id']."'") or die ('no se pudo');
while ($a = mysql_fetch_row($resulted2)){
print "<tr>\n";
foreach ($a as $b)
print "\t<td><font face=arial size=1/>$b</font></td>\n";
print "</tr>\n";
}
$resulting = mysql_query ("SELECT programcourses.courseid FROM programcourses WHERE programcourses.programid = ('$programid') AND programcourses.courseid !=('$b')");
$num_rowsing = mysql_num_rows($resulting);
print "Hay un total de $num_rowsing materias tomadas.<P>";
print "<table width=80% border=1>\n";
while ($get_infoing = mysql_fetch_row($resulting)){
print "<tr>\n";
foreach ($get_infoing as $fielding)
print "\t<td><font face=arial size=1/>$fielding</font></td>\n";
print "</tr>\n";
}
print "</table>\n";

Thanks very much for any help.
0
 
snoyes_jwCommented:
Did you try the query I posted above?
0
 
kephillipsAuthor Commented:
snoyes_jw,
yes, didn't seem to work. For some reason you selected all the students and that's all the list I got.

1.studentcourses.studentid where studentid = $studentid is to see what courses student has taken

2.programcourses.programid = students.programid is to see what courses student takes in all.

so I would need to take query2 - query1 to find out what the student lacks.

0
 
snoyes_jwCommented:
Add
AND student.studentid = $studentIdVariable
to the WHERE clause.  This query will give you the list of missing courses for the given student:

SELECT * FROM student, programcourses // implicit join - each student, each programcourse
  LEFT JOIN studentcourses // LEFT JOINs include records that exist in student, but might not in studentcourses
    ON studentcourses.studentid = student.studentid // limit the LEFT JOIN to match students with courses
      AND programcourses.courseid = studentcourses.courseid // further limit the LEFT JOIN to only the required courses
  WHERE student.programid = programcourses.programid // limit the implicit join to match students to their program
    AND studentcourses.courseid IS NULL // only keep those results where there was no studentcourse found
    AND student.studentid = $studentid // for the one student in question
  ORDER BY student.studentid, programcourses.courseid;

Another way to do it, using 2 queries and some php code:

$result = mysql_query("SELECT * FROM studentcourses WHERE studentcourses.studentid = $studentid");
$sql = "SELECT * FROM students, programcourses WHERE students.studentid = $studentid AND students.programid = programcourses.programid AND programcourses.programid NOT IN ('FakeCourseNumber'";
while ($row = mysql_fetch_assoc($result))
    $sql .= ", " . $row['courseid'];
$sql .= ")";
$result = mysql_query($sql);
0
 
kephillipsAuthor Commented:
snoyes_jw,
NOT IN won't work on mysql 3.23 (which happens to be what i'm using).
0
 
kephillipsAuthor Commented:
I think I'm best upgrading to mysql 4+. Can someone tell me where to download this? Preferably RPM for RH9. Thanks
0
 
jkna_gunnCommented:
kephillips, did you try my solution? or is it not appropriate?
0
 
kephillipsAuthor Commented:
jkna_gunn,

Thanks for the help. For some reason though, nothing worked with the script you gave me.
Here is what I have so far. It removes one of the taken courses, but not several....the problem is that $a is LIST of retrieved courseid's but not one by one as a loop....(maybe this will help clarify my problem to you).

$resulted2 = mysql_query("SELECT idmateria FROM studentcourses
WHERE idestudiante='".$_GET['id']."'") or die ('no se pudo');
while ($rowed2=mysql_fetch_array($resulted2))
{
$a= $rowed2['idmateria'];
}

$resulting = mysql_query ("SELECT programcourses.idmateria FROM programcourses
WHERE programcourses.idprograma = ('$programid') AND programcourses.idmateria !=('$a')");
0
 
jkna_gunnCommented:
ok let me check im on the same page here,

you want to first query all courses that are available for the given program

then you want to remove all the courses from that list that the given student has already taken.

is that right?

if so i think i dont see why my code would not work..

the first array $choices1 should have all the courses for a program.

the second array $choices2 should have all the courses for a given student

the function array_diff will return all values in $choices1 that are not present in $choices2

can you tell me what did not work please

0
 
kephillipsAuthor Commented:
Ok. I'm a newbie at PHP, so not sure what went wrong.
The queries I need to make are:
1. WHAT COURSES STUDENT HAS TAKEN
2. LIST COURSES THAT STUDENT LACKS

Your code seems to be the right idea, I'm just not REAL sure how to apply it to my situation.
THESE ARE THE QUERIES THAT HAVE TO BE MADE...
here is query for #1
$result = mysql_query ("SELECT IDmateria  FROM studentcourses
WHERE IDestudiante='".$_GET['id']."' ORDER BY IDmateria ASC");

And query for #2
$a = mysql_query("SELECT IDmateria FROM studentcourses
WHERE IDestudiante='".$_GET['id']."'") or die ('no se pudo');
$resulting = mysql_query ("SELECT IDmateria FROM programcourses
WHERE IDprograma = 35  AND IDmateria !=('$a')");

maybe this will help you
0
 
jkna_gunnCommented:
no i dont seem to get it :(

the query #2 does not make sense as you are passing in a resultset object as the value for IDmateria.

at the very least it should be
$row = mysql_fetch_array($a);
$idmateria = $row["IDmateria"];

but if you did that then it would only look at the first IDmateria.

you would ahve to write the query so that it ignored all the returned IDmaterias

..... WHERE IDprograma=35 AND IDmateria != ('$id1') AND IDmateria != ('$id2') AND ... and so on.
0
 
kephillipsAuthor Commented:
jkna_gunn,
You're right. That is what is hapenning, and it would be rediculous to "IDmateria != ('$id1') AND IDmateria != ('$id2') AND ... and so on" as there are more than 150 courses.
I know there is  a better way, but how?(at last we're on the same page).
0
 
skullnobrainsCommented:
not sure i get the db structure

SELECT programcourse.courseid                                                         /*courses in program*/
FROM student                                                                                    /*table 1 student*/
INNER JOIN programcourses                                                               /*table 2 programs*/
ON student.programid=programcourses.programid                               /*courses of student's program*/
LEFT JOIN studentcourses                                                                   /*join with taken courses*/
ON programcourses.courseid=studentcourses.courseid                         /*display matches, outer*/
where studentcourse.courseid is null and student.id=$id;                      /*only if course has not been taken, choose student*/

or at least something very similar will do the trick as i'm not sure of the exact table structures.
if you remove "and student.id=$id", you will display all the missing courses from every student which you can group either by student or by course for easy output

the left join will output null values for the second table if a match of the first table is not found so u just add the proper 'is null' in the where clause and u can pull all the values of table 1 which are not held in the second.

note that the optimisation is not that good and there could be a few issues if the number of courses is really huge.
in this case i would suggest that u
- pull out all the courses the student has taken and store them in an array (QUERY 1)
- proceed through all the courses that are in the corresponding program for display, (QUERY 2)
- stick at the beginning of the loop something such as
if($ARRAY_STORED[$COURSEID])continue;// if the course has already been taken
i assume the table has been constructed using loop(whatever)$ARRAY_STORED[$COURSEID]=1;
this will be fast even with a lot of courses.

u need to use inner join whenever possible in both options (low memory use for mysql, speed)
u had better use IDs in both cases.

hope either will help.
0
 
jkna_gunnCommented:
yeah thats why i thought my first bit of code should have done the trick.

the code i gave you why not try printing out the two arrays

print_r($courses1);
print_r($courses2);
0
 
skullnobrainsCommented:
sorry my sol1 is roughly the same as snoyes's.
had not been on this thread for a long time...
it definitely should work and will let u possibilities to display the same for all the users through a single query.

if u have good reasons for the second, try  (modified from above)

Your code seems to be the right idea, I'm just not REAL sure how to apply it to my situation.
THESE ARE THE QUERIES THAT HAVE TO BE MADE...
//here is query for #1 PULL ALL THE COURSES THE STUDENT HAS TAKEN
$result = mysql_query ("
SELECT IDmateria  FROM student
INNER JOIN programcourses ON student.IDprograma=programcourses.IDprograma
WHERE IDestudiante='".$_GET['id']."'");

//build the array
$TAKEN=array();
while($line=mysql_fetch_row($result))$TAKEN[$line[0]]=1;

//And query for #2 PULL ALL THE COURSES FROM HIS PROGRAM
$result = mysql_query ("
SELECT IDmateria FROM student
INNER JOIN studentcourses ON studentcourses.IDprograma=student.IDprograma
WHERE IDestudiante='".$_GET['id']."'");

while($line=mysql_fetch_row($result)){if($TAKEN[$line[0]])continue; //CONTINUE IF THE COURSE HAS BEEN TAKEN
   //....
   //any script for display
   echo $line[0].',';
}
0
 
kephillipsAuthor Commented:
no go my friends.

maybe this will help
TABLES DEFINITIONS:

PROGRAMCOURSES contains two fields; IDprograma, IDmateria
STUDENTS contains IDestudiante and IDprograma
STUDENTCOURSES contains IDestudiante, IDprograma and IDmateria

to find out courses a student is tied to, querying STUDENTCOURSES is sufficient (where IDestudiante='".$_GET['id']."'"

To find out all the courses that need to be taken within a given program just query PROGRAMCOURSES where IDprograma= '$programid'

Now, to find out what courses lack to be taken????????
0
 
kephillipsAuthor Commented:
no go my friends.

maybe this will help
TABLES DEFINITIONS:

PROGRAMCOURSES contains two fields; IDprograma, IDmateria
STUDENTS contains IDestudiante and IDprograma
STUDENTCOURSES contains IDestudiante, IDprograma and IDmateria

to find out courses a student has taken, querying STUDENTCOURSES is sufficient (where IDestudiante='".$_GET['id']."'"

To find out all the courses that need to be taken within a given program just query PROGRAMCOURSES where IDprograma= '$programid'

Now, to find out what courses lack to be taken????????
0
 
skullnobrainsCommented:
STUDENTCOURSES contains IDestudiante, IDprograma and IDmateria
i assume id_programa is a typo error. if not please explain why it is here.

-------------------------------------------------------------

can u try the solutions we gave you ?

TESTED QUERY

requĂȘte SQL : [Modifier]
SELECT bateau.id_bateau,croisiere.id_croisiere,client.id_client /*courses in program*/
FROM
bateau /*table 1 student*/
INNER JOIN croisiere /*table 2 programs*/
ON bateau.id_bateau=croisiere.id_bateau /*courses of student's program*/
LEFT JOIN client /*join with taken courses*/
ON client.id_client=croisiere.id_croisiere /*display matches, outer*/
where client.id_client is null and (1 or bateau.id_bateau=1) order by id_bateau LIMIT 0, 30  

does work and outputs all the cruises of each specific boat which have no match in clients table
(nb this query is nonsense but it is a simple test.)

i only changed field names of the query i gave u above (thank snoyes :) does work indeed.

did you try it out ? what does it output ?

"no go" does not help much when we don't even know what does not go nor why nor the error message if one....

the second solution should work as well.
all the necessary explainations are in the previous posts.
try them out.
0
 
kephillipsAuthor Commented:
Ok.
plugged in  jkna_gunn's code in from the top. here's the error:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource
And when I use
print_r($courses1);
print_r($courses2);
I get
Array ( ) Array ( )
Skullnobrains, your code gives me the same error (except for the Array()).
0
 
kephillipsAuthor Commented:
here's what I have:
$courses1 = array();
$courses2 = array();
$query1 = mysql_query ("SELECT programcourses.IDmateria FROM programcourses WHERE programcourses.IDprograma = ('$program')");

$query2= mysql_query("SELECT studentcourses.IDmateria FROM studentcourses WHERE studentcourses.IDestudiante='".$_GET['id']."')");

$result = mysql_query($query1);
while ($row = mysql_fetch_array($result)) {
    array_push($courses1,$row[0]);
}

$result = mysql_query($query2);
while ($row = mysql_fetch_array($result)) {
    array_push($courses2,$row[0]);

}
print_r($courses1);
print_r($courses2);
$finalcourses = array_diff($courses1,$courses2);

// well something like that anyway!
mysql_close;


?>
0
 
jkna_gunnCommented:
ok if your getting a fetch array error then your query must have an error in it somewhere.
in this case i would print out the query like so :

print $query1;

then you should try and run that exact query in mysql directly and see if it works.
it will tell you the error if there are any.
0
 
skullnobrainsCommented:
actually yes something similar indeed.

if you just used error handlings, you would have noticed that you first query is wrong : parenthesis around the variable value.

use something like
if(!mysql_query('select ...'))die ('wrong query<br>'.mysql_error());

all the proper codes and examples you will find anywhere are constructed in that way.

NOTE : i'd highly recommend to use the single query method which is much easier to manipulate and more powerfull.
you will get that query right if u use mysql_error for debugging in not more than 2 mn.
0
 
kephillipsAuthor Commented:
ok. Nothing is working. I have tried and tried with the queries you have suggested but nothing works.
Does anybody know how I can upgrade mysql from 3.23 to 4.0. There are several downloads available and I have tried but can't seem to get it to work. If there is an RPM out there it would be nice. I have downloaded one, but halfway through loading it, it quits....? Help please.
0
 
skullnobrainsCommented:
to upgrade, just download it from mysql.com, using a client that supports resuming, file by file.

if you can't give a proper error message instead of this, nobody can help.

use mysql_error() !!! you can't debug what you can't see !

if you can't get this to work using the version you have, upgrading will not help you much.

btw, my version is 3.23.49 so the tested query should work for you if you type it in properly.

the array() stuff obviously mean that the querys are wrong.

if your query is likely to give no results, you must use
if(false===($thing=mysql_query()))die(mysql_error());
my advice is to create a custom query function for this sole purpoise, and never use mysql_query again.
0
 
jkna_gunnCommented:
$query1 = mysql_query ("SELECT programcourses.IDmateria FROM programcourses WHERE programcourses.IDprograma = ('$program')");

$query2= mysql_query("SELECT studentcourses.IDmateria FROM studentcourses WHERE studentcourses.IDestudiante='".$_GET['id']."')");

those queries dont look right!!

$query1 = "select programcourses.IDmateria FROM programcourses WHERE programcourses.IDprograma='$program'":

$query2 = "select studentcourses.IDmateria FROM studentcourses WHERE studentcourses.IDestudiante='" . $_GET['id'] . "'";

looks better now

0
 
skullnobrainsCommented:
yup, and if you look up 3 posts you will notice the echo ;)
anyway, it's a shame that you have to dig through this as mysql_error would have made it visible from the start.
0
 
kephillipsAuthor Commented:
skullnobrains,
You're totally right. I should have added in mysql_error() like you say- I only learned about it now...the syntax that is.
OK> I now have mysql 4 installed. I can do sub queries! Thanks
0
 
kephillipsAuthor Commented:
OK.
Many thanks for the help thus far.
Here's what I have come up with.
$strQuery = "SELECT IDmateria FROM courses
WHERE IDmateria NOT IN  (SELECT IDmateria FROM studentcourses WHERE IDestudiante='" . $_GET['id'] . "')";
//Execute the query and die if any error
$result = mysql_query($strQuery) or die("Something went wrong with this query : " . $strQuery . " \n<br/>Error : " . mysql_error());
while($rs = mysql_fetch_array($result))
{
   echo $rs['IDmateria'] . '<br>';
}

WITH THIS ERROR:
Something went wrong with this query : SELECT IDmateria FROM courses WHERE IDmateria NOT IN (SELECT IDmateria FROM studentcourses WHERE IDestudiante='10151')
Error : You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT IDmateria FROM studentcourses WHERE IDestudiante='10151'

Any ideas? Thanks.
0
 
skullnobrainsCommented:
the overall syntax seems good, but you have the exact message that is outputed when running on an older version.
the verion supporting subqueries is 4.1

it is possible, that you get this error when something is wrong with the inner query.
try to execute the subquery as a single query and see if it is allright.
note : this error usually means there is something about the query structure that mysql can't resolve.

the NOT IN syntax is a standard sql feature.
i'm not 100% sure wether it is supported or not (though i guess yes)
you must check this from mysql.com which happens to be down about all the time these days.

if you can give a restrict access to your mysql (ie a read access on a non-empty db for simple testing),
you'll easily get someone to have a look. (i'll have one as soon as i get it. can't do a thing on my 3.23)

if you try the single-query method, it will be faster to proceed (could be huge difference in some cases),
and easier to debug.

skull

ps: don't worry about the mysql_error() thing. that was just about getting folks to use it !
0
 
kephillipsAuthor Commented:
OK.
FINALLY GOT IT!!!!!!!!!!
The answer to my problem was upgrading to mysql 4.1 like was suggested before and then using a subquery:
("SELECT programcourses.IDmateria, courses.Materia, courses.Creditos
FROM programcourses, courses WHERE programcourses.IDmateria NOT IN (SELECT studentcourses.IDmateria FROM studentcourses WHERE studentcourses.IDestudiante='$id')
AND programcourses.IDprograma='$program' AND courses.IDmateria=programcourses.IDmateria ORDER BY Materia ASC");

I upgraded from mysql 3.23 to 4 then from 4 to 4.1 like was recomended.
It was pretty straight forward, just downloaded all the RPM's, stopped mysql service and  installed:
1.>rpm -Uvh nodeps MySQL- server-4.1.1-0.i386.rpm
2.>rpm -Uvh MySQL-shared-compat-4.1.1-0.i386.rpm
3.>rpm -Uvh MySQL-client-4.1.1-0.i386.rpm


Many thanks to all those who helped. Most points goes to Skullnobrains for walking me through and jkna_gunn. Thanks.
0
 
skullnobrainsCommented:
cool if u managed.

once more, if you don't link your queries using joins when you can, you are going to very highly overload your server.

u may use EXPLAIN SELECT... (preferably in myadmin) to retrieve information on the query processing.

/* adding a simple join */
SELECT programcourses.IDmateria, courses.Materia, courses.Creditos
FROM programcourses
INNER JOIN courses
ON courses.IDmateria=programcourses.IDmateria
WHERE programcourses.IDprograma='$program'
AND programcourses.IDmateria NOT IN (SELECT studentcourses.IDmateria FROM studentcourses WHERE studentcourses.IDestudiante='$id')
ORDER BY Materia ASC

you must understand that although it seems the same
(and could theorically be if the queries where always perfectly optimised)

your query means that mysql will
FIRST create an enormous table which is a cartesian product of the 2 first
- number of rows is numrows(courses) * numrows (programcourses).
- number of cols is the sum of the number of cols of the above if no cols are specified (in your case just 3)
THEN check this table against the where clause.
i heard the where clauses where handled in turn so the sub-query would be executed for each line of the big table.
i guess this part is properly optimised by mysql but EXPLAIN select will give you the answer.

using the join syntax alone is definitely the fastest.
/* only joins */
SELECT programcourses.IDmateria, courses.Materia, courses.Creditos
FROM programcourses
INNER JOIN courses
ON courses.IDmateria=programcourses.IDmateria
LEFT JOIN studentcourses
ON studentcourses.IDmateria=programcourses.IDmateria and studentcourses.IDestudiante='$id'
WHERE programcourses.IDprograma='$program'
AND programcourses.IDmateria is null
ORDER BY Materia ASC

should be alright now that i have most of the db structure.
you can play with joins at will to stick them in different orders depending on the EXPLAIN select results.
mysql often parses joins backwards (could be corrected in the query as well)

there is still something crooked : there is no reason why you should input the IDprograma
the relation student-program should be present somewhere in the db.
could be an independant table, in student with the courses, in courses adding a students col...(bad way)
i'd recommend the independant table if this is not set somewhere yet,
and second store in student (just start numbering courses at 100, anything lower is a prog)
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 16
  • 8
  • 7
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now