CODEWITHSUNDEEP
mysql
Mawg
Mawg

Reputation: 40145

Counting and ordering and joining

My previous question gave me the answer that I could take 

mysql> describe taps;
+------------+-----------+------+-----+-------------------+-------+
| Field      | Type      | Null | Key | Default           | Extra |
+------------+-----------+------+-----+-------------------+-------+
| tag        | int(11)   | NO   |     | NULL              |       |
| station    | int(11)   | NO   |     | NULL              |       |
| time_Stamp | timestamp | NO   |     | CURRENT_TIMESTAMP |       |
+------------+-----------+------+-----+-------------------+-------+
3 rows in set (0.00 sec)

and use the query 

SELECT tag
     , COUNT(DISTINCT station) as `visit_count` 
  FROM taps 
 GROUP 
    BY tag 
 ORDER 
    BY COUNT(DISTINCT station) DESC

to get the visitors ordered by the number of stations they have visited.

Now I want to add

mysql> describe visitors;
+--------+---------+------+-----+---------+-------+
| Field  | Type    | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| tag_id | int(11) | NO   |     | NULL    |       |
| name   | text    | NO   |     | NULL    |       |
| email  | text    | NO   |     | NULL    |       |
| phone  | text    | NO   |     | NULL    |       |
+--------+---------+------+-----+---------+-------+
4 rows in set (0.00 sec)

And, instead of getting the visitors tag_id, I want to get his name, email and phone. I know that it involves aJOIN, but just can't figure it out :-(

[Update] Just to be clear, I want to output an HTML table, ordered by whoever visited the most stations, showing name, email & phone

Upvotes: 0

Views: 45

Answers (1)

shikhar
shikhar

Reputation: 2469

SELECT tag
     ,v.email, COUNT(DISTINCT station) as `visit_count` 
  FROM taps as t JOIN visitors as v ON t.tag = v.tag_id
 GROUP 
    BY v.email
 ORDER 
    BY COUNT(DISTINCT station) DESC

Upvotes: 1

Related Questions