CODEWITHSUNDEEP
phpmysqlsqljoinsql-order-by
user164863
user164863

Reputation: 641

SQL query from two tables ordering results by both tables columns

I have two tables for registering out-going phone calls.

First table is people. The scheme is:

-----------------------------------
id | fname | lname | phone_number |
-----------------------------------
1  | John  | Black | 132333312    |
2  | Marry | White | 172777441    |
-----------------------------------

Second tables called calls. The scheme is:

----------------------------------------------------------------------
id | scr_phone | dst_phone | dst_public_id | date      | notes       |
----------------------------------------------------------------------
1  | 555       | 132333312 | 1             | 1.12.2013 | chit-chat   |
2  | 555       | 172777441 | 2             | 1.12.2013 | serios talk |
3  | 555       | 172777441 | 2             | 2.12.2013 | conversation|
----------------------------------------------------------------------

I'm displaying list of phones for users not in an alphabetical order but by frequency of calls. So whoever calls from local phone 555 sees at the top the people who he/she calls more often. Here is the MySQL query I use for it:

SELECT p.fname, p.lname, c.notes, COUNT(c.dst_public_id) AS amount
FROM people p
JOIN calls c ON c.dst_public_id = p.id
WHERE phones != ''
GROUP BY p.id
ORDER BY amount DESC, p.lname ASC

As a result I get person number 2 at the top of the phones list and person number 1 is on second place (I count how many calls are for each individual public and order it by that amount). Here is the query result:

--------------------------------
fname | lname | notes | amount |
--------------------------------
Marry | White |       |  2     |
John  | Black |       |  1     |
--------------------------------

This is all good. But I want always to display last note made on last conersation with that person. I can probably make a totally separated MySQL query for each person requesting last record of each and getting the data from that, something like:

SELECT notes FROM calls WHERE dst_public_id = 2 ORDER BY date DESC LIMIT 1

... and then add the result of the first query inside my PHP script, but is there any way to do it with one single query?

Upvotes: 3

Views: 579

Answers (2)

peter.petrov
peter.petrov

Reputation: 39437

Can you try this? I haven't tested it as I don't have SQL in front of me but you should get the idea.

SELECT tbl.notes, tbl.amount, p.fname, p.lname

FROM people p

join
(
select t.dst_public_id, c1.notes, t.cnt as amount
from calls c1 join 
   (
      SELECT c2.dst_public_id, count(c2.id) as cnt, max(c2.id) as id
      FROM 
      calls c2
      group by c2.dst_public_id
   ) t on c1.id = t.id
) tbl on p.id = tbl.dst_public_id

WHERE p.phones != ''
GROUP BY p.id
ORDER BY tbl.amount DESC, p.lname ASC

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269513

You can get the last note with the following MySQL trick, using group_concat() and substring_index():

SELECT p.fname, p.lname, c.notes, COUNT(c.dst_public_id) AS amount,
       substring_index(group_concat(notes order by id desc separator '^'), '^', 1) as last_notes
FROM people p JOIN
     calls c
     ON c.dst_public_id = p.id
WHERE phones != ''
GROUP BY p.id
ORDER BY amount DESC, p.lname ASC;

For this to work, you need a separator character that will never appear in the notes. I have chosen '^' for this purpose.

Upvotes: 2

Related Questions