SQL Get specific columns from one table and all rows from a joined table in one query
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
;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
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...
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
精彩评论