Bsrgvty

Making a pikuach nefesh phone call on Yom Kippur - mitsva or something to be avoided?

Is it allowed to let the engine of an aircraft idle without a pilot in the plane. (For both helicopters and aeroplanes)

How were Kurds involved (or not) in the invasion of Normandy?

Does using an img title attribute in addition to the alt attribute help image SEO?

Moving objects and gravitational radiation

If you pass through the order of colors in Prismatic Wall one way, do you reverse the order of colors passing through the other way?

Is it really better for the environment if I take the stairs as opposed to a lift?

What plausible reasons why people forget they didn't originally live on this new planet?

Probability of a number being rational

Why is 1.e4 c5 2.Nf3 b6 so unpopular?

How would a race of humanoids with tails design [vehicle] seats?

What's the best way to annotate this syncopation?

What can I wear to avoid getting frisked and crotch searched by TSA at the airport?

Stare long enough and you will have found the answer

What is the following style of typography called?

What would a chair for a Human with a Tail look like?

Do any languages mark social distinctions other than gender and status?

When applying for a visa has there ever been a case of embassy asking for proof of right to be in the present country?

Fantasy series about a human girl with gold tattoos who makes too much blood

Do neurons of a neural network model a linear relationship?

Can every type of linear filter be modelled by a convolution?

Why do baby boomers have to sell 5% of their retirement accounts by the end of the year?

Are there any Baryons that have quark-antiquark combinations?

How does an Evocation Wizard's Overchannel ability interact with Chaos Bolt?

JOIN to obtain all the rows from the first table along with NULLs from the second table

ON DUPLICATE KEY UPDATE questionSubqueries run very fast individually, but when joined are very slowSQL join query to show rows with non-existent rows in one tableHow to prevent #Error values derived from Nulls in an outer joinWhy does the first query scan and the second seek?Getting rows in left table only if not present in the right tableWhich join to use?How can I properly merge the rows of several table with default values?SQL Outer Join while conditioning on an extra columnSELECT row based on range from columns in second table

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty
margin-bottom:0;

3

1

I have two tables (tblReps and tblDailyWorkingTime). The tblReps table, i.e., the first one, returns 37 rows, but when I JOIN it with the second table I get 36 rows as the second table only has 36 rows that match the first table.

How do I go about getting the 37 rows back with a NULL result for the row that was not matched?

Here is the query that I have so far:

SELECT 
 tblReps.[sName] AS 'RepName',
 tblReps.sNote AS 'Type',
 RIGHT(CAST(tblDaily.sDateAndTimeStart AS smalldatetime), 8) AS 'DayStarted'

FROM 
 [tblRepresentatives] AS tblReps

FULL OUTER JOIN 
 tblDailyWorkingTime AS tblDaily ON tblDaily.sRepresentativeCode = tblReps.sCode 

WHERE 
 tblDaily.sDate = CAST(GETDATE() AS DATE)
 AND tblReps.[sActive] = 'True'

sql-server join

share|improve this question

edited Jun 3 at 21:07

MDCCL

7,32933 gold badges2020 silver badges4848 bronze badges

asked May 27 at 13:06

Mark BlackburnMark Blackburn

1822 bronze badges

add a comment
 | 

3

1

I have two tables (tblReps and tblDailyWorkingTime). The tblReps table, i.e., the first one, returns 37 rows, but when I JOIN it with the second table I get 36 rows as the second table only has 36 rows that match the first table.

How do I go about getting the 37 rows back with a NULL result for the row that was not matched?

Here is the query that I have so far:

SELECT 
 tblReps.[sName] AS 'RepName',
 tblReps.sNote AS 'Type',
 RIGHT(CAST(tblDaily.sDateAndTimeStart AS smalldatetime), 8) AS 'DayStarted'

FROM 
 [tblRepresentatives] AS tblReps

FULL OUTER JOIN 
 tblDailyWorkingTime AS tblDaily ON tblDaily.sRepresentativeCode = tblReps.sCode 

WHERE 
 tblDaily.sDate = CAST(GETDATE() AS DATE)
 AND tblReps.[sActive] = 'True'

sql-server join

share|improve this question

edited Jun 3 at 21:07

MDCCL

7,32933 gold badges2020 silver badges4848 bronze badges

asked May 27 at 13:06

Mark BlackburnMark Blackburn

1822 bronze badges

add a comment
 | 

I have two tables (tblReps and tblDailyWorkingTime). The tblReps table, i.e., the first one, returns 37 rows, but when I JOIN it with the second table I get 36 rows as the second table only has 36 rows that match the first table.

How do I go about getting the 37 rows back with a NULL result for the row that was not matched?

Here is the query that I have so far:

SELECT 
 tblReps.[sName] AS 'RepName',
 tblReps.sNote AS 'Type',
 RIGHT(CAST(tblDaily.sDateAndTimeStart AS smalldatetime), 8) AS 'DayStarted'

FROM 
 [tblRepresentatives] AS tblReps

FULL OUTER JOIN 
 tblDailyWorkingTime AS tblDaily ON tblDaily.sRepresentativeCode = tblReps.sCode 

WHERE 
 tblDaily.sDate = CAST(GETDATE() AS DATE)
 AND tblReps.[sActive] = 'True'

sql-server join

share|improve this question

edited Jun 3 at 21:07

MDCCL

7,32933 gold badges2020 silver badges4848 bronze badges

asked May 27 at 13:06

Mark BlackburnMark Blackburn

1822 bronze badges

SELECT 
 tblReps.[sName] AS 'RepName',
 tblReps.sNote AS 'Type',
 RIGHT(CAST(tblDaily.sDateAndTimeStart AS smalldatetime), 8) AS 'DayStarted'

FROM 
 [tblRepresentatives] AS tblReps

FULL OUTER JOIN 
 tblDailyWorkingTime AS tblDaily ON tblDaily.sRepresentativeCode = tblReps.sCode 

WHERE 
 tblDaily.sDate = CAST(GETDATE() AS DATE)
 AND tblReps.[sActive] = 'True'

share|improve this question

edited Jun 3 at 21:07

MDCCL

7,32933 gold badges2020 silver badges4848 bronze badges

asked May 27 at 13:06

Mark BlackburnMark Blackburn

1822 bronze badges

share|improve this question

edited Jun 3 at 21:07

MDCCL

7,32933 gold badges2020 silver badges4848 bronze badges

asked May 27 at 13:06

Mark BlackburnMark Blackburn

1822 bronze badges

edited Jun 3 at 21:07

MDCCL

7,32933 gold badges2020 silver badges4848 bronze badges

edited Jun 3 at 21:07

MDCCL

7,32933 gold badges2020 silver badges4848 bronze badges

asked May 27 at 13:06

Mark BlackburnMark Blackburn

1822 bronze badges

asked May 27 at 13:06

Mark BlackburnMark Blackburn

1822 bronze badges

1 Answer
1

active

oldest

votes

8

First of all, the correct type of join to use in this case is a left join:

...
FROM 
 [tblRepresentatives] AS tblReps

LEFT OUTER JOIN 
 tblDailyWorkingTime AS tblDaily ON tblDaily.sRepresentativeCode = tblReps.sCode 
...

In your specific case, you could still use FULL because this condition in WHERE would turn it into a left join anyway:

tblReps.[sActive] = 'True'

But it is better to express the intent accurately.

The same effect that turns your full join into a left join is actually responsible for the missing 37th row that you expected. More specifically, this other WHERE condition:

tblDaily.sDate = CAST(GETDATE() AS DATE)

transforms your join further into an inner join. That is why the 37th row is missing from the output.

The reason for that happening is this. The FROM clause returns a null in tblDaily.sDate for the unmatched row. Because the WHERE clause logically executes after the FROM, the above-mentioned predicate excludes the unmatched row, since NULL = CAST(GETDATE() AS DATE) does not evaluate to True.

What you need to do instead is move the sDate condition to the ON subclause:

...
FROM 
 [tblRepresentatives] AS tblReps

LEFT OUTER JOIN 
 tblDailyWorkingTime AS tblDaily ON tblDaily.sRepresentativeCode = tblReps.sCode
 AND tblDaily.sDate = CAST(GETDATE() AS DATE)

WHERE 
 tblReps.[sActive] = 'True'

That way the right-hand side of the join is filtered on tblDaily.sDate = CAST(GETDATE() AS DATE) before the join takes place. Consequently, the query will return the expected 37 rows from tblReps complemented either with matching data from tblDaily or with nulls.

share|improve this answer

edited May 27 at 16:13

answered May 27 at 13:39

Andriy MAndriy M

17.3k66 gold badges3939 silver badges7878 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you, Andriy that explanation was very helpful and makes sense to me. I have tested and I am now getting the expected result.
  
  – Mark Blackburn
  May 27 at 14:26
  

  
  
  
  

add a comment
 | 

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/4.0/"u003ecc by-sa 4.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f239140%2fjoin-to-obtain-all-the-rows-from-the-first-table-along-with-nulls-from-the-secon%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

8

First of all, the correct type of join to use in this case is a left join:

...
FROM 
 [tblRepresentatives] AS tblReps

LEFT OUTER JOIN 
 tblDailyWorkingTime AS tblDaily ON tblDaily.sRepresentativeCode = tblReps.sCode 
...

In your specific case, you could still use FULL because this condition in WHERE would turn it into a left join anyway:

tblReps.[sActive] = 'True'

But it is better to express the intent accurately.

The same effect that turns your full join into a left join is actually responsible for the missing 37th row that you expected. More specifically, this other WHERE condition:

tblDaily.sDate = CAST(GETDATE() AS DATE)

transforms your join further into an inner join. That is why the 37th row is missing from the output.

The reason for that happening is this. The FROM clause returns a null in tblDaily.sDate for the unmatched row. Because the WHERE clause logically executes after the FROM, the above-mentioned predicate excludes the unmatched row, since NULL = CAST(GETDATE() AS DATE) does not evaluate to True.

What you need to do instead is move the sDate condition to the ON subclause:

...
FROM 
 [tblRepresentatives] AS tblReps

LEFT OUTER JOIN 
 tblDailyWorkingTime AS tblDaily ON tblDaily.sRepresentativeCode = tblReps.sCode
 AND tblDaily.sDate = CAST(GETDATE() AS DATE)

WHERE 
 tblReps.[sActive] = 'True'

That way the right-hand side of the join is filtered on tblDaily.sDate = CAST(GETDATE() AS DATE) before the join takes place. Consequently, the query will return the expected 37 rows from tblReps complemented either with matching data from tblDaily or with nulls.

share|improve this answer

edited May 27 at 16:13

answered May 27 at 13:39

Andriy MAndriy M

17.3k66 gold badges3939 silver badges7878 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you, Andriy that explanation was very helpful and makes sense to me. I have tested and I am now getting the expected result.
  
  – Mark Blackburn
  May 27 at 14:26
  

  
  
  
  

add a comment
 | 

8

First of all, the correct type of join to use in this case is a left join:

...
FROM 
 [tblRepresentatives] AS tblReps

LEFT OUTER JOIN 
 tblDailyWorkingTime AS tblDaily ON tblDaily.sRepresentativeCode = tblReps.sCode 
...

In your specific case, you could still use FULL because this condition in WHERE would turn it into a left join anyway:

tblReps.[sActive] = 'True'

But it is better to express the intent accurately.

The same effect that turns your full join into a left join is actually responsible for the missing 37th row that you expected. More specifically, this other WHERE condition:

tblDaily.sDate = CAST(GETDATE() AS DATE)

transforms your join further into an inner join. That is why the 37th row is missing from the output.

The reason for that happening is this. The FROM clause returns a null in tblDaily.sDate for the unmatched row. Because the WHERE clause logically executes after the FROM, the above-mentioned predicate excludes the unmatched row, since NULL = CAST(GETDATE() AS DATE) does not evaluate to True.

What you need to do instead is move the sDate condition to the ON subclause:

...
FROM 
 [tblRepresentatives] AS tblReps

LEFT OUTER JOIN 
 tblDailyWorkingTime AS tblDaily ON tblDaily.sRepresentativeCode = tblReps.sCode
 AND tblDaily.sDate = CAST(GETDATE() AS DATE)

WHERE 
 tblReps.[sActive] = 'True'

That way the right-hand side of the join is filtered on tblDaily.sDate = CAST(GETDATE() AS DATE) before the join takes place. Consequently, the query will return the expected 37 rows from tblReps complemented either with matching data from tblDaily or with nulls.

share|improve this answer

edited May 27 at 16:13

answered May 27 at 13:39

Andriy MAndriy M

17.3k66 gold badges3939 silver badges7878 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you, Andriy that explanation was very helpful and makes sense to me. I have tested and I am now getting the expected result.
  
  – Mark Blackburn
  May 27 at 14:26
  

  
  
  
  

add a comment
 | 

First of all, the correct type of join to use in this case is a left join:

...
FROM 
 [tblRepresentatives] AS tblReps

LEFT OUTER JOIN 
 tblDailyWorkingTime AS tblDaily ON tblDaily.sRepresentativeCode = tblReps.sCode 
...

In your specific case, you could still use FULL because this condition in WHERE would turn it into a left join anyway:

tblReps.[sActive] = 'True'

But it is better to express the intent accurately.

The same effect that turns your full join into a left join is actually responsible for the missing 37th row that you expected. More specifically, this other WHERE condition:

tblDaily.sDate = CAST(GETDATE() AS DATE)

transforms your join further into an inner join. That is why the 37th row is missing from the output.

The reason for that happening is this. The FROM clause returns a null in tblDaily.sDate for the unmatched row. Because the WHERE clause logically executes after the FROM, the above-mentioned predicate excludes the unmatched row, since NULL = CAST(GETDATE() AS DATE) does not evaluate to True.

What you need to do instead is move the sDate condition to the ON subclause:

...
FROM 
 [tblRepresentatives] AS tblReps

LEFT OUTER JOIN 
 tblDailyWorkingTime AS tblDaily ON tblDaily.sRepresentativeCode = tblReps.sCode
 AND tblDaily.sDate = CAST(GETDATE() AS DATE)

WHERE 
 tblReps.[sActive] = 'True'

That way the right-hand side of the join is filtered on tblDaily.sDate = CAST(GETDATE() AS DATE) before the join takes place. Consequently, the query will return the expected 37 rows from tblReps complemented either with matching data from tblDaily or with nulls.

share|improve this answer

edited May 27 at 16:13

answered May 27 at 13:39

Andriy MAndriy M

17.3k66 gold badges3939 silver badges7878 bronze badges

...
FROM 
 [tblRepresentatives] AS tblReps

LEFT OUTER JOIN 
 tblDailyWorkingTime AS tblDaily ON tblDaily.sRepresentativeCode = tblReps.sCode 
...

tblReps.[sActive] = 'True'

tblDaily.sDate = CAST(GETDATE() AS DATE)

...
FROM 
 [tblRepresentatives] AS tblReps

LEFT OUTER JOIN 
 tblDailyWorkingTime AS tblDaily ON tblDaily.sRepresentativeCode = tblReps.sCode
 AND tblDaily.sDate = CAST(GETDATE() AS DATE)

WHERE 
 tblReps.[sActive] = 'True'

share|improve this answer

edited May 27 at 16:13

answered May 27 at 13:39

Andriy MAndriy M

17.3k66 gold badges3939 silver badges7878 bronze badges

answered May 27 at 13:39

Andriy MAndriy M

17.3k66 gold badges3939 silver badges7878 bronze badges

answered May 27 at 13:39

Andriy MAndriy M

17.3k66 gold badges3939 silver badges7878 bronze badges