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 |
|
Jaypoc
Yak Posting Veteran
58 Posts |
 Posted - 2008-11-04 : 18:26:30
|
| I have an issue I've been working on and I just can't seem to get the SQL right.I need to retrieve a list of object ID's from one table, and for each of them return any "review" information from another table (if data exists. If not it can return blanks or Nulls)SELECT DISTINCT id FROM users ORDER BY id(Returns all user id's numerically)SELECT TOP 1 id, date, review FROM reviews WHERE id = '1' ORDER BY date DESC(Returns the last (most recent) review for the user)I want to combine the results of these two tables giving me a list of ALL user IDs (from the users table) and the details of the one latest review.uid | date | review 1 | 9/7/2008 | Good 2 | <NULL> | <NULL> 3 | 8/3/2008 | GoodThanks |
|
|
Jaypoc
Yak Posting Veteran
58 Posts |
Posted - 2008-11-05 : 11:59:38
|
| here's the specific data and sample output I'm looking for. I know how to get it to return all reviews and give all the data, but not to return all user ID's and their review information (or nulls).USERS: ext int(4), -- The User ID name nvarchar, -- User's NameREVIEWS: ext int(4), -- The userID date smalldatetime, -- The Date reviewed nvarchar(1), -- Y or N review nvarchar(255), -- Review Notes==Sample DATA==USERS:6116 | Jason 6117 | Stephen6118 | JoanneREVIEWS:6116 | 2008-08-27 | Y | "Top Quality. Great work"6116 | 2008-09-10 | Y | "Missing Component G4"6116 | 2008-11-01 | N | <NULL>I'd like the query to return each User ID (ext) with the most recent review (by date)6116 | 2008-11-01 | N | <NULL>6117 | <NULL> | <NULL> | <NULL>6118 | <NULL> | <NULL> | <NULL> |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-05 : 12:07:42
|
| [code]SELECT u.ext,tmp.date,tmp.reviewed,tmp.reviewFROM Users uLEFT JOIN (SELECT r.*FROM REview rINNER JOIN (SELECT ext,MAX(date) AS latest FROM REVIEW GROUP BY ext)r1ON r1.ext=r.extAND r1.latest=r.date)tmpON tmp.ext=u.ext[/code] |
|
|
|
Jaypoc
Yak Posting Veteran
58 Posts |
Posted - 2008-11-05 : 14:06:19
|
| Hey thanks a bunch! It works great...So now I ask if there's a way to infuse your thinking patterns into my brain! Is there any *good* references that simplify the concepts of more advanced queries? I am finally understanding JOINs somewhat and can build basic queries (slightly advanced ones using visualizer in Enterprive Manager) but I hate having to pop in here whenever I need a (probably simple to most of you) query. I'd like to give back and maybe answer a few questions someday. |
|
|
|
|
|
|
|
|
Combining 2 Selects