SQL Get specific columns from one table and all rows from a joined table in one query

Question

This may have been asked before and I just can't find it.

I have a one to many relationship in the database on a few tables.

• table1
• table2
• table3

table2 - table3 is the 1-many relationship

here's a mock of what I have:

select
table1.id
table1.Column
table2.Column2
-- I want all entries here from table 3 here as well
From table1 t1
left outer join table2 t2 on t2.ID = t1.ID
left outer join join table3 t3 on t3.ID2 = t2.ID2

Is it possible to also select all of the entries that belong to table3 in this query without specifying a sub-query in the select statement?

Also, does this look right? As I've said in the past I'm really new to SQL, thus my sucky code...

EDIT

Sorry guys I misspoke. I need a single column from each of the rows that should be in table3

select
table1.id,
table1.Column,
table2.Column2,
-- I'm going to need a subquery here aren't I...?
table3.columnFromRrow1,
table3.columnFromRrow2,
table3.columnFromRrow3
From table1 t1
left outer join table2 t2 on t2.ID = t1.ID
left outer join join table3 t3 on t3.ID2 = t2.ID2

Answer 1

I've modified (and corrected your query to do what you want).

SELECT 
  table1.id,
  table1.Column,
  table2.Column2, 
  table3.* -- All columns from table3
FROM table1 AS t1 
     LEFT OUTER JOIN table2 AS t2 
       ON t2.ID = t1.ID 
     LEFT OUTER JOIN table3 AS t3 
       ON t3.ID2 = t2.ID2 

NOTE: This answer is no longer valid, because the original question has been modified...

Answer 2

Using *

select
table1.id
table1.Column
table2.Column2
-- I want all entries here from table 3 here as well
table3.*
From table1 t1
left outer join table2 t2 on t2.ID = t1.ID
left outer join join table3 t3 on t3.ID2 = t2.ID2

Answer 3

;WITH cte AS
     ( SELECT table1.t1id,
              table1.t1col,
              table2.t2col,
              table3.t3col,
              ROW_NUMBER() OVER (PARTITION BY t1id,t1col,t2col 
                                 ORDER BY table3.id) AS RN
     FROM     table1 t1
              LEFT OUTER JOIN table2 t2
              ON       t2.ID = t1.ID
              LEFT OUTER JOIN
              JOIN table3 t3
              ON       t3.ID2 = t2.ID2
     )
SELECT 
   t1id,
   t1col,
   t2col,
   MAX(CASE WHEN RN=1 THEN t3col END) AS columnFromRrow1,
   MAX(CASE WHEN RN=2 THEN t3col END) AS columnFromRrow2,
   MAX(CASE WHEN RN=3 THEN t3col END) AS columnFromRrow3
FROM cte
WHERE RN<=3
GROUP BY t1id,t1col,t2col