Sql-server – How to query based on related table without returning records from related table

sql server

Table_A

^ID^Name^
|1 |Joe |
|2 |Jane|

Table_B

^ID^Foreign_ID^Company  ^Specialty^
|1 |1         |Microsoft|SQL      |
|2 |1         |Microsoft|PHP      |
|3 |2         |Oracle   |SQL      |
|4 |2         |Oracle   |HTML5    |

The query:

SELECT Name, Company
FROM   Table_A
         LEFT JOIN Table_B
         ON Table_A.ID = Table_B.Foreign_ID
WHERE  Table_B = 'Microsoft'

Say I'm formatting a report, and I'm only interested in employee's that work at Microsoft and I only want 1 record per employee, how do I prevent the Speciality column in Table_B from creating multiple rows?

Best Answer

You don't need a join, since you have no columns from table_B in the output. You could use a semi-join, i.e an EXISTS subquery:

SELECT a.Name, 'Microsoft' AS Company
FROM Table_A AS a
WHERE EXISTS 
      ( SELECT *
        FROM Table_B AS b
        WHERE a.ID = b.Foreign_ID
          AND b.Company = 'Microsoft'
      ) ;

If you did want some other column from table_B (say the Specialty) and still only 1 row, you could use either a derived table with a window function, or CROSS APPLY with TOP 1. CROSS APPLY is a special join where we can reference previous tables. Note that it's similar to INNER JOIN (you had LEFT JOIN while you need INNER JOIN):

SELECT a.Name, 'Microsoft' AS Company, b.Specialty
FROM Table_A AS a
  CROSS APPLY 
    ( SELECT TOP (1) bi.Specialty 
      FROM Table_B AS bi
      WHERE a.ID = bi.Foreign_ID
        AND b.Company = 'Microsoft'
      ORDER BY Specialty 
    ) AS b ;
Related Question