modifying mysql query to refine results

[jayke00]

Hey,

I've been stuck on this problem for several days now and have yet to find a solution. To

best describe my problem I created a visual representation :)

http://www.acllc.com/hosting/test.html

The first item on the page is a mysql table named 'user_info'. This table contains data

about different users that have attended different classes offerend by the company I work

for.

The second item is a mysql table named 'class_info'. This table has a row for each class

that a person has attended: beginner, advanced, and expert. Each name is matched up with one

class, so any one persons name can appear up to three times.

This purpose of this database is to allow an administrator to view all the people that have

attended company classes. What the administrator will see is similar to what can be found in

item three: 'show_db.php'.

Initially, 'show_db.php' will show everything that is recorded in table 'user_info'. But at

the bottom of 'show_db.php' a form will allow the administrator to refine the information by

selecting to show only people who have/have not attended one of the three classes. This is

no problem since I can use the submitted data to form a query such if the submitted data

equals:

"Show people who have attended the beginner class"


The query would be:

$query =
"
SELECT user_info.user_name, user_info.email, user_info.company, class_info.user_name
FROM user_info, class_info
WHERE (class_info.class_name = 'beginner')
and (class_info.user_name = user_info.user_name)
";

The result of that query will show all the people who have attended the 'beginner' class.

What I'm looking for is a way to be able to submit the form again and have the results

refined from the first submission. An example would be after the first submission showing

who attended the 'beginner' class, having a second showing who did not attend the 'advanced'

class. So the final results would show everyone who attended the 'begginer' class, but not

the 'advanced'.

Im looking for a way to search only the results of the first output.

Please let me know if anything is unclear or if you have a solution.
Thanks :)

[jayke00]

Im realizing that this is more of a sql dilema. Im needing to query something like this:

SELECT user_info.user_name, user_info.email, user_info.company, class_info.user_name
FROM user_info, class_info
WHERE (class_info.class_name = 'beginner')
and (class_info.class_name = 'advanced')
and (class_info.class_name != 'expert')
and (class_info.user_name = user_info.user_name)

Im wanting to show users who have attended beginner and advanced classes, but not expert. The above query won't do that, but I need something that will. Any ideas?

Thanks :)

[jayke00]

To show users who have attended beginner and advanced classes, but not expert, I used:

select user_info.user_name
, user_info.email
, user_info.company
from user_info
inner
join class_info
on user_info.user_name = class_info.user_name
group
by user_info.user_name
, user_info.email
, user_info.company
having sum(
case when class_info.class_name
in ('advanced','beginner')
then 1 else 0 end
) = 2
and sum(
case when class_info.class_name
in ('expert')
then 1 else 0 end
) = 0