Retrieve data from tables via JOINS

Question

I have three tables: sessions, urls and visitors. I need to join these three tables in such a way that I should be able to get data from each table and the maximum number of rows returned should be equal to sessions.

Following is the basic schema of my tables.

Table sessions

session_id | url_id | referrer_id | country
-------------------------------------------
  1234     |  a1b1  |    bb11     |   US
  4567     |  x1y1  |    ll33     |   IN
  6789     |  a1b1  |    ff99     |   UK

Table urls

  id       |        url                 |
-----------------------------------------
 a1b1      | https://url-1.com          |
 x1y1      | https://url-2.com          |
 bb11      | https://referrer-url-1.com |
 ll33      | https://referrer-url-2.com |
 ff99      | https://referrer-url-3.com |

Table visitors

   id      | session_id | visiting_time |
-----------------------------------------
   1       |   1234     |   447383930   |
   2       |   4567     |   547383930   |
   3       |   6789     |   647383930   |

What I want as the final output should look like:

 session_id | visiting_time | url                |  referrer_url      | country
------------------------------------------------------------------------------------------
   1234     |  447383930    | https://url-1.com  | https://referrer-url-1.com | US |
   4567     |  547383930    | https://url-2.com  | https://referrer-url-2.com | IN |
   6789     |  647383930    | https://url-1.com  | https://referrer-url-3.com | UK |

I want to map url_id in sessions table with id in urls table and get the corresponding url from urls table and have the value in the new column named url. Similarly, map referrer_id in sessions table with id in urls table and get the corresponding url from urls table and have the value in the new column named referring_url.

As you can see: JOINS with sessions and visitors is simple and can be simply done via:

select session_id, visiting_time, country
from sessions,
     visitors
where sessions.session_id = visitors.session_id;

But joining with urls table and getting the url and referring_url is somewhat tricky. I have tried LEFT JOIN and INNER JOIN but couldn't make it work.

Any help with query or references would be helpful.

Thanks!

Please stop using comma based Implicit joins and use [Explicit `Join` based syntax](https://stackoverflow.com/questions/44917/explicit-vs-implicit-sql-joins) — Madhur Bhaiya, Oct 25 '18 at 19:58
Your current query does an INNER JOIN, an old style implicit one. — jarlh, Oct 25 '18 at 19:59

Madhur Bhaiya · Accepted Answer · 2018-10-25T20:07:43.180

2

You should avoid using comma based Implicit joins and use Explicit Join based syntax
You will need two joins with urls table; one to fetch the url and another for referrer_url.

Try the following:

SELECT s.session_id, 
       v.visiting_time, 
       u1.url, 
       u2.url AS referrer_url, 
       s.country
FROM sessions AS s
JOIN visitors AS v ON v.session_id = s.session_id 
JOIN urls AS u1 ON u1.id = s.url_id 
JOIN urls AS u2 ON u2.id = s.referrer_id

edited Oct 25 '18 at 20:07

answered Oct 25 '18 at 20:01

Madhur Bhaiya

28,155
10
49
57

1

All other answers are also valid but this answer worked perfectly and was answered first. Thanks Madhur Bhaiya for helping :) – softvar Oct 25 '18 at 20:14
@softvar happy to help; and do stop using comma based joins :) – Madhur Bhaiya Oct 25 '18 at 20:14
1

Sure, will take care from next timw. Will read more about it in depth. – softvar Oct 25 '18 at 20:18

score 1 · Answer 2 · answered Oct 25 '18 at 20:02

select sessions.session_id, visitors.visiting_time, urls.url, urlsReferrer.url referrer_url, sessions.country
from sessions
        inner join visitors on sessions.session_id = visitors.session_id
        inner join urls on sessions.url_id = url.id
        left join urls urlsReferrer on sessions.referrer_id = urlsReferrer.id

score 1 · Answer 3 · answered Oct 25 '18 at 20:03

You should use a join on urls twice one of url_id and one for referrer_id

    select session_id
      , visiting_time
      , u1.url
      , u2.url
      , country
    from sessions
    INNER JOIN  visitors ON sessions.session_id = visitors.session_id
    INNER JOIN urls u1 on u1.id= sessions.url_id
    INNER JOIN urls u2 on u2.id= sessions.referrer_id

In this way you can join the sessions for retrive both then values you need

score 1 · Answer 4 · answered Oct 25 '18 at 20:20

Joins are defined in the from statement - please read up on https://www.w3schools.com/sql/default.asp to better get a sense of join usage.

Modify the query as needed based on which table "referrer_url" actually comes from

Warning: You must include a where statement which limits your result. I strongly suggest defining a date field and range to prevent you from initiating a long running query and affecting database performance.

see below for query

select 
s.session_id, 
v.visiting_time, 
s.country,
u.url,
u.referrer_url


from 
sessions s
join visitors v on session_id
join urls on u.id=s.url_id

;

Retrieve data from tables via JOINS

4 Answers4

see below for query