List:Brisbane MySQL Users Group« Previous MessageNext Message »
From:Mark Unwin Date:August 22 2005 1:22am
Subject:RE: Followup - Combining & Sorting Results
View as plain text  
Yeah - there really isn't anything in common.
I was just after an easy way to list all devices, from both tables, by
(combined) IP Address..... 
Maybe I'll use two queries, dump them to an array (in PHP), and sort
that...

Mark.

-----Original Message-----
From: Arjen Lentz [mailto:arjen@stripped] 
Sent: Monday, August 22, 2005 10:23 AM
To: Mark Unwin
Cc: MySQL Users Group - Brisbane
Subject: Re: Followup - Combining & Sorting Results

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