MySQL inner join question

Question

I have two tables in a MySQL database

urltracker with columns id and urlclicked and urlreferrer with columns id, url_id and urlreferrer

the urltracker is really just a lookup table with the url_id column in the urlreferrer table being the link between the tables.

Example DDL:

CREATE TABLE urltracker (
  id           SERIAL PRIMARY KEY,
  urlclicked   VARCHAR(200) NOT NULL
);

CREATE TABLE urlreferrer (
  id           SERIAL PRIMARY KEY,
  url_id       BIGINT UNSIGNED NOT NULL,
  urlreferrer  VARCHAR(200) NOT NULL
  FOREIGN KEY(url_id) REFERENCES urltracker(id)
);

What i wany to do is join the two tables in such a way that i can get the url that was clicked by looking up the urlclicked table and the total number of referrers from the urlreferrer table for this particular url.

I have messed about with this for 2 hours and i'm not getting anywhere, im no database expert can anyone please explain how to achieve this.

Thanks in advance

Answer 1

if you want it for all Urls,

 Select urlClicked, Count(*)
   From urlReferrer R 
       Join urlTracker U
           On U.id = R.urlId
   Group By urlClicked

If you only want it for a specified initial click,

 Select urlClicked, Count(othRef.id)
 From urlReferrer R 
       Join (urlTracker U Join urlReferrer othRef
                  On othRef.urlId = U.urlId)
           On U.id = R.urlId
 Where R.id = [Specified Referrer's Id]
 Group By urlClicked

-- edited to correct order of Where clause

Answer 2

I think :

SELECT ut.urlclicked, COUNT(ur.id) 
FROM urltracker ut JOIN urlreferrer ur ON (ut.id = ur.url_id) 
GROUP BY ut.urlclicked

should do it.

Answer 3

Thanks that worked a treat!