MySQL internal join question
I have two tables in a MySQL database
urltracker with column ids and urls and urlreferrer with column ids, url_id and urlreferrer
urltracker is simply a lookup table with the url_id column in the urlreferrer table, which is the link between the tables.
DDL example:
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 need to do is join the two tables in such a way that I can get the url that was clicked by looking at the url table and the total number of referrers from the urlreferrer table for that particular url.
I messed this up with this for 2 hours and I am not going anywhere, im no database specialist can explain how to do this.
Thank you in advance
0
source to share
2 answers
if you want it for all addresses,
Select urlClicked, Count(*)
From urlReferrer R
Join urlTracker U
On U.id = R.urlId
Group By urlClicked
If you only want it for the 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 Id]
Group By urlClicked
- edited for correct order of Where clause
+1
source to share