Using Left outer join and where

Question

5.00/5 (1 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 17:27pm

Nihal Hussain

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Hasham Ahmad · Answer 1 · 2013-01-17T18:10:00

Solution 1

SQL

SELECT tblBonusMaster.*,tblComboDetails.*
from tblBonusMaster
left join tblComboDetails on tblBonusMaster.boBonusMode=tblComboDetails.cdValue;

you are using where that causing the trouble

Posted 17-Jan-13 18:10pm

Hasham Ahmad

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

[no name] 18-Jan-13 1:19am

no condition ... run the query ... it will give you the results

[no name] 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.

[no name] 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'.

[no name] 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 ...

solanki.net · Answer 2 · 2013-01-17T18:20:00

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 .

Posted 17-Jan-13 18:20pm

solanki.net

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.