List:Brisbane MySQL Users Group« Previous MessageNext Message »
From:Mark Unwin Date:August 18 2005 3:22am
Subject:Followup - Combining & Sorting Results
View as plain text  
Following on from the post on the Meetup.com board - 

My initial post:
-------------------------------------
I have two tables. I wish to extract the following from the tables -

Table 1
IP Address
Device Type
Device Name

Table 2
IP Address
System Name
OS Type

Combine these results and sort the whole list by IP Address. Is this
possible ?

Mark. 
-------------------------------------

Arjens response:
-------------------------------------
Sure. First of all, you can store an IPv4 address as an INT UNSIGNED,
INSERT with INET_ATON("1.2.3.4").

Now for your query, it's a simple inner join. Can be done using a comma
but for readability I generally use another syntax which shows the join
condition:

SELECT INET_NTOA(t1.ip) AS ip
t1.device_type,t1.device_name,
t2.system_name,t2.os_type
FROM t1 JOIN t2 ON t1.ip = t2.ip
ORDER BY ip

(note that we're sorting by the decoded IP address) 
-------------------------------------

What if the two tables are not related ?
IE - if the IPAddress fields aren't foreign keys ?
I have a list of "PCs" and a list of "Network Items" - obviously the
IPAddresses are different between the two tables, how can I get a list
of all PCs & all Other Network Items, sorted by IP Address ?

Mark Unwin.


*************************************************************************************************
This message and any attachments, or any part
of it is intended solely for the named addressee.  

Reading, printing, distribution, storing, commercialising
or acting on this transmission or any information it contains, by anyone other than the
addressee, is prohibited. If you have received this message in error, please destroy all
copies and notify 
Qld Police Credit Union Ltd on +61 7 3008 4444 or by replying to the sender.

This message may contain legally privileged and
confidential information, and/or copyright material
of QPCU or third parties.

QPCU is not responsible for any changes made
to a document other than those made by QPCU,
or for the effect of the changes on the document's meaning.
You should only re-transmit, distribute or commercialise
the material if you are authorised to do so.

Any views expressed in this message are
those of the individual sender.  You may not rely on this message as advice unless
subsequently confirmed by fax or letter signed by an Officer or Director of QPCU, or 
an Authorised Representative QPCU.

QPCU advises that this e-mail and any attached files should be scanned to detect viruses. 
QPCU accepts no liability for loss or damage (whether caused by negligence or not)
resulting from the use of any attached files.

Information regarding Privacy can be found at the QPCU web site. ( www.qpcu.org.au )

General Advice Warning

Any advice has been prepared without taking into account your particular objectives,
financial situation or needs.  For that reason, before acting on the advice you should
consider the appropriateness of the advice having regard to your own objectives,
financial situation and needs.  Where the advice relates to the acquisition, or possible
acquisition, of a particular financial product, you should obtain a Product Disclosure
Statement relating to the product and consider the Product Disclosure Statement before
making any decision about whether to acquire the product.
*************************************************************************************************


Thread
Followup - Combining & Sorting ResultsMark Unwin18 Aug
  • Re: Followup - Combining & Sorting ResultsArjen Lentz22 Aug
    • Re[2]: Followup - Combining & Sorting ResultsPaul Campbell22 Aug
RE: Followup - Combining & Sorting ResultsMark Unwin22 Aug