List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:January 21 2003 8:14am
Subject:Help with a select where an ID isn't in another table, but is a valid row.
View as plain text  
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)

Thread
Help with a select where an ID isn't in another table, but is a valid row.Daevid Vincent21 Jan
  • Re: Help with a select where an ID isn't in another table, but is avalid row.Diana Soares21 Jan