Selecting Random Rows From MySQL DB With Following Conditions

Hey,


I have a database with 3 tables called "userProfiles", "waiting" , "completed".


* userProfiles contains information on users, each user having their own unique ID (INT).
* waiting contains 2 fields, "giver" and "receiver".
* completed contains 2 fields, "usera" and "userb".


This is basically a system where people do something for each other. For example:

===

* User A gives a point to user B.
* A record is inserted into the "waiting" table, with user A's ID (from userProfiles) in the "giver" field and user B's ID in the "receiver" field.
* Once user B gives a point to user A, the record mentioned above is deleted and a new record is inserted into the "completed" table with their IDs in the 2 fields.

===

What I want to do is select 5 random rows from the "userProfiles" table. However the ID selected cannot be:

1. Their own ID, which is given already set as $ownID
2. In the "receiver" field of "waiting" where "giver" = $ownID (i.e. user has already given that userID a point)
3. In the "completed" table where the other row field contains $ownID (i.e. they have already exchanged points.

How would I do this in an efficient way?

I'm not sure if I made this clear so I'll give you another example with tables shown:

TABLE userProfiles

...id...|....other stuff which don't matter
==============================
....1...|....blah blah blah.....
....2...|....blah blah blah.....
....3...|....blah blah blah.....
....4...|....blah blah blah.....
....5...|....blah blah blah.....
....6...|....blah blah blah.....
....7...|....blah blah blah.....
....8...|....blah blah blah.....
....9...|....blah blah blah.....
...10...|....blah blah blah.....

TABLE waiting

...giver...|...receiver...
=================
......2.....|.......1........
......2.....|.......5........
......6.....|.......2........

TABLE completed

...userA...|...userB...
================
......2......|......3......
......4......|......2......


Say that we're talking about a user who is logged on is user 2. Now the script should select 5 random ids from "userProfile" so he can give them points. However the following ids cannot be selected and for the following reasons:

1 & 5 as he has already given them a point as shown in "waiting".
3 & 4 as he has already exchanged points with them.

How would I do this? It has to be quite efficient coz I will have quite a lot of records...


Thanks!!!

Hodge

 

First, eliminate the "Waiting" table and add a 3rd column called Completed to the Completed table. The Completed column is either true or false/null. This will save you space and also simplifys your data and queries.

Here's the SQL
SELECT * FROM userProfiles U
WHERE U.id<>$ownID
AND U.id NOT IN
(
SELECT userA FROM Completed C
WHERE C.userB=$ownID
UNION
SELECT userB FROM Completed C
WHERE C.userA=$ownID
)
ORDER BY RAND() LIMIT 5

 

Hey thanks for the reply.

Just a question, what is all this "U" and "C" mean? Sorry I'm still relatively new to MySQL and I've never seen that before.

Is it meant to be a short hand notation or something? As in you're saying userProfiles can be called U and Completed can be called C?

Also what does the <> mean??

Sorry I'm so confused!

 

Yes, they are aliases.

<> means not equal.

 

Ah rite, sorry about that... I didn't know there's another notation for not equal other than !=

Anyway thanks for your help - it worked

 

Thanks for the Rep. How much Rep points does it take to get to 2 greens?

 

100