Join tables help required

[bigfatwallet]

Can someone please help with the following:

 

Table url:

urlid

url

user

credits

 

Table urlhistory:

urlid

user

date

 

What I would like to do is return a random urlid to the browser based on the following:

 

1) credits > 0

2) user (in table url) is not the user($id)

3) The table urlhistory does not have a record for that urlid, that user and todays date.

 

 

I thought I would be able to join the tables,

(limit by 1 and order random)

but have never used a join in mysql before.

 

Also if there are no more remaining urlids to choose I would need to show default

text saying no more urls available.

 

Many thanks in advance

[zq29]

This is an SQL question, and as such, will be moved to our SQL forum. I'm going to take a stab at answering it for you though, not sure if this is right, but will give you something to start with:

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] u.urlid FROM url AS u, urlhistory AS h WHERE u.credits > 0 AND u.user != '$id' AND h.date != '$today' [!--sql2--][/div][!--sql3--]

You will need to provide $today with todays date with some php, I cant remeber the SQL to insert todays date, without the time, it might just be date()...

[ryanlwh]

need to join the tables together as well, and order by rand()

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] u.urlid FROM url AS u LEFT JOIN urlhistory AS h ON u.urlid=h.urlid WHERE u.credits > 0 AND u.user != '$id' AND h.urlid IS NULL AND LEFT(h.date,10) != CURDATE() AND h.user!='$id' ORDER BY RAND() LIMIT 1 [!--sql2--][/div][!--sql3--]