Programming Help

Web Development



What's Here?

  • Members: 132,396
  • Replies: 471,058
  • Topics: 73,027
  • Snippets: 2,540
  • Tutorials: 666
  • Total Online: 1,231
  • Members: 66
  • Guests: 1,165

Who's Online?

Welcome to Dream.In.Code
Getting Help is Easy!

Join 132,396 Programmers for FREE! Get instant access to thousands of experts, tutorials, code snippets, and more! There are 1,231 people online right now. Registration is fast and FREE... Join Now!




Show the difference in two fields from two tables MySQL

 
 Reply to this topicStart new topic

Show the difference in two fields from two tables MySQL
gm04030276
post 28 Aug, 2008 - 05:04 AM
Post #1


New D.I.C Head

*
Joined: 6 Jan, 2008
Posts: 14


My Contributions
I have a little program that lifts all the installed software on windows computers and dump is to my linux server to be sorted and put in a database. I used some regex to lift out various updates and put them in a different table because i don't want them in the software table! Anyway, it seems to have gone a bit wrong because I have 332 out of 203 updates showing on some computers. The known updates are got from data from all the computers. So...question, i have two tables,
installed_updates (id, computer, update) and known_updates (update)
what sort of fun SQL query do I need run to see what ones are in installed_updates (update) that aren't in known_updates(update) for a given computer.
I tried
CODE
SELECT * FROM `installed_updates` WHERE `computer`='CP010' AND `update`!=`known_updates`.`update`;

It didn't work...I didn't really expect it to!

Help muchly appreciated! smile.gif

Graham
User is offlineProfile CardPM

Go to the top of the page

baavgai
post 28 Aug, 2008 - 05:18 AM
Post #2


Dreaming Coder

Group Icon
Joined: 16 Oct, 2007
Posts: 1,962



Thanked 96 times

Dream Kudos: 475

Expert In: C, C++, Java, C#, ASP.NET, PHP, Perl, Python, Oracle, SQL Server, MySql, HTML, JavaScript, Lua

My Contributions
You have to name both tables before you can use them. You also need a point of connection between the two tables.

e.g.
CODE

SELECT *
    FROM installed_updates a
    left outer join known_updates b
        on a.update=b.update
    WHERE a.computer='CP010'


The query returns all values from installed_updates and all the records from known_updates that match (if my assumptions about your schema are correct.)

To get all the installed_updates that don't have known_updates, just filter out the records that didn't have a match:
CODE

SELECT a.*
    FROM installed_updates a
    left outer join known_updates b
        on a.update=b.update
    WHERE a.computer='CP010' and b.update is null


Note, you will get funky results if known_updates.update is not unique.

Hope this helps.
User is offlineProfile CardPM

Go to the top of the page

gm04030276
post 28 Aug, 2008 - 06:59 AM
Post #3


New D.I.C Head

*
Joined: 6 Jan, 2008
Posts: 14


My Contributions
I assumed I would have to do some table joining but I so very rarely do it that i couldn't remember how! Unfortunately it didn't work sad.gif

I'll give you a bit more detail see if you notice anything.

the create table statements to show their exact structure
CODE

CREATE TABLE `installed_updates` (
`ID` INT(11) UNSIGNED NOT NULL PRIMARY KEY,
`computer` VARCHAR(5) NOT NULL,
`update` VARCHAR(100) NOT NULL
) ENGINE = MYISAM

CREATE TABLE `known_updates` (
`update` VARCHAR(100) NOT NULL PRIMARY KEY
) ENGINE = MYISAM


The known updates are inserted when a line in one of the files is identified as an update and it is also put in the installed_updates with its relevant computer ID no. So known_updates should be a combination of all the updates from all the files but it wont add duplicates because it is the primary key and mysql will fail with an error when you do try (which is what we want!)
User is offlineProfile CardPM

Go to the top of the page

mocker
post 28 Aug, 2008 - 07:30 AM
Post #4


D.I.C Regular

Group Icon
Joined: 14 Oct, 2007
Posts: 256



Thanked 15 times
My Contributions
baavgai's query should work for that, what error did you get?

I typically use isnull(b.update) instead of "b.update is null", I believe they mean the same though
User is offlineProfile CardPM

Go to the top of the page

gm04030276
post 28 Aug, 2008 - 07:37 AM
Post #5


New D.I.C Head

*
Joined: 6 Jan, 2008
Posts: 14


My Contributions
No error, just nothing came up! .... unless its putting the same thing in several times to the installed updates table...ill look now...
yip...theres our problem...it seems to have doubled the input.
Tried
CODE
SELECT DISTINCT `update` FROM `installed_updates` WHERE `computer`='CP010';
and it came up with 116 results. Twice that (332) is what it's telling me it has installed...weird. Ok, I'll just mod the query to ask for distinct ones instead, should solve the problem! Thanks
User is offlineProfile CardPM

Go to the top of the page

baavgai
post 28 Aug, 2008 - 07:53 AM
Post #6


Dreaming Coder

Group Icon
Joined: 16 Oct, 2007
Posts: 1,962



Thanked 96 times

Dream Kudos: 475

Expert In: C, C++, Java, C#, ASP.NET, PHP, Perl, Python, Oracle, SQL Server, MySql, HTML, JavaScript, Lua

My Contributions
If you've got dups then something is screwing up somewhere. Consider putting a constraint and the table and finding out who:
CODE

CREATE TABLE `installed_updates` (
`ID` INT(11) UNSIGNED NOT NULL PRIMARY KEY,
`computer` VARCHAR(5) NOT NULL,
`update` VARCHAR(100) NOT NULL,
UNIQUE(`computer`, `update`)
) ENGINE = MYISAM
User is offlineProfile CardPM

Go to the top of the page

gm04030276
post 28 Aug, 2008 - 11:03 AM
Post #7


New D.I.C Head

*
Joined: 6 Jan, 2008
Posts: 14


My Contributions
Oooo what a fun little thing! 'UNIQUE()'
Its fun to learn new things! Especially in SQL!
User is offlineProfile CardPM

Go to the top of the page

Fast ReplyReply to this topicStart new topic
Time is now: 11/22/08 07:26AM
Invision Power Board v2.1.7 © 2008  IPS, Inc.
Licensed to: MediaGroup1

Live Help!

Tutorials

Programming

Web Development

Reference Sheets

Code Snippets

Bye Bye Ads

Free DIC T-Shirt

T-Shirt Example

Related Sites

Monthly Drawing

Thumb Drive

Partners

Top Contributors

Top 10 Kudos This Month