seo Question posted in
Some basic information to get you started with this can be found here.

SQL Query: How to get data from four different tables – SEO

I am using mysql and here is the schema that I have.

First table: Keywords

+------------+-------------+
| keyword_id | keyword_tag |
+------------+-------------+
|          2 | marketing   |
|         58 | sales       |
|         59 | scraping    |
|          1 | seo         |
|          3 | testkeyword |
+------------+-------------+

Second table: Domains

+-----------+-----------------+---------------+---------------------+-----------------+
| domain_id | domain_name     | campaign_name | campaign_date       | campaign_note   |
+-----------+-----------------+---------------+---------------------+-----------------+
|         1 | test.org        | campaign 1    | 2019-08-27 17:10:58 | Test            |
|        11 | example.org     | campaign 2    | 2019-08-27 17:36:06 | Campaign Note 2 |
+-----------+-----------------+---------------+---------------------+-----------------+

Third table: Domain_Keywords

+-------+-----------+------------+
| dk_id | domain_id | keyword_id |
+-------+-----------+------------+
|     1 |         1 |          1 |
|     2 |         1 |          2 |
|     3 |         1 |          3 |
|     4 |        11 |          1 |
|     5 |        11 |          2 |
|     6 |        11 |         58 |
|     7 |        11 |         59 |
+-------+-----------+------------+

Fourth table: Emails

+----------+-----------------------+-------+--------------+-------+----------------+-----------+---------+
| email_id | email                 | valid | is_generated | score | number_results | domain_id | user_id |
+----------+-----------------------+-------+--------------+-------+----------------+-----------+---------+
|        1 | [email protected]           |     1 |            1 |   0.5 |              2 |        1  |       3 |
|        2 | [email protected]           |     1 |            1 |   0.3 |              0 |        1  |       1 |
|        3 | [email protected]        |     1 |            1 |   0.3 |              0 |        11 |       4 |
|        4 | [email protected]        |     1 |            1 |   0.3 |              0 |        11 |       4 |
|        5 | [email protected]        |     1 |            1 |   0.3 |              0 |        11 |       1 |
|        6 | [email protected]        |     1 |            1 |   0.5 |              3 |        11 |       3 |
+----------+-----------------------+-------+--------------+-------+----------------+-----------+---------+

I want to show the data as following:
enter image description here

Kindly guide me how can I query data from those four tables. Thanks

Tags:

2

Answers


  1. 0

    You may try this. Simple inner join with group by clause will give you your desired result. Just Group_Concat is use to convert your cities name into one string and count is use to count the email records.

    You may find this fiddle working Link.

    
select Domains.campaign_name, Domains.campaign_date, tab2.countemail as Found, tab2.countemail as generate, tab1.KeyTag 
from Domains inner join 
       (select Domains.domain_id, group_concat(Keywords.keyword_tag) as KeyTag 
         from Domains  
          inner join Domain_Keywords on Domains.domain_id = Domain_Keywords.domain_id 
          inner join Keywords on Domain_Keywords.keyword_id = Keywords.keyword_id  group by  Domains.domain_id ) as tab1 
          on Domains.domain_id = tab1.domain_id
inner join 
         ( select Domains.domain_id, Count(Emails.is_generated) as countemail
          from Domains
           inner join Emails on Domains.domain_id = Emails.domain_id
           group by  Domains.domain_id
          ) as tab2
on Domains.domain_id = tab2.domain_id

​```
    Login or Signup to reply.
  2. 0

    Because you can have multiple rows in each table for a given domain_id, it’s necessary to perform all aggregation in derived tables, and then join them to the Domains table.

    SELECT d.campaign_name,
       d.campaign_date,
       COALESCE(e.num_emails, 0) AS num_emails,
       COALESCE(e.num_generated_emails, 0) AS num_generated_emails,
       k.keywords
FROM Domains d
JOIN (SELECT dk.domain_id, 
             GROUP_CONCAT(k.keyword_tag) AS keywords
      FROM Domain_Keywords dk
      JOIN Keywords k ON k.keyword_id = dk.keyword_id
      GROUP BY dk.domain_id) k ON k.domain_id = d.domain_id
LEFT JOIN (SELECT domain_id,
                  COUNT(*) AS num_emails,
                  SUM(is_generated) AS num_generated_emails
           FROM Emails
           GROUP BY domain_id) e ON e.domain_id = d.domain_id

    Output:

    campaign_name   campaign_date       num_emails  num_generated_emails    keywords
campaign 1      2019-08-27 17:10:58 2           2                       seo,marketing,testkeyword
campaign 2      2019-08-27 17:36:06 4           4                       seo,marketing,sales,scraping

    Demo on dbfiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search