SQL Gurus

Cras · **Joined:** 30th Mar, 2008 **Posts:** 49237

I'm having a mental block, would appreciate assistance from any folks out there better with SQL than me.

Table one has a list of users and the applications they use. Table two has a list of these applications and whether or not they work on Vista.

For example

Code:

User        |      App
Frank       |  Word
Frank       |  Excel
Steve       |  Word
Steve       |  Outlook
Lisa        |  Excel
Lisa        |  Outlook


App           |     Working
Word          |       Yes
Excel         |       Yes
Outlook       |       No

What I want to know is which users have all their applications working on Vista. Which in this case should just give me Frank. I'm having a complete brainfart. Help please!

MrD · **Joined:** 30th Mar, 2008 **Posts:** 2139

Get all the user names that have all of their app counterparts in the two table listed as working?

Rephrase that: get all the user names that have none of their app counterparts in the two table listed as not working and you've got:

Code:

SELECT DISTINCT `one`.`user`
FROM `one`
WHERE `one`.`user` NOT
IN (

SELECT `one`.`user`
FROM `one`
LEFT JOIN `two` ON `one`.`app` = `two`.`app`
WHERE `two`.`working` = 'no'
GROUP BY `one`.`user`
)

"That'll be twenty dollars!!"

myp · **Posted:** Mon Jan 19, 2009 9:18

Code:

DELETE FROM App
WHERE User = Steve

Doctor Glyndwr · **Joined:** 30th Mar, 2008 **Posts:** 32624

MrD wrote:

"That'll be twenty dollars!!"

Look at all those backquotes! Someone's been bitten in the past with a whacky table or column name, I'm guessing?

Grim... · **Posted:** Mon Jan 19, 2009 10:27

If you like joins:

Code:

SELECT User.* FROM User
LEFT JOIN App on User.App = App.App AND App.Working = 'no'
WHERE 
App.App IS NULL

(assuming, obv, that the two tables are called App and User).

GazChap · **Posted:** Mon Jan 19, 2009 10:27

No harm in being thorough

MaliA · **Posted:** Mon Jan 19, 2009 10:34

A child of 8 could have worked that out, y'know

Cras · **Joined:** 30th Mar, 2008 **Posts:** 49237

MrD wrote:

Solution

Cheers for that, still having issues though. Won't a left join return every single username in table One, regardless if there are matching applications in table Two?

Grim... · **Posted:** Mon Jan 19, 2009 10:40

Craster wrote:

MrD wrote:

Solution

Cheers for that, still having issues though. Won't a left join return every single username in table One, regardless if there are matching applications in table Two?

Yes, right up until you kick all the non-null results out in the WHERE clause.

Cras · **Joined:** 30th Mar, 2008 **Posts:** 49237

Yay - it works, although it takes 3 and a half minutes to run.

Thanks folks!

GazChap · **Posted:** Mon Jan 19, 2009 11:49

Three and a half minutes? Perhaps stick some indexes on the name columns?

Grim... · **Posted:** Mon Jan 19, 2009 11:59

Or repair the tables.

MaliA · **Posted:** Mon Jan 19, 2009 12:04

Grim... wrote:

Or repair the tables.

If you had a table with 4 legs, and removed one, what would happen?

Cras · **Joined:** 30th Mar, 2008 **Posts:** 49237

They're just very large tables, and it's a SQL 2005 DB running on top of my Vista desktop - you'll forgive it for not being lightning fast.

MrD · **Joined:** 30th Mar, 2008 **Posts:** 2139

THAT'S AN ASSUMPTION.

Be Excellent To Each Other

SQL Gurus
Lend me your brains

Who is online

Be Excellent To Each Other

SQL Gurus Lend me your brains

Who is online

SQL Gurus
Lend me your brains