Join select queries on an id in SQL Server I have three tables that SELECT from and I'd need to join the results.

It feels like this should be a rather easy task but I struggle with the syntax.

Working on Microsoft SQL Server Enterprise Edition v9.00.3042.00, i.e. SQL Server 2005 Service Pack 2

What I came up with so far is..


TableA has id (int), name (varchar), comment (varchar) and extra (varchar).
TableB has id (int) and char (varchar)
TableC has eid (int) and info (varchar).


SQL query:

SELECT
    b.count, a.id, a.name, a.comment 
FROM
    (SELECT * 
     FROM TableA a
     WHERE id + 100 IN (SELECT distinct(eid)
                        FROM TableC
                        WHERE NOT eid IS NULL)
    )
JOIN
   (SELECT 
        char, count(*) AS count 
    FROM TableB b sel 
    GROUP BY char) on a.id + 100 = b.char


What I'd like to have is the output of the top select (TableA) with the count of TableB where a.id+100 is b.char

Excuse my amateur-ish sql..
If I understand correctly, I think this should work...

We're checking that TableA.id+100 is in TableC.eid and then counting the number of times TableA.id+100 appears as TableB.char.

It's always good to create a SQLFiddle though so that others can easily pick up your schema and code to debug.

SELECT
  a.id,
  a.name,
  a.comment,
  COUNT(b.char) AS count
FROM TableA a
INNER JOIN TableB b 
  ON a.id+100 = b.char
INNER JOIN TableC 
  ON a.id+100 = c.eid
GROUP BY a.id, a.name, a.comment;


As Aaron Bertrand pointed out below, using EXISTS would help to eliminate double counting on where duplicate eids appear in  TableC.

SELECT
      a.id,
      a.name,
      a.comment,
      COUNT(b.char) AS count
FROM TableA a
INNER JOIN TableB b 
  ON a.id+100 = b.char
WHERE EXISTS (SELECT * FROM TableC where eid = a.id+100)
GROUP BY a.id, a.name, a.comment;