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