6 Replies - 5379 Views - Last Post: 28 August 2008 - 12:03 PM

#1 gm04030276   User is offline

  • New D.I.C Head
  • member icon

Reputation: 2
  • View blog
  • Posts: 21
  • Joined: 06-January 08

Show the difference in two fields from two tables MySQL

Post icon  Posted 28 August 2008 - 06:04 AM

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
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! :)

Graham

Is This A Good Question/Topic? 0
  • +

Replies To: Show the difference in two fields from two tables MySQL

#2 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7164
  • View blog
  • Posts: 14,932
  • Joined: 16-October 07

Re: Show the difference in two fields from two tables MySQL

Posted 28 August 2008 - 06:18 AM

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

e.g.
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:
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.
Was This Post Helpful? 1
  • +
  • -

#3 gm04030276   User is offline

  • New D.I.C Head
  • member icon

Reputation: 2
  • View blog
  • Posts: 21
  • Joined: 06-January 08

Re: Show the difference in two fields from two tables MySQL

Posted 28 August 2008 - 07:59 AM

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 :(

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

the create table statements to show their exact structure
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!)
Was This Post Helpful? 0
  • +
  • -

#4 mocker   User is offline

  • D.I.C Regular
  • member icon

Reputation: 51
  • View blog
  • Posts: 466
  • Joined: 14-October 07

Re: Show the difference in two fields from two tables MySQL

Posted 28 August 2008 - 08:30 AM

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
Was This Post Helpful? 0
  • +
  • -

#5 gm04030276   User is offline

  • New D.I.C Head
  • member icon

Reputation: 2
  • View blog
  • Posts: 21
  • Joined: 06-January 08

Re: Show the difference in two fields from two tables MySQL

Posted 28 August 2008 - 08:37 AM

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
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
Was This Post Helpful? 0
  • +
  • -

#6 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7164
  • View blog
  • Posts: 14,932
  • Joined: 16-October 07

Re: Show the difference in two fields from two tables MySQL

Posted 28 August 2008 - 08:53 AM

If you've got dups then something is screwing up somewhere. Consider putting a constraint and the table and finding out who:
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


Was This Post Helpful? 0
  • +
  • -

#7 gm04030276   User is offline

  • New D.I.C Head
  • member icon

Reputation: 2
  • View blog
  • Posts: 21
  • Joined: 06-January 08

Re: Show the difference in two fields from two tables MySQL

Posted 28 August 2008 - 12:03 PM

Oooo what a fun little thing! 'UNIQUE()'
Its fun to learn new things! Especially in SQL!
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1