Member Avatar for facarroll

I have the following code which is intended to supply the family and given names of people who are in the users table but not in the quiz table. Can I even do this, and this throws an error anyway?

$query = mysql_query("SELECT quiz.userGroup AS q_userGroup, quiz.userDate, users.userGroup AS u_userGroup, DATE_FORMAT(quiz.addDate,'%b %e, %Y'), quiz.passState, users.family, users.given FROM quiz, users WHERE quiz.userIdRec = users.id AND users.userGroup IN('$g1', '$g2', '$g3', '$g4', '$g5') AND quiz.managerId='".$userid."' AND quiz.quizTitle ='".$_SESSION['squiz']."' AND (SELECT users.family AS u_family, users.given AS u_given FROM users LEFT JOIN quiz ON users.id = quiz.userIdRec WHERE quiz.userIdRec IS NULL AND users.userGroup IN('$g1', '$g2', '$g3', '$g4', '$g5')) AND quiz.userDate BETWEEN '".$start_raw."' AND '".$end_raw."' ORDER BY users.egroup, users.family, users.given, quiz.userDate");

The error lies in the section of code as follows "AND (SELECT users.family.....'$g2', '$g3', '$g4', '$g5'))"

Some help with the code would be much appreciated.

  • 2 Contributors
  • 4 Replies
  • 123 Views
  • 3 Hours Discussion Span
  • Latest Post Latest Post by urtrivedi
Member Avatar for urtrivedi

AND must follow boOlean result. you are giving subquery to AND

tell us :
table strutrute
sample data
sample expected output

Member Avatar for facarroll

If I do this it works fine as far as it goes.

$query = mysql_query("SELECT quiz.userGroup AS q_userGroup, quiz.userDate, users.userGroup AS u_userGroup, DATE_FORMAT(quiz.addDate,'%b %e, %Y'), quiz.passState, users.family, users.given FROM quiz, users WHERE quiz.userIdRec = users.id AND users.userGroup IN('$g1', '$g2', '$g3', '$g4', '$g5', '$g6', '$g7') AND quiz.managerId='".$userid."' AND quiz.quizTitle ='".$_SESSION['squiz']."' AND quiz.userDate BETWEEN '".$start_raw."' AND '".$end_raw."' ORDER BY users.egroup, users.family, users.given, quiz.userDate");

The database contains a user table and a quiz table. The above query will inform me which students have completed certain tests and shows their results over a nominated time period. The test results are held in the table quiz. So far so good.

However, if a student is lazy and does not attempt any quizzes, there are no entries for that student in the quiz table, so I want the query to tell me which names in the users table do not appear in the quiz table. The variables $g1, $g2 etc refer to student group names.
Does this make sense?

Member Avatar for facarroll

How do I make the code that I post format correctly for human readers?

Member Avatar for urtrivedi 
select * from users where id not in (select userIdRec from quiz)
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.