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 |
|
kifeda
Posting Yak Master
136 Posts |
 Posted - 2007-09-27 : 14:09:26
|
| Hello all!I'm stuck on this sql statement. I want to do an inner join on a table called tblapsunits but if I do an inner on client id, since a client can have more than one allotment of units, the recordset will return two or more lines each of the authorization. 9Actually I think it will make me do a group by). So basically, I need to select ONLY the latest authorization number in the apsunit table where the date is is not expired. To do this I would do something like this:select strauthorizationNumber from tblapsunits where dtexpirationDate <= getdatte()or soemthing like that. So the question is how do I combine that statement with:SELECT a.strfirstname+' '+a.strlastname as theauthor, e.strtitle, e.strevent, e.inteventId, e.strstatus, aw.strfirstname+' '+aw.strlastname as appointmentWith, e.dtstartdate, e.dtendDate, c.strfirstname as cfirstname, c.strlastname as clastname, e.dtdatecreateFROM dbo.tblevents e left outer join tbladmin a on a.intadminId = e.intauthorid left outer join tbladmin aw on aw.intadminId = e.intappointmentWith left outer join tblclients c on c.intclientid = e.intclientIdI hope this makes sense. All I want to do is add the most recent and valid authorization number to the recordset above.Thanks in advance! |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-09-27 : 14:11:37
|
| Check out LEFT JOIN in books on line.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
kifeda
Posting Yak Master
136 Posts |
Posted - 2007-09-27 : 14:23:59
|
| its not completely an inner join problem. I have to use a where in or something like that. you didn't read my post. I explained it all there. you just read my post title |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-09-27 : 14:47:49
|
| You can try an INNER JOIN and use GROUP BY and do a MIN on the authorization number. If you can post some sample data and expected output we can help out.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-27 : 14:58:19
|
| "if I do an inner on client id, since a client can have more than one allotment of units, the recordset will return two or more lines each of the authorization"Use EXISTS instead of an INNER JOIN perhaps?Kristen |
|
|
|
kifeda
Posting Yak Master
136 Posts |
Posted - 2007-09-27 : 15:02:04
|
| yes, let's try that |
|
|
|
|
|
|
|
|
where in and inner join problem