CODEWITHSUNDEEP
mysqljoinmany-to-manyselect-query
matimod
matimod

Reputation: 13

Difficulty with unusual many to many select query

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

Upvotes: 0

Views: 42

Answers (2)

Tom
Tom

Reputation: 6663

You can do this. You can use a CROSS JOIN to get all entries from the software table, then do a LEFT JOIN to the computer_has_software table WHERE software_idsoftware IS NULL to get software that does not exist for that computer.

SELECT  s.idsoftware, s.name

FROM    computer as c

        CROSS JOIN software as s

        LEFT JOIN computer_has_software as chs 
        ON s.idsoftware = chs.software_idsoftware
        AND c.idcomputer = chs.computer_idcomputer

WHERE   chs.software_idsoftware IS NULL
        and c.name = 'komp001';

sqlfiddle

Upvotes: 1

Alex
Alex

Reputation: 17289

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

Upvotes: 0

Related Questions