How to compare to variables from a select statement?

[PatPHP]

I have two select statements and I want to give back the record if awardsequal = tptotal. How can I do that?

The first sql which I wrote here doesn't work. It is just an example how I would like to have everything together. I hope you can understand.

 

SELECT * 
FROM training t
JOIN member m ON m.id =  '480'
WHERE t.id =  '30'
AND ( EXISTS (@awardsequal= @tptotal)))


SELECT COUNT(*) AS awardsequal
FROM member_award ma
JOIN training_prerequisite tp
ON ma.awardid = tp.AwardID 
WHERE ma.memberid = 480 AND tp.trainingid = 31 AND tp.GroupNum=0

SELECT COUNT(*) AS tptotal 
FROM training_prerequisite tp 
WHERE tp.trainingid = 31 AND tp.GroupNum=0

[fenway]

You sure you can't combine that logic? Tell us more.

[PatPHP]

At the moment I run all the statements separate and do the comparing in php. Works great so far but I'm sure it would not be the best solution. I just don't know enough about mysql yet. Anyway I try to explain in more detail what I try to achieve here and I hope I still will get a better solution.

 

A member has a certain awards and if he want's to attend a training he needs to meet this awards. Here a simplified example:

 

To be able to sign up for the training you need to have this awards:

Swimming

Dancing

Singing

 

User has:

Swimming

Dancing

Surfing

 

So the user would not be able to sign up because all his awards don't meet the training requirements. So my question is how can I make this comparison? If you look at my sql statement above it would work if this way would exist in mysql. I just want the mysql give a string in return when the user is able to sign up.

 

I know it's a little bit complicated to explain over the web.

[fenway]

Basically, you'll need to make sure that the user has all of the required awards... mysql doesn't handle "lists" as you would expect.  So you can either compare arrays in php, or run a more complex DB query that will tell you exactly.

[PatPHP]

so how can i run a more complex query that will tell me exactly? don't really understand what you mean. an example would be great. thx

[fenway]

Well, you can do it the "lazy" way, and simply use IN() with HAVING/COUNT().