barryleajo barryleajo - 2 months ago 9
SQL Question

Can't join three tables and return all records from just one

I am using, but very new to, SQL Server Express 2012 and SS Management Studio and databases in general.
Despite much time and research, I cannot quite achieve my goal with a particular stored procedure and can't move on without some advice.

I want to retrieve data from three related Tables A, B and C.
I want to have all rows from TableA and only the relevant rows from tables TableB and TableC (if they exist).

I found this link on StackOverflow which was the closest match to my problem, which I have tried without success. The code snippet is as follows:

SELECT A.*
FROM
(TableA A LEFT JOIN TableB B ON A.id = B.a_id)
LEFT JOIN TableC C ON A.id = C.a_ID


Now, this code has the related "ID" fields for TableB (B.a_id) and TableC (C.a_id) BOTH related to TableA (A.id).

In my case, TableA is related to TableB and TableC is related to TableB. It is all records from TableA that I want returned with just a couple of 'descriptive' fields from the other two tables for each of the TableA records.
I am returning more records than I require so how do I filter the output to just the records in TableA?

Can anyone suggest what I should do to resolve this please.

IMPORTANT EDIT

Sorry everyone - just to clarify from reading your excellent responses..

I JUST want to return ALL rows in TableA ONLY. I am including TableB and TableC just to return appropriate columns for each of these rows.

FURTHER INFO EDIT

In simple terms my context is similar to this:

TableA is a table of Employees. It has two columns: EmployeeID, EmployeeName

TableB is a table of Depts. It has three columns DeptID, DeptName, EmployeeID

TableC is a table of Firms. It has three columns FirmID, FirmName, DeptID

I want a list of all Employees (i.e. all rows in TableA) to put into a listbox (in Excel VBA) from which the user can select. In the listbox I would want to show the user respective columns from TableB and TableC:

EmployeeID, EmployeeName, FirmName, DeptName

I am looking for the logic and query code to achieve this

Answer

When you join tables, you won't get records from different tables in separate rows, you fetch data from all the tables into the same result row.

Just include the fields that you want from the tables in the SELECT:

SELECT
  A.*,
  B.Something,
  C.Something
FROM
  TableA A
  LEFT JOIN TableB B ON A.id = B.a_id
  LEFT JOIN TableC C ON A.id = C.a_ID

Note that you will always get a value for the TableB and TableC tables, i.e. the result is always the same number of columns. If there is no corresponding records from TableB or TableC, that field in the result will be null.

(Note also; using * in the SELECT is not good practice for actual application code. You should specify the fields that you want from the A table in the final code.)

Edit:

If you have more than one record in TableB or TableC for each record in TableA, and still only want one record in the result, you have to decide what to do with the multiple values from TableB or TableC.

To handle that you would group on the values from TableA and use aggregates on the values from TableB and TableC. For example getting the sum of the Count fields from TableA and the average of the Price fields from TableB:

SELECT
  A.Id,
  A.Name,
  SUM(B.Count) TotalCount,
  AVG(C.Price) AveragePrice
FROM
  TableA A
  LEFT JOIN TableB B ON A.id = B.a_id
  LEFT JOIN TableC C ON A.id = C.a_ID
GROUP BY
  A.Id,
  A.Name