Jump to content

Archived

This topic is now archived and is closed to further replies.

gerkintrigg

SQL left join (I think)

Recommended Posts

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]

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
Nice one, [b]Barand[/b].

I concede. I recommend his method in this case.

;)

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.