Hi,
I didn't test this.
Assuming that Swordfish.scanner.scanner_id <> 0 for all rows (and
that's why it doesn't appear in the results list from the first query):
SELECT InteractV2.Job_Queue.TargetRange AS TargetIP,
IFNULL(INET_NTOA(Swordfish.scanner.ipaddr),'ANY SCANNER') AS ScannerIP
FROM InteractV2.Job_Queue
LEFT JOIN Swordfish.scanner
ON Swordfish.scanner.scanner_id = InteractV2.Job_Queue.ScannerID
WHERE InteractV2.Job_Queue.CompanyID = '123';
Note the LEFT JOIN and the IFNULL.
Hope it works!
On Tue, 2003-01-21 at 08:14, Daevid Vincent wrote:
> 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)
>
--
Diana Soares
