Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-server-2005
Hi.
Here i have two tables tblBonusMaster and tblComboDetails
 
tblBonusMaster
 
boID Description boBonusMode
---- ----------- -----------
1 200 Daily
2 100 Yearly
3 1000 0
4 150 0
 
tblComboDetails
 
cdClass cdText cdValue
------- ------ -------
BonusMode Daily Daily
BonusMode Weekly Weekly
BonusMode Yearly Yearly
 

here i want to get all the field values of the first table(ie tblBonusMaster) including the 3rd and 4th row of the tblBonusMaster containing the cdClass (ie Bonus Mode in this case)
 
below is my query
 
SELECT tblBonusMaster.*,tblComboDetails.*
from tblBonusMaster
left join tblComboDetails on tblBonusMaster.boBonusMode=tblComboDetails.cdValue
where tblComboDetails.cdClass='Bonus Mode'
 

 
using this query Im only getting the 1st and 2nd row of tblBonusMaster. the row containing zero are not getting displayed. How can i get all the rows of the first table
Posted 17-Jan-13 18:27pm
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

SELECT tblBonusMaster.*,tblComboDetails.*
from tblBonusMaster
left join tblComboDetails on tblBonusMaster.boBonusMode=tblComboDetails.cdValue;
 
you are using where that causing the trouble
  Permalink  
Comments
Nihal Hussain at 18-Jan-13 0:55am
   
@Hasham Ahmad. I want the 3rd and 4th row also along with 1st and 2nd. So whats the condition i should put
Nihal Hussain at 18-Jan-13 1:50am
   
@Hasham Ahmad In that Link Im getting it. but what if i add another cdClass say cdClass='Bonus Type' but condition I wil give is where cdClass='Bonus Mode' then how can i get the 3rd and 4th row of tblBonusMaster which has bobonusMode value as 0.
Nihal Hussain at 18-Jan-13 3:58am
   
@Hasham Ahmad It may because suppose cdClass='Bonus Type' contains the same cdValue say cdValue='Daily'(which is already therein cdClass='Bonus Mode' then cdValue= 'Daily' of both cdClass's ie Bonus Mode and Bonus Mode will get displayed if we dont give the condition where tblComboDetails.cdClass='Bonus Mode'.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

your query is right .u cant get 3 and 4th row because
u r using condition tblBonusMaster.boBonusMode=tblComboDetails.cdValue so
it will display match data of both table . so u should change condition .
  Permalink  
Comments
Nihal Hussain at 18-Jan-13 0:55am
   
@solanki.net. I want the 3rd and 4th row also along with 1st and 2nd. So whats the condition i should put
solanki.net at 18-Jan-13 1:06am
   
If u add id column in tblComboDetails table then apply join on id column then u can get 3rd row but not 4th row because your second table has only 3 record .if your second table (tblComboDetails ) has four record then u can get 3rd and 4th row also.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 340
1 OriginalGriff 250
2 DamithSL 170
3 Kornfeld Eliyahu Peter 130
4 Peter Leow 95
0 OriginalGriff 7,315
1 DamithSL 5,199
2 Sergey Alexandrovich Kryukov 4,917
3 Maciej Los 4,866
4 Kornfeld Eliyahu Peter 4,514


Advertise | Privacy | Mobile
Web01 | 2.8.141223.1 | Last Updated 18 Jan 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100