gerkintrigg Posted August 18, 2006 Share Posted August 18, 2006 Hi all...I'm writing a newsletter script and am trying to work out how to select details from my members database folder WHERE member.userid does not exist in the orders table. In other words members who have never ordered something.I have done left joins before, but am a bit rusty, and have never done them as part of a normal table join.Mysqlfreaks.com/forums was offline... sorry.Please help.At the moment my code looks like this:[code]<?phpinclude $root.'includes/db.php';switch ($_POST['who']) {case "6months": $who="&&(orders.timestamp>=".(time()-2678400).")&&(orders.user_id=member.userid)"; $title_who='Customers From The Last 6 Months'; break;case "never":// this needs changing!! $who="&&()"; $title_who='Members who\'ve never bought anything'; break; case "uk": $who="&&((Address LIKE '%United Kingdom%')||((Address LIKE '%UK%')))"; $title_who='People From The UK'; break; case "foreign": $who="&&(!(member.Address LIKE '%United Kingdom%')&&!(member.Address LIKE '%UK%'))"; $title_who='People From Outside The UK'; break; case "everyone": $who=""; $title_who=' Everyone'; break;}$sql=mysql_query("SELECT member.Email FROM member, orders WHERE (member.Subscriber='Subscriber')".$who);while ($email_row = mysql_fetch_array($sql)){ if ($to !=''){$to = $to.','.$email_row['Email'];}else {$to = $email_row['Email'];}$people=$email_row['Email'].', '.$people;}?>[/code] Quote Link to comment Share on other sites More sharing options...
HeyRay2 Posted August 18, 2006 Share Posted August 18, 2006 Sounds like a job for a subquery:[code]<?phpcase "never": $who="&&(member.userid NOT IN (SELECT user_id FROM orders GROUP BY user_id))"; $title_who='Members who\'ve never bought anything'; break; ?>[/code] Quote Link to comment Share on other sites More sharing options...
Barand Posted August 18, 2006 Share Posted August 18, 2006 1 ) LEFT JOIN will be quicker than a subquery2 ) SQL uses "AND" , not "&&"This will pull member details for those members with no orders[code]SELECT m.* FROM member m LEFT JOIN order oON o.user_id = m.useridWHERE o.user_id IS NULL[/code] Quote Link to comment Share on other sites More sharing options...
HeyRay2 Posted August 18, 2006 Share Posted August 18, 2006 Nice one, [b]Barand[/b].I concede. I recommend his method in this case. ;) Quote Link to comment Share on other sites More sharing options...
gerkintrigg Posted August 26, 2006 Author Share Posted August 26, 2006 Barand,I thought && was interchangable with AND..?Can the left join be used with the rest of the select query like this:?[code]case "never": $who="AND(LEFT JOIN order oON o.user_id = m.useridWHERE o.user_id IS NULL)"; $title_who='Members who\'ve never bought anything'; break;[/code]Thanks in advance. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.