mySQL SELECT two tables

[tazanet]

I have two tables members and ads, members table has auto_increment 'ID' , 'CREDIT' and some members information.
ads table has 'AID' auto_increment and 'ID' to track who's link is that

Basically 1 member can have up to 5 link ads.

I want to get from query LIMIT 5 ads who has enough credits and not from same members ID

I'm using php have 4000 members row and growing

 

[swampBoogie]

What is enough credit?

select membername,max(aid)
  from members m inner join ads a
    on m.id = a.id
 where credit > 4711
 group by membername
 limit 5

 

[tazanet]

Thank you so much it works, but when I do rand() it always gives me a first 'AID' from the group how can I randomize it like from 1 member get always different ads LIMIT 5

 

[tazanet]

members table
---------------------------------
ID   CREDIT   fname   lname
1    24      jon     smith
2    50      mark     tween
3    23      sam     jonson
4    61      dave    derian
5    28      gio     fery
6    95      anna    lopez

ads table
--------------------------------
AID   ID   ACTIVE
1     1      Y
2     1      Y
3     1      Y
4     2      Y
5     2      N
6     2      Y
7     3      Y
8     3      Y
9     3      Y
10    4      N
11    4      Y
12    4      Y



members can have few ads few rows in ads table. I want to get LIMIT 5 ads from different member randomly every time different ad. Lets say if 1 member has 3 ads get 1 ad randomly from each member LIMIT 5 times

i'm using php4 mysql3.23


this one works but problem is it gives me a first ad in group always, it doesn't randomize

SELECT * FROM members m inner join ads a ON m.ID=a.ID WHERE CRED>1 GROUP BY m.ID ORDER BY RAND() LIMIT 5