List:Brisbane MySQL Users Group« Previous MessageNext Message »
From:Arjen Lentz Date:August 22 2005 12:23am
Subject:Re: Followup - Combining & Sorting Results
View as plain text  
Hi Mark,

On Thu, 2005-08-18 at 13:22, Mark Unwin wrote:
> 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 ?

Looks to me like two separate queries.
You could make it into a union, but it really makes little sense since
the two tables have nothing in common really.

Regards,
Arjen.


> 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.
>
> *************************************************************************************************
-- 
Arjen Lentz, Community Relations Manager
MySQL AB, www.mysql.com

MySQL related blogs @ http://www.planetmysql.org/


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