List:General Discussion« Previous MessageNext Message »
From:Diana Soares Date:January 21 2003 5:48pm
Subject:Re: Help with a select where an ID isn't in another table, but is a
valid row.
View as plain text  
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

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