join - Combine 3 MySQL queries -

-

i mysql newbie. combine following 3 queries , result set one, no success. differences between queries in clause (kw_research.id_country=) , in select columns (global_value, local_value, us_local_value). 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') global_value         (((dn_domains     join kw_keywords)     join kw_research)     join dn_tlds) ,     co_domain_keyword         dn_domains.sid = co_domain_keyword.id_domain ,     kw_keywords.id_keyword = co_domain_keyword.id_keyword ,     kw_keywords.id_keyword = kw_research.id_keyword ,     dn_domains.tld = dn_tlds.tld ,     kw_research.id_country = '1'     order     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') local_value         (((dn_domains     join kw_keywords)     join kw_research)     join dn_tlds) ,     co_domain_keyword         dn_domains.sid = co_domain_keyword.id_domain ,     kw_keywords.id_keyword = co_domain_keyword.id_keyword ,     kw_keywords.id_keyword = kw_research.id_keyword ,     dn_domains.tld = dn_tlds.tld ,     kw_research.id_country = dn_tlds.country     order     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') us_local_value         (((dn_domains     join kw_keywords)     join kw_research)     join dn_tlds) ,     co_domain_keyword         dn_domains.sid = co_domain_keyword.id_domain ,     kw_keywords.id_keyword = co_domain_keyword.id_keyword ,     kw_keywords.id_keyword = kw_research.id_keyword ,     dn_domains.tld = dn_tlds.tld ,     kw_research.id_country = '220'     order     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, need 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 |     +-----+------------------------+------+------------+---------------------+-------------+-------+-----------------------+---------------------+-----------------------+

you can combine queries union clause , write top level select distinct -

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'        (kw_research.local_search * kw_research.aprx_cpc * '0.42' * dn_tlds.value * '12')         else null end global_value,        (kw_research.local_search * kw_research.aprx_cpc * '0.42' * dn_tlds.value * '12') local_value,        case when kw_research.id_country = '220'        (kw_research.local_search * kw_research.aprx_cpc * '0.42' * dn_tlds.value * '12')         else null end us_local_value (((dn_domains         join kw_keywords)         join kw_research)         join dn_tlds) ,       co_domain_keyword dn_domains.sid = co_domain_keyword.id_domain ,       kw_keywords.id_keyword = co_domain_keyword.id_keyword ,       kw_keywords.id_keyword = kw_research.id_keyword ,       dn_domains.tld = dn_tlds.tld ,       (kw_research.id_country in (1, 220) or kw_research.id_country = dn_tlds.country) order dn_domains.sid asc

Comments

Post a Comment

Popular posts from this blog

django - How can I change user group without delete record -

-
on website user have 1 group. , user can change group. it's made user.groups.clear() and user.groups.add(new_group) but it's not efficient, because there 2 sql query: delete, insert. how can change group update query? user , group related each other using manytomanyfield . means intersection table exists relating both entities, , if don't specify model map (using through attribute) django creates 1 you. looking @ sources django.contrib.auth.models see that's case. fortunatly, can access intermediary model using through attribute of manager (in case, user.groups.through ). can use regular model. example: >>> alice = user.objects.create_user('alice', 'alice@example.com', 'alicepw') >>> employee = group.objects.create(name='employee') >>> manager = group.objects.create(name='manager') >>> alice.groups.add(employee) >>> alice.groups.all() [<group: employee...
Read more

java - EclipseLink JPA Object is not a known entity type -

-
i have strange problem eclipselink , object want persist. have 1 object (keypointlistimpl) stores object keypointimpl in list. persisting keypointimpl objects works great if try persist keypointlistimpl object java.lang.illegalargumentexception says object keypointimpl isn't known entity type. here keypointimpl code: @entity @table(name="keypoints") public class keypointimpl implements keypoint { @id @generatedvalue(strategy = generationtype.identity) private int id; @enumerated(enumtype.string) private detectortype keypointtype; private float x; private float y; private float size; private float angle; private float response; private int octave; private int classid; ... } here keypointlistimpl code: @entity @table(name="keypointlists") public class keypointlistimpl implements keypointlist { @id @generatedvalue(strategy = generationtype.identity) private int id; @onetoone(cascade={ca...
Read more

java - Need to add SOAP security token -

-
i need set custom soap header attribute on jax-ws generated webservice client. case webservice calls must go through proxy server requiring specific token (recieved web request header) present in soap request header. e.g.: 1 carserviceservice service = null; 2 service = new carserviceservice(new url(url), new qname(qname); 3 carserviceendpoint port = service.getcarserviceport(); it seems in line 3 wsdl retrieved , call fails due missing security token. 1 point direction on how done? a detailed example has been mentioned here: creating , deploying jax-ws web service on tomcat 6 this article shows how create , use security token .
Read more