SQL Server Multiple table joins with a WHERE clause

0 votes

I'm trying to run a SELECT query using SQL Server, but I'm having trouble getting the desired results. I've tried utilising subqueries and joining in various orders, but nothing exactly works the way I want. Consider the fabricated example of software programs that may be installed on users' machines and have various version levels.

I have to do a JOIN with a WHERE, but I can't seem to get the outcomes I need. Maybe I'm interpreting my data incorrectly; I'm not entirely sure why I can't make this 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

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?

Sep 17 in Database by Kithuzzz
• 20,660 points

1 answer to this question.

0 votes

It matters whether you place the filter in the WHERE or the JOIN when using LEFT JOIN or RIGHT 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?

I hope this helps you. 

answered Sep 18 by narikkadan
• 37,660 points

Related Questions In Database

0 votes
0 answers

How to DROP multiple columns with a single ALTER TABLE statement in SQL Server?

In one ALTER TABLE statement, I would ...READ MORE

Sep 2 in Database by Kithuzzz
• 20,660 points
0 votes
0 answers

How to find sum of multiple columns in a table in SQL Server 2005?

I have a table Emp which has these rows: Emp_cd ...READ MORE

Aug 19 in Database by Kithuzzz
• 20,660 points
0 votes
0 answers

Backup a single table with its data from a database in sql server 2008

I want to use a script to ...READ MORE

Aug 29 in Database by Kithuzzz
• 20,660 points
0 votes
0 answers
0 votes
0 answers

SQL Update from One Table to Another Based on a ID Match

Account and credit card numbers are stored ...READ MORE

Aug 22 in Database by Kithuzzz
• 20,660 points
0 votes
1 answer

Join multiple strings in R

Joining strings in R is quite an ...READ MORE

answered Jul 19, 2018 in Data Analytics by DataKing99
• 8,240 points
0 votes
1 answer

IN vs OR in the SQL WHERE Clause

I assume you want to know the ...READ MORE

answered Sep 24, 2018 in Database by DataKing99
• 8,240 points
0 votes
1 answer

Calculate Time Intersection to Correlate Sequences of Independent Events

I think this solution requires a CROSS JOIN implementation. ...READ MORE

answered Oct 26, 2018 in Power BI by Upasana
• 8,620 points
0 votes
1 answer

Add a column with a default value to an existing table in SQL Server


answered Sep 12 in Database by narikkadan
• 37,660 points
0 votes
1 answer

Login failed for user 'sa'. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452) in sql 2008

Solution Go to Start > Programs > Microsoft SQL Server > Enterprise Manager. Right-click the SQL ...READ MORE

answered Sep 12 in Database by narikkadan
• 37,660 points
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP