I was up until 6:00am yesterday morning working on a project for Aerva. I wrote the longest SQL query I've ever written. Here's what it looks like:
[sql]
SELECT ac_users.*, ac_users2customers.*, ac_users2users.*,
customers.*, users.user_id, users.username
FROM ac_users, ac_users2customers, ac_users2users, customers, users
WHERE ac_users2customers.customer_id = '$this->custid'
AND ac_users2users.user_id = '$this->userid'
AND ac_users2customers.ac_user_id = ac_users2users.ac_user_id
AND customers.customer_id = ac_users2customers.customer_id
AND users.user_id = ac_users2users.user_id
AND customers.login_id = users.username
OR ac_users2users.user_id = '$this->userid'
AND ac_users2users.ac_user_id = ac_users.ac_user_id
AND ac_users2customers.ac_user_id = ac_users.ac_user_id
AND customers.customer_id = ac_users2customers.customer_id
AND users.user_id = ac_users2users.user_id
OR ac_users2customers.customer_id = '$this->custid'
AND customers.customer_id = '$this->custid'
AND ac_users2users.ac_user_id = ac_users2customers.ac_user_id
AND ac_users.ac_user_id = ac_users2users.ac_user_id
AND users.user_id = ac_users2users.user_id
AND users.username = customers.login_id
ORDER BY ac_users.first_name;
[/sql]
Those of you who are familiar with SQL might notice some of this is redundant and unnecessary. I know. Right now I don't care; I just need it to work. What does this SQL query do? It selects all users from the ac_users table, with a few conditions. I will attempt (at least partly) to explain what it does in English:
ac_users should only be seen by users who have the ac_user assigned to them (in ac_users2users) or if the company the user belongs to has a login_id that matches the username of another user who has been assigned to the ac_user (again, in ac_users2users).