CODEWITHSUNDEEP
mysqlsqlpostgresql
softvar
softvar

Reputation: 18435

Retrieve data from tables via JOINS

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!

Upvotes: 1

Views: 53

Answers (4)

Jayrod
Jayrod

Reputation: 11

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

;

Upvotes: 1

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

  • 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

Upvotes: 2

ScaisEdge
ScaisEdge

Reputation: 133360

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

Upvotes: 1

DanB
DanB

Reputation: 2039

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

Upvotes: 1

Related Questions