CODEWITHSUNDEEP
mysqljoinsubquery
Laszlo Molnar
Laszlo Molnar

Reputation: 21

Combine 3 MySQL queries

I am a mysql newbie. I would like to combine the following 3 queries and the result set into one, but no success. The differences between the queries are in the WHERE clause (kw_research.id_country=) and in the SELECT columns (global_value, local_value, us_local_value). Any help would be appreciated. Thank you.

Query 1,


    SELECT
    dn_domains.sid,
    dn_domains.domain,
    dn_domains.tld,
    kw_keywords.id_keyword,
    kw_keywords.keyword_name,
    kw_research.id_research,
    dn_tlds.value,
    (kw_research.local_search * kw_research.aprx_cpc * '0.42' * dn_tlds.value * '12') AS global_value
    FROM
    (((dn_domains
    JOIN kw_keywords)
    JOIN kw_research)
    JOIN dn_tlds) ,
    co_domain_keyword
    WHERE
    dn_domains.sid = co_domain_keyword.id_domain AND
    kw_keywords.id_keyword = co_domain_keyword.id_keyword AND
    kw_keywords.id_keyword = kw_research.id_keyword AND
    dn_domains.tld = dn_tlds.tld AND
    kw_research.id_country = '1'
    ORDER BY
    dn_domains.sid ASC

Result:


    +-----+------------------------+------+------------+---------------------+-------------+-------+---------------------+
    | sid | domain                 | tld  | id_keyword | keyword_name        | id_research | value | global_value        |
    +-----+------------------------+------+------------+---------------------+-------------+-------+---------------------+
    |   4 | infodomain.info        | info |      13534 | info domain         |       27181 |  0.05 |   4864.859894050059 |
    |   5 | exampledomain.com      | com  |      28566 | example domain      |       28694 |     1 |   8.064000120162964 |
    |   6 | domainexample.com      | com  |      27433 | domain example      |       27503 |     1 |  14.112000210285185 |
    |   7 | officedomain.com       | com  |      27072 | office domain       |       27130 |     1 |   297.3600044310093 |
    |   8 | extractdomain.com      | com  |      27578 | extract domain      |       27680 |     1 |   6676.991976928712 |
    +-----+------------------------+------+------------+---------------------+-------------+-------+---------------------+
    5 rows in set

Query 2,


    SELECT
    dn_domains.sid,
    dn_domains.domain,
    dn_domains.tld,
    kw_keywords.id_keyword,
    kw_keywords.keyword_name,
    kw_research.id_research,
    dn_tlds.value,
    (kw_research.local_search * kw_research.aprx_cpc * '0.42' * dn_tlds.value * '12') AS local_value
    FROM
    (((dn_domains
    JOIN kw_keywords)
    JOIN kw_research)
    JOIN dn_tlds) ,
    co_domain_keyword
    WHERE
    dn_domains.sid = co_domain_keyword.id_domain AND
    kw_keywords.id_keyword = co_domain_keyword.id_keyword AND
    kw_keywords.id_keyword = kw_research.id_keyword AND
    dn_domains.tld = dn_tlds.tld AND
    kw_research.id_country = dn_tlds.country
    ORDER BY
    dn_domains.sid ASC

Result:


    +-----+------------------------+------+------------+---------------------+-------------+-------+--------------------+
    | sid | domain                 | tld  | id_keyword | keyword_name        | id_research | value | local_value        |
    +-----+------------------------+------+------------+---------------------+-------------+-------+--------------------+
    |   4 | infodomain.info        | info |      13534 | info domain         |       27181 |  0.05 |  4864.859894050059 |
    |   5 | exampledomain.com      | com  |      28566 | example domain      |       28694 |     1 |  8.064000120162964 |
    |   6 | domainexample.com      | com  |      27433 | domain example      |       27503 |     1 | 14.112000210285185 |
    |   7 | officedomain.com       | com  |      27072 | office domain       |       27130 |     1 |  297.3600044310093 |
    |   8 | extractdomain.com      | com  |      27578 | extract domain      |       27680 |     1 |  6676.991976928712 |
    +-----+------------------------+------+------------+---------------------+-------------+-------+--------------------+
    5 rows in set

Query 3,


    SELECT
    dn_domains.sid,
    dn_domains.domain,
    dn_domains.tld,
    kw_keywords.id_keyword,
    kw_keywords.keyword_name,
    kw_research.id_research,
    dn_tlds.value,
    (kw_research.local_search * kw_research.aprx_cpc * '0.42' * dn_tlds.value * '12') AS us_local_value
    FROM
    (((dn_domains
    JOIN kw_keywords)
    JOIN kw_research)
    JOIN dn_tlds) ,
    co_domain_keyword
    WHERE
    dn_domains.sid = co_domain_keyword.id_domain AND
    kw_keywords.id_keyword = co_domain_keyword.id_keyword AND
    kw_keywords.id_keyword = kw_research.id_keyword AND
    dn_domains.tld = dn_tlds.tld AND
    kw_research.id_country = '220'
    ORDER BY
    dn_domains.sid ASC

Result:


    +-----+--------------------+------+------------+------------------+-------------+-------+-----------------------+
    | sid | domain             | tld  | id_keyword | keyword_name     | id_research | value | us_local_value        |
    +-----+--------------------+------+------------+------------------+-------------+-------+-----------------------+
    |   4 | infodomain.info    | info |      13534 | info domain      |       13535 |  0.05 |     1415.231969178199 |
    |  30 | domainhotspot.fr   | fr   |      13837 | domain hotspot   |       13838 |   0.1 |    3880.8000578284264 |
    |  72 | domainrecovery.net | net  |       2931 | domain recovery  |        2931 |   0.2 |     1761.177677000428 |
    |  75 | domainburner.co    | co   |      16893 | domain burner    |       16894 |   0.1 |    2286.1440437994006 |
    |  82 | domainrecorder.co  | co   |      16969 | domain recorder  |       16970 |   0.1 |    1344.6720444302562 |
    +-----+--------------------+------+------------+------------------+-------------+-------+-----------------------+
    5 rows in set

But, I need the following result set:


    +-----+------------------------+------+------------+---------------------+-------------+-------+-----------------------+---------------------+-----------------------+
    | sid | domain                 | tld  | id_keyword | keyword_name        | id_research | value | global_value           | local_value         | us_local_value        |
    +-----+------------------------+------+------------+---------------------+-------------+-------+-----------------------+---------------------+-----------------------+
    |   4 | infodomain.info        | info |      13534 | info domain         |       27181 |  0.05 |     4864.859894050059 |   4864.859894050059 |     1415.231969178199 |
    |   5 | exampledomain.com      | com  |      28566 | example domain      |       28694 |     1 |     8.064000120162964 |   8.064000120162964 |                  NULL |
    |   6 | domainexample.com      | com  |      27433 | domain example      |       27503 |     1 |    14.112000210285185 |  14.112000210285185 |                  NULL |
    |   7 | officedomain.com       | com  |      27072 | office domain       |       27130 |     1 |     297.3600044310093 |   297.3600044310093 |                  NULL |
    |   8 | extractdomain.com      | com  |      27578 | extract domain      |       27680 |     1 |     6676.991976928712 |   6676.991976928712 |                  NULL |
    |  30 | domainhotspot.fr       | fr   |      13837 | domain hotspot      |       13838 |   0.1 |                  NULL |                NULL |    3880.8000578284264 |
    |  72 | domainrecovery.net     | net  |       2931 | domain recovery     |        2931 |   0.2 |                  NULL |                NULL |     1761.177677000428 |
    |  75 | domainburner.co        | co   |      16893 | domain burner       |       16894 |   0.1 |                  NULL |                NULL |    2286.1440437994006 |
    |  82 | domainrecorder.co      | co   |      16969 | domain recorder     |       16970 |   0.1 |                  NULL |                NULL |    1344.6720444302562 |
    +-----+------------------------+------+------------+---------------------+-------------+-------+-----------------------+---------------------+-----------------------+

Upvotes: 2

Views: 712

Answers (3)

JHS
JHS

Reputation: 7871

You can combine the queries with a UNION clause and write a top level SELECT with a DISTINCT something like this -

SELECT dn_domains.sid,
       dn_domains.domain,
       dn_domains.tld,
       kw_keywords.id_keyword,
       kw_keywords.keyword_name,
       kw_research.id_research,
       dn_tlds.value,
       CASE WHEN kw_research.id_country = '1' THEN
       (kw_research.local_search * kw_research.aprx_cpc * '0.42' * dn_tlds.value * '12') 
       ELSE NULL END AS global_value,
       (kw_research.local_search * kw_research.aprx_cpc * '0.42' * dn_tlds.value * '12') AS local_value,
       CASE WHEN kw_research.id_country = '220' THEN
       (kw_research.local_search * kw_research.aprx_cpc * '0.42' * dn_tlds.value * '12') 
       ELSE NULL END AS us_local_value
FROM (((dn_domains
        JOIN kw_keywords)
        JOIN kw_research)
        JOIN dn_tlds) ,
      co_domain_keyword
WHERE dn_domains.sid = co_domain_keyword.id_domain AND
      kw_keywords.id_keyword = co_domain_keyword.id_keyword AND
      kw_keywords.id_keyword = kw_research.id_keyword AND
      dn_domains.tld = dn_tlds.tld AND
      (kw_research.id_country IN (1, 220) OR kw_research.id_country = dn_tlds.country)
ORDER BY dn_domains.sid ASC

Upvotes: 2

halfer
halfer

Reputation: 20420

Here's another approach, based on a re-read of your question - you just need some OR statements:

SELECT
dn_domains.sid,
dn_domains.domain,
dn_domains.tld,
kw_keywords.id_keyword,
kw_keywords.keyword_name,
kw_research.id_research,
dn_tlds.value,
(
    kw_research.local_search *
    kw_research.aprx_cpc * '0.42' *
    dn_tlds.value * '12'
) AS global_value
FROM
(((dn_domains
JOIN kw_keywords)
JOIN kw_research)
JOIN dn_tlds) ,
co_domain_keyword
WHERE
dn_domains.sid = co_domain_keyword.id_domain AND
kw_keywords.id_keyword = co_domain_keyword.id_keyword AND
kw_keywords.id_keyword = kw_research.id_keyword AND
dn_domains.tld = dn_tlds.tld AND
( /* Here's the new clause */
    kw_research.id_country IN (1, 220) OR
    kw_research.id_country = dn_tlds.country
)
ORDER BY
dn_domains.sid ASC

I am presuming that id_country is an integer, and so have converted the numeric literals from strings to numbers. I've also used IN, which is a bit faster that multiple OR statements.

Upvotes: 0

halfer
halfer

Reputation: 20420

One easy solution is this:

(
    SELECT ... /* i.e. query 1 */
)
UNION (
    SELECT ... /* i.e. query 2 */
)
UNION (
    SELECT ... /* i.e. query 3 */
)
ORDER BY
    sid

Here's a demo you can play with.

In each query, remove the ORDER BY and just add it to the end, as above. This approach will remove any duplicate rows between the queries - if you want the dup rows, use UNION ALL instead of UNION.

Bear in mind this may be non-optimal - an analysis of your tables may show that writing it all as one query would be more efficient.

Upvotes: 1

Related Questions