PHP Select Count and Left Join where

Question

Im not sure if it´s possible to combine SELECT COUNT + Left Join on this way but it would be very helpful for me.

This is my code whitout Left Join attempt:

$pp = mysql_result(mysql_query("SELECT COUNT(*) FROM app_tgp WHERE idt='$zaznam[id]' AND ( partic='' OR partic='y' OR (partic=='n' AND $zaznam[logoff]=='n'))"), 0);

I have to get COUNT of players from the specific training (WHERE idt) where partic = '' OR partic = 'y' OR (partic = 'n' AND TRAINING[logoff] = 'n')

Problem is that TRAINING[logoff] is value from different table. Am I able to make Left Join to get this value into Select Count to Where clause based on app_tgp.idt = app_training.id?

Thanks for any answer!

Answer 1

The problem is here $zaznam[logoff]=='n'. There must be any column name where you have written $zaznam[logoff]. For Applying Left Join you should do something like this:

SELECT COUNT(*) FROM app_tgp t1 LEFT JOIN app_training t2 on t1.idt = t2.id WHERE t1.idt='$zaznam[id]' AND ( t1.partic='' OR t1.partic='y' OR (t1.partic=='n' AND t2.your_column_name_here=='n'))

Answer 2

You must do LEFT JOIN claue. In that case, Your training table must have any column that means any information from columns app_tgp table. If so, you can build query:

$pp = mysql_result(mysql_query("SELECT COUNT(*) FROM app_tgp LEFT JOIN different_table ON app_tgp.column = different_table.column WHERE app_tgp.idt='$zaznam[id]' AND ( app_tgp.partic='' OR app_tgp.partic='y' OR (app_tgp.partic=='n' AND different_table.training[logoff] = 'n'))"), 0);

There in JOIN claus, app_tgp.column = different_table.column - columns must have indentical information