SQL Server Multiple table joins with a WHERE clause

Question

I m using SQL Server and I m having a difficult time trying to get the results from a SELECT query that I want  I ve tried joining in different orders and using subqueries but nothing quite works the way I want  Take this contrived example of software applications  with different version levels  that might be installed on peoples computers    I need to perform a JOIN with a WHERE  but for some reason I can t get the results I want    Maybe I m looking at my data wrong  I m not quite sure why I can t get this to work   Application table     ID  Name 1   Word 2   Excel 3   Powerpoint   Software Table  contains version information for different applications           ID  ApplicationID   Version 1   1             2003 2   1             2007 3   2             2003 4   2             2007 5   3             2003 6   3             2007   Software Computer junction table          ID  SoftwareID  ComputerID 1   1           1 2   4           1 3   2           2 4   5           2   Computer table    ID  ComputerName 1   Name1 2   Name2   I want a query that I could run where I select a specific computer to display what software version and application is has  but I also want it to display what application it does not have the version would be a NULL since it doesn t have that software on it   SELECT Computer ComputerName  Application Name  Software Version FROM Computer JOIN Software Computer     ON Computer ID   Software Computer ComputerID JOIN Software     ON Software Computer SoftwareID   Software ID RIGHT JOIN Application     ON Application ID   Software ApplicationID WHERE Computer ID   1    I want the following result set  ComputerName   Name          Version Name1          Word          2003 Name1          Excel         2007 Name1          Powerpoint    NULL   But I just get   Results ComputerName   Name          Version Name1          Word           2003 Name1          Excel          2007   I thought the RIGHT JOIN would include all the results in the application table  even if they aren t associated with the computer  What am I missing doing wrong

User · Answer

SELECT p Name  v Name FROM Production Product p JOIN Purchasing ProductVendor pv ON p ProductID   pv ProductID JOIN Purchasing Vendor v ON pv BusinessEntityID   v BusinessEntityID WHERE ProductSubcategoryID   15 ORDER BY v Name

User · Answer

When using LEFT JOIN or RIGHT JOIN  it makes a difference whether you put the filter in the WHERE or into the JOIN   See this answer to a similar question I wrote some time ago  What is the difference in these two queries as getting two different result set   In short    if you put it into the WHERE clause  like you did  the results that aren t associated with that computer are completely filtered out if you put it into the JOIN instead  the results that aren t associated with that computer appear in the query result  only with NULL values --  this is what you want

User · Answer

try this  DECLARE  Application TABLE Id INT PRIMARY KEY  NAME VARCHAR 20   INSERT  Application   Id  NAME   VALUES    1  Word       2  Excel       3  PowerPoint    DECLARE  software TABLE Id INT PRIMARY KEY  ApplicationId INT  Version INT  INSERT  software   Id  ApplicationId  Version   VALUES    1 1  2003      2 1 2007      3 2  2003      4 2 2007     5 3  2003      6 3 2007    DECLARE  Computer TABLE Id INT PRIMARY KEY  NAME VARCHAR 20   INSERT  Computer   Id  NAME   VALUES    1  Name1       2  Name2     DECLARE  Software Computer  TABLE Id INT PRIMARY KEY  SoftwareId int  ComputerId int  INSERT  Software Computer   Id  SoftwareId  ComputerId   VALUES    1 1  1      2 4 1      3 2  2      4 5 2    SELECT Computer Name ComputerName  Application Name ApplicationName  MAX Software2 Version  Version FROM  Application Application  JOIN  Software Software     ON Application ID   Software ApplicationID CROSS JOIN  Computer Computer LEFT JOIN  Software Computer Software Computer     ON Software Computer ComputerId   Computer Id AND Software Computer SoftwareId   Software Id LEFT JOIN  Software Software2     ON Software2 ID   Software Computer SoftwareID WHERE Computer ID   1  GROUP BY Computer Name  Application Name

User · Answer

SELECT Computer Computer Name  Application1 Name  Max Soft  Version   as Version1 FROM Application1 inner JOIN Software     ON Application1 ID   Software Application Id cross join Computer Left JOIN Software Computer     ON Software Computer Computer Id   Computer ID and Software Computer Software Id   Software Id Left JOIN Software as Soft     ON Soft Id   Software Computer Software Id WHERE Computer ID   1  GROUP BY Computer Computer Name  Application1 Name

User · Answer

Try this working fine      SELECT computer NAME  application NAME software Version FROM computer LEFT JOIN software computer ON computer ID   software computer ComputerID   LEFT JOIN software ON software computer SoftwareID   Software ID  LEFT JOIN application ON application ID   software ApplicationID    where computer id   1 group by application NAME UNION SELECT computer NAME  application NAME   NULL as Version FROM computer  application WHERE application ID not in   SELECT s applicationId FROM software computer sc LEFT JOIN software s   on s ID   sc SoftwareId WHERE sc ComputerId   1     AND computer id   1

User · Answer

The third row you expect  the one with Powerpoint  is filtered out by the Computer ID   1 condition  try running the query with the Computer ID   1 or Computer ID is null it to see what happens    However  dropping that condition would not make sense  because after all  we want the list for a given Computer   The only solution I see is performing a UNION between your original query and a new query  that retrieves the list of application that are not found on that Computer    The query might look like this   DECLARE  ComputerId int SET  ComputerId   1  -- your original query SELECT Computer ComputerName  Application Name  Software Version     FROM Computer     JOIN dbo Software Computer         ON Computer ID   Software Computer ComputerID     JOIN dbo Software         ON Software Computer SoftwareID   Software ID     RIGHT JOIN dbo Application         ON Application ID   Software ApplicationID     WHERE Computer ID    ComputerId  UNION  -- query that retrieves the applications not installed on the given computer SELECT Computer ComputerName  Application Name  NULL as Version FROM Computer  Application WHERE Application ID not in                SELECT s ApplicationId         FROM Software Computer sc         LEFT JOIN Software s on s ID   sc SoftwareId         WHERE sc ComputerId    ComputerId       AND Computer id    ComputerId

User · Answer

You need to do a LEFT JOIN   SELECT Computer ComputerName  Application Name  Software Version FROM Computer JOIN dbo Software Computer     ON Computer ID   Software Computer ComputerID LEFT JOIN dbo Software     ON Software Computer SoftwareID   Software ID RIGHT JOIN dbo Application     ON Application ID   Software ApplicationID WHERE Computer ID   1      Here is the explanation      The result of a left outer join  or simply left join  for table A and   B always contains all records of the  left  table  A   even if the   join-condition does not find any matching record in the  right  table    B   This means that if the ON clause matches 0  zero  records in B    the join will still return a row in the result   but with NULL in each   column from B  This means that a left outer join returns all the   values from the left table  plus matched values from the right table    or NULL in case of no matching join predicate   If the right table   returns one row and the left table returns more than one matching row   for it  the values in the right table will be repeated for each   distinct row on the left table  From Oracle 9i onwards the LEFT OUTER   JOIN statement can be used as well as

User · Answer

select C ComputerName  S Version  A Name    from Computer C inner join Software Computer SC      on C Id   SC ComputerId    Inner join Software S      on SC SoftwareID   S Id    Inner join Application A       on S ApplicationId   A Id

[sql-server] SQL Server: Multiple table joins with a WHERE clause

Examples related to sql-server

Examples related to join

Examples related to where