MySQL Forums
Forum List  »  General

JOIN is not returning expected results
Posted by: eric tse
Date: January 15, 2018 12:13PM

I am running a query:

UPDATE mainsearch INNER JOIN (some queries to generae a set) i
ON mainsearch.regip=i.ip SET instance=i.instance1,state=i.state1, filename=i.filename1,dnsip=i.dnsip1;


I am abstracting out (some queries to generae a set).
In fact

UPDATE mainsearch INNER JOIN (SELECT DISTINCT h.instance instance1, (CASE WHEN (b.used='Y' AND a.lbip IS NULL) THEN 'DRP' WHEN (b.used='Y' AND a.lbip IS NOT NULL) THEN 'O' WHEN (b.used='N' AND a.lbip IS NOT NULL AND a.server IN ('server1.dmz','server2.dmz')) THEN 'DLB' WHEN (b.used='N' AND h.filename IS NOT NULL AND a.server NOT IN ('server1.dmz','server2.dmz')) THEN 'I' ELSE 'A' END) state1, h.filename filename1, h.dnsip dnsip1, h.ip ip FROM mainsearch a, ServerIPs b, HostInfo h WHERE a.regip=b.regip AND a.regip=h.ip AND h.url=a.url AND h.server='par.dmz' AND h.instance='rproxy-xSM-t1' AND EXISTS (SELECT * FROM ActiveURL l WHERE l.filename=h.filename AND l.server=h.server) ) i ON mainsearch.regip=i.ip SET instance=i.instance1,state=i.state1, filename=i.filename1,dnsip=i.dnsip1;

and I am filtering things out
using ip=142.xx.xxx.xxx



Before the update and the JOIN this is how mainsearch looks like
mysql> select * FROM mainsearch where regip="142.34.220.108"
-> ;
+----------------+------------------------------------------------+---------------+---------+---------------+----------+-------+----------+-------+
| regip | url | instance | server | lbip | loc | state | filename | dnsip |
+----------------+------------------------------------------------+---------------+---------+---------------+----------+-------+----------+-------+
| 142.34.220.108 | tst.admin.accommodationandrealestate.gov.bc.ca | rproxy-xSM-t1 | par.dmz | 142.34.210.51 | KAMLOOPS | A | NULL | NULL |
| 142.34.220.108 | tst.assetdisposal.gov.bc.ca | rproxy-xSM-t1 | par.dmz | 142.34.210.51 | KAMLOOPS | A | NULL | NULL |
| 142.34.220.108 | tst.csa.pss.gov.bc.ca | rproxy-xSM-t1 | par.dmz | 142.34.210.51 | KAMLOOPS | A | NULL | NULL |
| 142.34.220.108 | tst.eoe.pss.gov.bc.ca | rproxy-xSM-t1 | par.dmz | 142.34.210.51 | KAMLOOPS | A | NULL | NULL |
| 142.34.220.108 | tst.epo.pss.gov.bc.ca | rproxy-xSM-t1 | par.dmz | 142.34.210.51 | KAMLOOPS | A | NULL | NULL |
| 142.34.220.108 | tst.pss.gov.bc.ca | rproxy-xSM-t1 | par.dmz | 142.34.210.51 | KAMLOOPS | A | NULL | NULL |
| 142.34.220.108 | tst.store.env.gov.bc.ca | rproxy-xSM-t1 | par.dmz | 142.34.210.51 | KAMLOOPS | A | NULL | NULL |
| 142.34.220.108 | tst.view.pdc.gov.bc.ca | rproxy-xSM-t1 | par.dmz | 142.34.210.51 | KAMLOOPS | A | NULL | NULL |
+----------------+------------------------------------------------+---------------+---------+---------------+----------+-------+----------+-------+


Before the update

mysql> SELECT DISTINCT h.instance instance1, (CASE WHEN (b.used='Y' AND a.lbip IS NULL) THEN 'DRP' WHEN (b.used='Y' AND a.lbip IS NOT NULL) THEN 'O' WHEN (b.used='N' AND a.lbip IS NOT NULL AND a.server IN ('mallet.dmz','cleek.dmz')) THEN 'DLB' WHEN (b.used='N' AND h.filename IS NOT NULL AND a.server NOT IN ('mallet.dmz','cleek.dmz')) THEN 'I' ELSE 'A' END) state1, h.filename filename1, h.dnsip dnsip1, h.ip ip FROM mainsearch a, ServerIPs b, HostInfo h WHERE a.regip=b.regip AND a.regip=h.ip AND h.url=a.url AND h.server='par.dmz' AND h.instance='rproxy-xSM-t1' AND EXISTS (SELECT * FROM ActiveURL l WHERE l.filename=h.filename AND l.server=h.server) AND ip='142.34.220.108';
+---------------+--------+------------------------------------------------------------------------------+---------------+----------------+
| instance1 | state1 | filename1 | dnsip1 | ip |
+---------------+--------+------------------------------------------------------------------------------+---------------+----------------+
| rproxy-xSM-t1 | O | rproxy-xSM-t1/conf/sites/tst.admin.accommodationandrealestate.gov.bc.ca.conf | 142.34.176.24 | 142.34.220.108 |
| rproxy-xSM-t1 | O | rproxy-xSM-t1/conf/sites/tst.assetdisposal.gov.bc.ca.conf | 142.34.210.69 | 142.34.220.108 |
| rproxy-xSM-t1 | O | rproxy-xSM-t1/conf/sites/tst.csa.pss.gov.bc.ca.conf | 142.34.210.51 | 142.34.220.108 |
| rproxy-xSM-t1 | O | rproxy-xSM-t1/conf/sites/tst.eoe.pss.gov.bc.ca.conf | 142.34.210.51 | 142.34.220.108 |
| rproxy-xSM-t1 | O | rproxy-xSM-t1/conf/sites/tst.epo.pss.gov.bc.ca.conf | 142.34.212.8 | 142.34.220.108 |
| rproxy-xSM-t1 | O | rproxy-xSM-t1/conf/sites/tst.pss.gov.bc.ca.conf | 142.34.210.51 | 142.34.220.108 |
| rproxy-xSM-t1 | O | rproxy-xSM-t1/conf/sites/tst.store.env.gov.bc.ca.conf | 142.34.176.45 | 142.34.220.108 |
| rproxy-xSM-t1 | O | rproxy-xSM-t1/conf/sites/tst.view.pdc.gov.bc.ca.conf | 142.34.212.5 | 142.34.220.108 |
+---------------+--------+------------------------------------------------------------------------------+---------------+----------------+
8 rows in set (0.00 sec)



AFter the JOIN and update I get


mysql> select * FROM mainsearch where regip="142.34.220.108"
-> ;
+----------------+------------------------------------------------+---------------+---------+---------------+----------+-------+------------------------------------------------------------------------------+---------------+
| regip | url | instance | server | lbip | loc | state | filename | dnsip |
+----------------+------------------------------------------------+---------------+---------+---------------+----------+-------+------------------------------------------------------------------------------+---------------+
| 142.34.220.108 | tst.admin.accommodationandrealestate.gov.bc.ca | rproxy-xSM-t1 | par.dmz | 142.34.210.51 | KAMLOOPS | O | rproxy-xSM-t1/conf/sites/tst.admin.accommodationandrealestate.gov.bc.ca.conf | 142.34.176.24 |
| 142.34.220.108 | tst.assetdisposal.gov.bc.ca | rproxy-xSM-t1 | par.dmz | 142.34.210.51 | KAMLOOPS | O | rproxy-xSM-t1/conf/sites/tst.admin.accommodationandrealestate.gov.bc.ca.conf | 142.34.176.24 |
| 142.34.220.108 | tst.csa.pss.gov.bc.ca | rproxy-xSM-t1 | par.dmz | 142.34.210.51 | KAMLOOPS | O | rproxy-xSM-t1/conf/sites/tst.admin.accommodationandrealestate.gov.bc.ca.conf | 142.34.176.24 |
| 142.34.220.108 | tst.eoe.pss.gov.bc.ca | rproxy-xSM-t1 | par.dmz | 142.34.210.51 | KAMLOOPS | O | rproxy-xSM-t1/conf/sites/tst.admin.accommodationandrealestate.gov.bc.ca.conf | 142.34.176.24 |
| 142.34.220.108 | tst.epo.pss.gov.bc.ca | rproxy-xSM-t1 | par.dmz | 142.34.210.51 | KAMLOOPS | O | rproxy-xSM-t1/conf/sites/tst.admin.accommodationandrealestate.gov.bc.ca.conf | 142.34.176.24 |
| 142.34.220.108 | tst.pss.gov.bc.ca | rproxy-xSM-t1 | par.dmz | 142.34.210.51 | KAMLOOPS | O | rproxy-xSM-t1/conf/sites/tst.admin.accommodationandrealestate.gov.bc.ca.conf | 142.34.176.24 |
| 142.34.220.108 | tst.store.env.gov.bc.ca | rproxy-xSM-t1 | par.dmz | 142.34.210.51 | KAMLOOPS | O | rproxy-xSM-t1/conf/sites/tst.admin.accommodationandrealestate.gov.bc.ca.conf | 142.34.176.24 |
| 142.34.220.108 | tst.view.pdc.gov.bc.ca | rproxy-xSM-t1 | par.dmz | 142.34.210.51 | KAMLOOPS | O | rproxy-xSM-t1/conf/sites/tst.admin.accommodationandrealestate.gov.bc.ca.conf | 142.34.176.24 |
+----------------+------------------------------------------------+---------------+---------+---------------+----------+-------+------------------------------------------------------------------------------+---------------+
8 rows in set (0.00 sec)

This is undesirable because each web site
for example
tst.assetdisposal.gov.bc.ca
should have
rproxy-xSM-t1/conf/sites/tst.assetdisposal.gov.bc.ca.conf
instead of
rproxy-xSM-t1/conf/sites/tst.admin.accommodationandrealestate.gov.bc.ca.conf

Now every entry has rproxy-xSM-t1/conf/sites/tst.admin.accommodationandrealestate.gov.bc.ca.conf

because the JOIN use mainsearch.regip=i.ip
and there are multiple i.ip with different filename.


DESIRABLE STARTE

+----------------+------------------------------------------------+---------------+---------+---------------+----------+-------+------------------------------------------------------------------------------+---------------+
| regip | url | instance | server | lbip | loc | state | filename | dnsip |
+----------------+------------------------------------------------+---------------+---------+---------------+----------+-------+------------------------------------------------------------------------------+---------------+
| 142.34.220.108 | tst.admin.accommodationandrealestate.gov.bc.ca | rproxy-xSM-t1 | par.dmz | 142.34.210.51 | KAMLOOPS | O | rproxy-xSM-t1/conf/sites/tst.admin.accommodationandrealestate.gov.bc.ca.conf | 142.34.176.24 |
| 142.34.220.108 | tst.assetdisposal.gov.bc.ca | rproxy-xSM-t1 | par.dmz | 142.34.210.51 | KAMLOOPS | O | rproxy-xSM-t1/conf/sites/tst.assetdisposal.gov.bc.ca.conf | 142.34.176.24 |
| 142.34.220.108 | tst.csa.pss.gov.bc.ca | rproxy-xSM-t1 | par.dmz | 142.34.210.51 | KAMLOOPS | O | rproxy-xSM-t1/conf/sites/tst.csa.pss.gov.bc.ca.conf | 142.34.176.24 |
| 142.34.220.108 | tst.eoe.pss.gov.bc.ca | rproxy-xSM-t1 | par.dmz | 142.34.210.51 | KAMLOOPS | O | rproxy-xSM-t1/conf/sites/tst.eoe.pss.gov.bc.ca.conf | 142.34.176.24 |
| 142.34.220.108 | tst.epo.pss.gov.bc.ca | rproxy-xSM-t1 | par.dmz | 142.34.210.51 | KAMLOOPS | O | rproxy-xSM-t1/conf/sites/tst.epo.pss.gov.bc.ca.conf | 142.34.176.24 |
| 142.34.220.108 | tst.pss.gov.bc.ca | rproxy-xSM-t1 | par.dmz | 142.34.210.51 | KAMLOOPS | O | rproxy-xSM-t1/conf/sites/tst.pss.gov.bc.ca.conf | 142.34.176.24 |
| 142.34.220.108 | tst.store.env.gov.bc.ca | rproxy-xSM-t1 | par.dmz | 142.34.210.51 | KAMLOOPS | O | rproxy-xSM-t1/conf/sites/tst.store.env.gov.bc.ca.conf | 142.34.176.24 |
| 142.34.220.108 | tst.view.pdc.gov.bc.ca | rproxy-xSM-t1 | par.dmz | 142.34.210.51 | KAMLOOPS | O | rproxy-xSM-t1/conf/sites/tst.view.pdc.gov.bc.ca.conf | 142.34.176.24 |
+----------------+------------------------------------------------+---------------+---------+---------------+----------+-------+------------------------------------------------------------------------------+---------------+



Can you help me to modify the JOIN or the update so that
each site can have its own filename?

ALSO I need to consider if filename = NULL... can I just need to copy the NULL over or simply join mainsearch.regip=i.ip when it is NULL?

Thanks you very much for your help again


Thanks and regards,
Eric

Options: ReplyQuote


Subject
Written By
Posted
JOIN is not returning expected results
January 15, 2018 12:13PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.