Click here to Skip to main content
13,195,379 members (58,545 online)
Rate this:
 
Please Sign up or sign in to vote.
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 17: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 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
Hasham Ahmad 18-Jan-13 1:19am
   
no condition ... run the query ... it will give you the results
Hasham Ahmad 18-Jan-13 1:29am
   
just checked it using http://sqlfiddle.com/#!3/518d9/3
it gives the result according to the requirements.
only if i have understood your problem
Nihal Hussain 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.
Hasham Ahmad 18-Jan-13 1:59am
   
the same query will do :s
i dont think adding bonus mode to cdclass will have any effect on the query
Nihal Hussain 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'.
Hasham Ahmad 18-Jan-13 4:05am
   
LEFT JOIN just selects all the rows from the table given left of the join statement irrespective of the conditions meet in the table given at the right side ... so there will be no issue if the condition fails .. all u need to do is just to mention that u require a LEFT JOIN ... thats it :) and u will get all the rows from the left table unless filtered out by the where clause as u did in the query u mentioned in the questions...

if you are still experiencing problemts ... then do share the complete picture of the task you are performing ...
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 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 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
Top Experts
Last 24hrsThis month


Advertise | Privacy |
Web03 | 2.8.171019.1 | Last Updated 18 Jan 2013
Copyright © CodeProject, 1999-2017
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