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/

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.