Join select queries on an id in SQL Server

Question

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..

Answer 1

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;