I have 3 tables one is user second is user_packages and the last one is packages. user table contains some information like name, email etc and user_packages table contains userID as a foreign key packageID and status of each package. Only one package can be activated at one time. packages table contains all the packages. Now i want to get the record in such a way that if I log in to the system I can see only one user which does not have matching id of the current session open. After getting that information the result will be stored in donor table. In this way there will be a relationship between users who will pay whom. I have the following query which is working very fine getting record of other person.
$this -> db -> query("Select * from user JOIN user_packages ON
user.id!='$id' AND user.canRecieve!='1' AND user.active='1' AND
user.id=user_packages.userID AND user_packages.status='1' ORDER BY RAND(),
user.id LIMIT 1");
Now what my main concern is that only those users should be visible to me which also has same packgeID as of the current user including above conditions. Let say I have packageID of 5 I want to select the person/user which also belongs to that packageID of 5.
So here is the answer after tried of many queries finally got the query which gives the correct output
$this -> db -> query("Select user.id, user.name, user.canRecieve,
user.active from (Select user.id, canRecieve, status, packageID, active from
user JOIN user_packages ON user.id='$id' AND user.canRecieve!='1' AND
user.active='1' AND user_packages.userID=user.id AND user_packages.status='1')
AS DONOR JOIN user ON user.id!='$id' AND user.canRecieve!='1' AND
user.active='1' JOIN user_packages ON user.id=user_packages.userID AND
user_packages.status='1' AND user_packages.packageID=DONOR.packageID ORDER BY
RAND(), DONOR.id LIMIT 1");