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:
(TableA A LEFT JOIN TableB B ON A.id = B.a_id)
LEFT JOIN TableC C ON A.id = C.a_ID
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 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
TableC tables, i.e. the result is always the same number of columns. If there is no corresponding records from
TableC, that field in the result will be
(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.)
If you have more than one record in
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
To handle that you would group on the values from
TableA and use aggregates on the values from
TableC. For example getting the sum of the
Count fields from
TableA and the average of the
Price fields from
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