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 |
|
esambath
Yak Posting Veteran
89 Posts |
 Posted - 2008-11-20 : 01:41:40
|
| Hi friends, Please help.I have 3 TableKR_categoryCatIdNameParentidKR_RequestReqIdCatIdUserIdReporterId [userid or 0]TopicDescriptionStatekr_UsermanagementUserIdNameI have display the Result {Username, Reporter Name, Category Name, Description, and State} for particular ReqId This is my query, I need to modify the querySELECT u1.Name AS UserName ,u2.Name AS ReporterName,c.Name as CategoryName,r.Description,r.State,r.Abusive,r.Topic,r.Status,r.reqid FROM kr_Request r JOIN kr_Usermanagement u1 ON u1.UserId=r.UserId JOIN kr_Usermanagement u2 ON u2.UserId=r.ReporterId join Kr_Category c On c.catId=r.CatId where r.Deleted=0 and ReqId=90 Order by r.creationdateTime descThe scenario is when the Reporter Id is zero, there is no userid will be found, so it will doest not return any value. But what I need is when the reporterid is zero the reporter name should be display like “No Reporter”….If the reporter id is equal to the user id, that display the actual results…Thanks and RegardsE.sambath kumar |
|
|
Deon Smit
Starting Member
47 Posts |
Posted - 2008-11-20 : 01:46:40
|
| Try using a outer join or left outer join. Had the same thing a while back. It will not loose any records then. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-20 : 01:49:13
|
| [code]SELECT u1.Name AS UserName ,COALESCE(u2.Name,'No Reporter') AS ReporterName,c.Name as CategoryName,r.Description,r.State,r.Abusive,r.Topic,r.Status,r.reqid FROM kr_Request r JOIN kr_Usermanagement u1 ON u1.UserId=r.UserIdLEFT JOIN kr_Usermanagement u2 ON u2.UserId=r.ReporterIdjoin Kr_Category c On c.catId=r.CatIdwhere r.Deleted=0 and ReqId=90 Order by r.creationdateTime desc[/code] |
|
|
|
|
|
|
|
|
join query