CODEWITHSUNDEEP
phpmysqlsqlcodeigniterjoin
De ruige
De ruige

Reputation: 347

Complex SQL query JOIN and WHERE

I have a table, where I need to get the contacts from. So I use a complex query to retrieve all the contacts from my "sms" table.

However, it should only count the records for the provided "imei" field, but my query keeps returning all the counts for the specific contact.

Below is the code is use:

$sql = $this->db->query('
    SELECT 
        t1.mnumber AS t1_number, t1.mcontent AS t1_content, t1.mcontact as t1_contact, SUM(t2.TOTAL) AS mTotal
    FROM
        sms t1
            JOIN
        (SELECT 
            mContent, mcontact, COUNT(mNumber) AS TOTAL, mnumber, MAX(mID) mID
        FROM
            sms
        GROUP BY mContact) t2 ON t1.mcontact = t2.mcontact
            AND t1.mid = t2.mid
        WHERE t1.mIMEI = ' . (IMEI) . '
        GROUP BY t1.mContact
        ORDER BY t1.mid DESC');

    return $sql->result();

Upvotes: 2

Views: 153

Answers (3)

De ruige
De ruige

Reputation: 347

The answer given by @Gordon Linoff is correct. The real issue hower is in the table itself.

It stores all messages, but sometimes the 'mNumber' is an international phonenumber, and sometimes it is the format the user stored it to the cellphone.

Therefore, when i load the 'mContent' using the 'mNumber' field, it only gives me a partial result!!

So still looking for a solution to that? Maybe a multiple WHERE clausule?

Upvotes: 0

SamNapkins
SamNapkins

Reputation: 11

You need the WHERE clause in both of the SELECT statements.

SELECT t1.mnumber AS t1_number, t1.mcontent AS t1_content, t1.mcontact as t1_contact, SUM(t2.TOTAL) AS mTotal
FROM sms t1 JOIN
     (SELECT mContent, mcontact, COUNT(mNumber) AS TOTAL, mnumber, MAX(mID) mID
      FROM sms
      WHERE mIMEI = ' . (IMEI) . '
      GROUP BY mContact
     ) t2
     ON t1.mcontact = t2.mcontact AND t1.mid = t2.mid
WHERE t1.mIMEI = ' . (IMEI) . '
GROUP BY t1.mContact
ORDER BY t1.mid DESC

Without the WHERE clause in both places, you will be joining all records that match the mContact. Even the ones that do not meet your t1.mIMEI = ' . (IMEI) . ' condition.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270391

Better formatted, the query is:

SELECT t1.mnumber AS t1_number, t1.mcontent AS t1_content, t1.mcontact as t1_contact, SUM(t2.TOTAL) AS mTotal
FROM sms t1 JOIN
     (SELECT mContent, mcontact, COUNT(mNumber) AS TOTAL, mnumber, MAX(mID) mID
      FROM sms
      GROUP BY mContact
     ) t2
     ON t1.mcontact = t2.mcontact AND t1.mid = t2.mid
WHERE t1.mIMEI = ' . (IMEI) . '
GROUP BY t1.mContact
ORDER BY t1.mid DESC

The problem is that the where clause is in the wrong place. For it to affect the count, you need it in the subquery. For it to affect the output rows, you need it in the outer query. I would suggest:

SELECT t1.mnumber AS t1_number, t1.mcontent AS t1_content, t1.mcontact as t1_contact, SUM(t2.TOTAL) AS mTotal
FROM sms t1 JOIN
     (SELECT mContent, mcontact, COUNT(mNumber) AS TOTAL, mnumber, MAX(mID) as maxmid
      FROM sms
      WHERE sms.mIMEI = ' . (IMEI) . '
      GROUP BY mContact
     ) t2
     ON t1.mcontact = t2.mcontact AND t1.mid = t2.maxmid
GROUP BY t1.mContact
ORDER BY t1.mid DESC

I suspect there may be other simplifications, depending on what mid andmcontact mean in terms of the database structure.

Upvotes: 2

Related Questions