hi there ;)

i need a sql statement which selects all events from agendax events where the field "needfotografen" > "SELECT COUNT(*) FROM agendax_fotografen WHERE uid=".$xoopsUser->getVar('uid')

agendax_events.id = agendax_fotografen.event_id

any idea who to do that?

another problem: it is possible that there is no record in agendax_fotografen for an event. so inner join is impossible ?!?!

What you are looking for is a subselect, which works in Oracle and (I think) in MySQL 4.x, but not in older version of MySQL. For this reason, I do not recommend trying to do it that way.

Instead, you should first make the count query and store the count in a PHP variable, then use that var in your query against the events table. This has the nice side effect of dealing with the case where there are no records by returning 0.

This is far less complicated that the subselect and works everywhere.

Dennis

thanks for your idea but i did it totally different now. i added a field to xoops_agendax_events with the number of fotographs ;)