List:Brisbane MySQL Users Group« Previous MessageNext Message »
From:Paul Campbell Date:August 22 2005 12:58am
Subject:Re[2]: Followup - Combining & Sorting Results
View as plain text  
Hi Mark,
It seems to me that you are storing info about assets on your network.

Correct me if I am wrong. If you step back a bit you might actually be
able to structure your tables differently and come up with something
that will do the job for a long time to come

At this time you have 2 tables with the following info to be extracted

> Table 1
> IP Address
> Device Type
> Device Name
> Table 2
> IP Address
> System Name
> OS Type

Now if you restructure your tables to this it might free you up


asset_id               PK (you could make this ip address depending if you wanted to
                       track multiple networks. ie.a company has 20 branches and
                       they all use private networks in the range.)
asset_description      eg. Jane's Computer, printer in tea room  etc
                           etc etc
asset_location         Rm10    (Putting location here takes more
                       storage room in the db but gives you a history
                       of where the asset has been in it's lifetime.
                       You would have to tie this in with the PK above
                       or use a joining table. I'll leave that for
asset_type_id          FK

Table_Two    (a lookup table)

asset_type_id          PK
asset_type_description eg.router, switch, hub, computer, printer,(the
                       beauty of this method is that an asset can be anything you
                       like...people, cars, projectors, books)don't be
                       worried about storing srtange things in your
                       asset db. You are only storing the use of the
                       object, not the object itself ie. Fred knows
                       C++ so you have one asset of C++ and if you
                       structure it right you can then convert the use
                       of this table into a project time management
                       thing without too much trouble.)

I hope I haven't gone too far left field for you Mark, stuff like this
flies into my head every so often. Let me know if any of this helps
you out

Paul Campbell

-----Original Messsage-----
Arjen> Hi Mark,

Arjen> On Thu, 2005-08-18 at 13:22, Mark Unwin wrote:
>> Following on from the post on the 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 ?

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

Arjen> Regards,
Arjen> Arjen.

>> Arjens response:
>> -------------------------------------
>> Sure. First of all, you can store an IPv4 address as an INT UNSIGNED,
>> INSERT with INET_ATON("").
>> 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. (
> )
>> 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> -- 
Arjen> Arjen Lentz, Community Relations Manager
Arjen> MySQL AB,

Arjen> MySQL related blogs @

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