PHP select from 3 tables

Question

I have 3 database tables: site, paper and linkTable.

 idSite     SiteName  
 --------------------
   1           AAA
   2           BBB
   3           CCC


idPaper     PaperName
------------------------
   1           Paper1
   2           Paper2
   3           Paper3
   4           Paper4


idLinkTable    idSite   idPaper
----------------------------------
     1           1         1
     2           1         3
     3           2         1

I want to show all the papers without idPaper=1,idPaper=3 for idSite=1; Similarly for idSite=2, need all the papers without idPaper=1. This is the required output:

SiteName  PaperName
------------------------
   AAA         Paper2
   AAA         Paper4
   BBB         Paper2
   BBB         Paper3
   BBB         Paper4

I have tried this code:

SELECT s.SiteName AS Site, p.PaperName AS Paper
 FROM site s 
INNER JOIN linkTable l ON s.idSite = l.idSite 
INNER JOIN paper p ON l.idPaper != p.idPaper;

This is my result when i am using this code:

SiteName  PaperName
------------------------
   AAA         Paper2
   AAA         Paper3
   AAA         Paper4
   AAA         Paper1
   AAA         Paper2
   AAA         Paper4
   BBB         Paper2
   BBB         Paper3
   BBB         Paper4

Answer 1

You need all possible combinations between Site and Paper tables, except the ones which already exist in the linkTable. Use CROSS JOIN to get all possible combinations, and NOT EXISTS to eliminate the results already existing in the linkTable. Try the following:

SELECT s.SiteName AS Site, p.PaperName AS Paper
FROM site s 
CROSS JOIN paper p 
WHERE NOT EXISTS ( SELECT idLinkTable FROM linkTable 
                   WHERE linkTable.idSite = s.idSite 
                     AND linkTable.idPaper = p.idPaper
                 )