SQL left join (I think)

[gerkintrigg]

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]<?php
include $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]

[HeyRay2]

Sounds like a job for a subquery:

[code]
<?php

case "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]

[Barand]

1 ) LEFT JOIN will be quicker than a subquery
2 ) SQL uses "AND" , not "&&"

This will pull member details for those members with no orders
[code]SELECT m.* FROM member m  LEFT JOIN order o
ON o.user_id = m.userid
WHERE o.user_id IS NULL
[/code]

[HeyRay2]

Nice one, [b]Barand[/b].

I concede. I recommend his method in this case.

;)

[gerkintrigg]

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 o
ON o.user_id = m.userid
WHERE o.user_id IS NULL
)";
    $title_who='Members who\'ve never bought anything';
  break;[/code]

Thanks in advance.