Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
IvanJAdv
Starting Member
2 Posts |
 Posted - 2011-07-21 : 21:40:38
|
| Let's say I have 2 tables:"Student"ID, Name1, Ivan2, Tom"Data"ID, Data1, blah1, blah2What I want to accomplish is to pull all the records from the "Student" table, but pull no more than 1 corresponding record from the "Data" table. The result I want to end up with is:"Result"ID, Name, Data1, Ivan, blah (or blah 2, don't care which)2, Tom, null...how do I go about this? I used to think full outer join would get me there, but looks like I'm wrong.Thanks! |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-07-21 : 22:08:54
|
try1. use LEFT JOIN to Data table 2. use GROUP BY column ID, NAME and applied aggregate function (min or max) on column Data
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-07-22 : 10:47:24
|
| [code]SELECT s.ID,s.Name, d.MinDataFROM (SELECT ID,MIN(Data) AS MinData FROM Data GROUP BY ID) dLEFT JOIN Student sON s.ID = d.ID[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
IvanJAdv
Starting Member
2 Posts |
Posted - 2011-07-22 : 10:58:11
|
| Thanks that helps! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-07-22 : 11:05:12
|
| wc------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
Simple Q: How to join, without duplicating left?