Jump to content


Photo

SQL left join (I think)


  • Please log in to reply
4 replies to this topic

#1 gerkintrigg

gerkintrigg
  • Members
  • PipPipPip
  • Magician
  • 828 posts
  • LocationBristol, UK
  • Age:37

Posted 18 August 2006 - 03:10 PM

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:
<?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;
}
?>

Neil Trigger - http://www.ghostlypublishing.co.uk - Ghostly Publishing - Children's Fantasy Books

#2 HeyRay2

HeyRay2
  • Members
  • PipPipPip
  • Advanced Member
  • 223 posts

Posted 18 August 2006 - 05:04 PM

Sounds like a job for a subquery:

<?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;
   
?>


#3 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 18 August 2006 - 08:04 PM

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
SELECT m.* FROM member m  LEFT JOIN order o
ON o.user_id = m.userid
WHERE o.user_id IS NULL

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#4 HeyRay2

HeyRay2
  • Members
  • PipPipPip
  • Advanced Member
  • 223 posts

Posted 18 August 2006 - 08:09 PM

Nice one, Barand.

I concede. I recommend his method in this case.

;)

#5 gerkintrigg

gerkintrigg
  • Members
  • PipPipPip
  • Magician
  • 828 posts
  • LocationBristol, UK
  • Age:37

Posted 26 August 2006 - 06:15 PM

Barand,
I thought && was interchangable with AND..?
Can the left join be used with the rest of the select query like this:?
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;

Thanks in advance.
Neil Trigger - http://www.ghostlypublishing.co.uk - Ghostly Publishing - Children's Fantasy Books




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users