List:Brisbane MySQL Users Group« Previous MessageNext Message »
From:Mark Unwin Date:August 22 2005 1:19am
Subject:RE: Re[2]: Followup - Combining & Sorting Results
View as plain text  
Thanks Paul,

  It's actually a lot more complex (the DB structure) - I was
simplifying it for an example. If you want to check out the DB
structure, download the software from here:
http:winventory.sourceforge.net .

Thanks again,
Mark Unwin. 

-----Original Message-----
From: Paul Campbell [mailto:paul@stripped] 
Sent: Monday, August 22, 2005 10:59 AM
To: Brisbane MySQL Mailing List
Subject: Re[2]: Followup - Combining & Sorting Results

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

Table_One

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 10.0.0.1
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
                       another time....lol.
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

--
Cheers,
Paul Campbell
 
mailto:paul@stripped

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

Arjen> 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 ?

Arjen> Looks to me like two separate queries.
Arjen> You could make it into a union, but it really makes little sense 
Arjen> since 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("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> --
Arjen> Arjen Lentz, Community Relations Manager MySQL AB, www.mysql.com

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





--
Brisbane MySQL Users Group Mailing List Mailing List For list archives:
http://lists.mysql.com/ug-brisbane
To unsubscribe:
http://lists.mysql.com/ug-brisbane?unsub=1



Thread
RE: Re[2]: Followup - Combining & Sorting ResultsMark Unwin22 Aug