Difficulty with unusual many to many select query

Question

I've just started to learn MySQL. I've been researching this for a few hours, unfortunately I can't find a solution. I suspect this is pretty easy, I just don't know how to do it. I haven't been able to find a similar situation here or on other sites.

Here is some example data

computer
+------------------+
idcomputer | name
1          | komp001
2          | komp002 

computer_has_software
+---------------------------------------+
computer_idcomputer | software_idsoftware
1                   | 1
1                   | 2

software
+------------------+
idsoftware | name
1          | notepad
2          | eclipse
3          | firefox
4          | google chrome

As you can see komp001 has both notepad and eclipse installed.

I want a query that will tell me what software is available, but not installed on komp001 - i.e. all software not on komp001.

I would expect the answer to be firefox and google chrome.

software
+------------------------+
idsoftware | name
3          | firefox
4          | google chrome

Answer 1

http://sqlfiddle.com/#!9/2914a/3

SELECT
s.*
FROM 
  software as s
LEFT JOIN 
  (SELECT
     *
   FROM
     computer_has_software
   WHERE
     computer_idcomputer = 1
  ) as chs
ON 
  s.idsoftware = chs.software_idsoftware
WHERE
  chs.software_idsoftware IS NULL