Having a bit of a sticky wicket here... I'm sure the answer is simple
but I'm just not seeing it.
Basically I want to return all the TargetRanges and ScannerIDs. You
would think it's simple, here's the snag. See how ScannerID has 0 in
some rows. Well, we used 0 to mean "any scanner" in our PHP code. So, I
want a select query that will return me BOTH the rows for CompanyID =
123. As for the 0 ScannerID row, well leave the column blank, or
populate it with "ANY SCANNER" or something, that would be awesome.
Currently I only get the one row that has a ScannerID = 5. The second
query below doesn't work as it gives me multiple permutations (of
course). Any of you gurus know how to resolve this?
So the output should ideally look like:
+--------------------------+----------------+
| TargetIP | ScannerIP |
+--------------------------+----------------+
| www.interactnetworks.com | 66.150.172.129 |
| 192.168.30.1 | ANY SCANNER |
+--------------------------+----------------+
OR even this is cool
+--------------------------+----------------+
| TargetIP | ScannerIP |
+--------------------------+----------------+
| www.interactnetworks.com | 66.150.172.129 |
| 192.168.30.1 | |
+--------------------------+----------------+
mysql> select * from InteractV2.Job_Queue;
+---------+-----------+--------------------------+-----------+
| QueueID | ScannerID | TargetRange | CompanyID |
+---------+-----------+--------------------------+-----------+
| 3 | 0 | 65.121.191.46 | 89 |
| 8 | 0 | 12.228.139.218 | 99 |
| 14 | 0 | 12.228.90.64 | 121 |
| 10 | 0 | 206.253.218.122 | 117 |
| 11 | 5 | 206.253.218.123 | 117 |
| 15 | 5 | www.interactnetworks.com | 123 | <--
| 19 | 0 | 192.168.30.1 | 123 | <--
| 17 | 0 | 207.13.196.235 | 125 |
+---------+-----------+--------------------------+-----------+
mysql> SELECT InteractV2.Job_Queue.TargetRange AS TargetIP,
-> INET_NTOA(Swordfish.scanner.ipaddr) AS ScannerIP
-> FROM InteractV2.Job_Queue, Swordfish.scanner
-> WHERE Swordfish.scanner.scanner_id =
InteractV2.Job_Queue.ScannerID
-> AND InteractV2.Job_Queue.CompanyID = '123';
+--------------------------+----------------+
| TargetIP | ScannerIP |
+--------------------------+----------------+
| www.interactnetworks.com | 66.150.172.129 |
+--------------------------+----------------+
mysql> SELECT InteractV2.Job_Queue.TargetRange AS TargetIP,
-> INET_NTOA(Swordfish.scanner.ipaddr) AS ScannerIP
-> FROM InteractV2.Job_Queue, Swordfish.scanner
-> WHERE Swordfish.scanner.scanner_id =
InteractV2.Job_Queue.ScannerID
-> OR InteractV2.Job_Queue.ScannerID = 0
-> AND Swordfish.scanner.scanner_id <>
InteractV2.Job_Queue.ScannerID
-> AND InteractV2.Job_Queue.CompanyID = '123';
+--------------------------+-----------------+
| TargetIP | ScannerIP |
+--------------------------+-----------------+
| 206.253.218.123 | 66.150.172.129 |
| www.interactnetworks.com | 66.150.172.129 |
| 192.168.30.1 | 66.150.172.129 |
[snipped for brevity sake]
| 192.168.30.1 | 192.168.10.70 |
| 192.168.30.1 | 192.168.10.80 |
| 192.168.30.1 | 192.168.25.4 |
+--------------------------+-----------------+
29 rows in set (0.00 sec)
Other useful information:
select scanner_id, ipaddr, inet_ntoa(ipaddr) as scanner_ip from
Swordfish.scanner order by scanner_id;
+------------+------------+-----------------+
| scanner_id | ipaddr | scanner_ip |
+------------+------------+-----------------+
| 5 | 1117170817 | 66.150.172.129 |
[snipped for brevity sake]
| 23 | 3232238150 | 192.168.10.70 |
| 25 | 3232238160 | 192.168.10.80 |
| 26 | 3232241924 | 192.168.25.4 |
+------------+------------+-----------------+
27 rows in set (0.00 sec)
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
