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 --------------------------------------------------------------------- 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
