Jump to content


Photo

JOIN Statements - need a bit of help


  • Please log in to reply
1 reply to this topic

#1 twikkistep

twikkistep
  • New Members
  • Pip
  • Newbie
  • 1 posts

Posted 03 August 2006 - 08:01 AM

I'm trying to get a grip on JOIN statements -- if I have a table with contacts, and another table with communication entries connected to the first table with contactid, how can I look up this: "select 5 random contacts that have more than 3 communication entries"? Thanks for helping a newbie out.

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 03 August 2006 - 02:20 PM

Give this a try (UNTESTED):

SELECT ct.*, cm.contactid, COUNT(*) AS entries FROM communications AS cm LEFT JOIN contacts AS ct ON( ct.id = cm.contactid ) GROUP BY cm.contactid HAVING entries > 3 ORDER BY RAND() LIMIT 5

Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users