CODEWITHSUNDEEP
mysqlselectdistinct
Jeff Stone
Jeff Stone

Reputation: 319

SELECT DISTINCT, trying to pull from a join, need unique results

Two tables are in question; leadsand contactAttempts.

I'm trying to pull distinct leads that were contacted in 2012, with specific keywords from a text column on a separate table.

The problem is that I keep getting the same lead ids over and over, and the results are so massive that it is timing out and crashing the website.

I've tried multiple variations, including all in one sql statement. Separating them into 2 sql statements is my latest iteration at an attempt.

Select distinct is not currently working in the format I'm trying. id is the primary in both tables, leadID connects leads to contactAttempts:

<? $sql="SELECT * 
 FROM contactAttempts a
 JOIN leads l
 ON l.id = a.leadID  
 WHERE l.agentID = 2 
 AND l.leadType IN(0,2)
 AND a.timestamp BETWEEN '2012-01-01 00:00:00' AND '2012-12-31 23:59:59' 
 LIMIT 0,50";
$res=mysql_query($sql);
while($row=mysql_fetch_assoc($res)){
    $sql2="SELECT DISTINCT leadID FROM contactAttempts WHERE 
leadID='$row[id]' AND (contactAttempts.notes LIKE '%shown%' OR 
contactAttempts.notes LIKE '%showed%' OR contactAttempts.notes LIKE 
'%offer%' OR contactAttempts.notes LIKE '%inspection%' OR 
contactAttempts.notes LIKE '%appraisal%' OR contactAttempts.notes LIKE 
'%closing%' OR contactAttempts.notes LIKE '%drive%' OR 
contactAttempts.notes LIKE '%drove%' OR contactAttempts.notes LIKE '%car%' 
OR contactAttempts.notes LIKE '%preview%' OR contactAttempts.notes LIKE 
'%previewed%' OR contactAttempts.notes LIKE '%took pictures%') LIMIT 1";
    $res2=mysql_query($sql2);$x=0;
    while($row2=mysql_fetch_assoc($res2)){
        $x++;
        echo $x.' - '.$row2['leadID'];
        echo '<br />';
    }
} ?>

Here's a source example: table leads:

id - 100, bunch of other columns not useful to this script

id - 200, bunch of other columns not useful to this script

table contactAttempts:

id - 1, leadID - 100, notes - 'Showed house to customer, they liked it', timestamp - '2012-01-21 12:05:11'

id - 2, leadID - 100, notes - 'Showed house to customer again, they liked it', timestamp - '2012-02-21 12:05:11'

id - 3, leadID - 200, notes - 'Showed house to a different customer, they hated it', timestamp - '2012-01-21 12:05:11'

Right now, the results would be: 100,100,200. I need the results to be 100,200. The script needs to omit multiple occurrences of leadID 100.

Upvotes: 0

Views: 173

Answers (2)

Alex
Alex

Reputation: 17289

<? 
$sql="
 SELECT 
    l.id as id,
    a.id as attempt_id,
    a.leadID as leadID,
 FROM leads l
 INNER JOIN (
     SELECT id, leadID
     FROM contactAttempts 
     WHERE contactAttempts.notes REGEXP 'shown|showed|offer|inspection|appraisal|closing|drive|drove|car|preview|previewed|took pictures' 
     AND timestamp BETWEEN '2012-01-01 00:00:00' AND '2012-12-31 23:59:59'
    ) as a
 ON a.leadID = l.id
 WHERE l.agentID = 2 
   AND l.leadType IN(0,2)
 ORDER BY l.id, a.id
 ";
$res=mysql_query($sql);
$currLead = 0;
$x=0;
while($row=mysql_fetch_assoc($res)){
    if ($currLead != $row['leadID']){
        echo 'NEW DISTINCT LEAD = '.$row['leadID'].'<br />';
        $x=0;
    }
    $x++;
    echo $x.' of lead '.$row['leadID'].'  attempt '.$row['attempt_id'];
    echo '<br />';
    $currLead = $row['leadID'];
} ?>

Upvotes: 1

Strawberry
Strawberry

Reputation: 33935

Before I (or others) get to solving the actual problem; some observations on your first query.

First, that query is (I believe) logically identical to the following, which I find easier to read:

SELECT * 
  FROM contactAttempts a
  JOIN leads l
    ON l.id = a.leadID  
 WHERE l.agentID = 2 
   AND l.leadType IN(0,2)
   AND YEAR(a.timestamp) = 2012 
 LIMIT 0,50

Secondly, YEAR() prevents the use of index, so on larger datasets this will slow things down considerably. a.timestamp BETWEEN '2012-01-01 00:00:00' AND '2012-12-31 23:59:59' may look cumbersome but it will be much faster on indexed data.

Thirdly, LIMIT without ORDER BY is pretty much meaningless.

Also, and equally, LIKE '%...' cannot use an index, although LIKE '...%' can

Upvotes: 0

Related Questions