Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»»

NULL Values and Joins Expand / Collapse
Author
Message
Posted Monday, December 20, 2010 7:12 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:05 AM
Points: 13,724, Visits: 10,680
SanDroid (12/20/2010)
I agree that if you read the question the answer is obvious.

However there is a HUGE typo in the explination...
B not C is the Answer?

Disapointing.

May I ask you a question:
why is the first post in this thread from you? (announcing which question this thread belong to).
I was under the impression that you made the question...




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1037175
Posted Monday, December 20, 2010 7:27 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:01 AM
Points: 1,232, Visits: 1,046
da-zero (12/20/2010)
SanDroid (12/20/2010)

However there is a HUGE typo in the explination...
B not C is the Answer?
Disapointing.

May I ask you a question:
why is the first post in this thread from you? (announcing which question this thread belong to).
I was under the impression that you made the question...

Sorry I should have worded this reply better.

The disapointing thing is that this question is eaxactly like it was originaly submitted.
It was edited twice for errors after that, but the corrected version is not what was posted today.

At least the answers and the script match, even if the explination does not.

I took the time to edit and correct my question so it was easy to read, and have a good explination.
It would have been nice to have seen that posted, and not the original with mistakes.

UPDATE: I sent Steve an email explaining to him what happend. I asked that everyone get a free point for having to deal with such things on a Monday.
Post #1037184
Posted Monday, December 20, 2010 7:48 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, September 12, 2014 8:38 AM
Points: 3,675, Visits: 72,434
The answer has the following text in it.

7 rows where column d = three

The wording of the answers were confusing as hell.

They should be listed as
7 rows returned, the last row has column d = three

or something like that.

I got it right, but I spent more time analyzing the answers trying to figure out what they meant than I did actually analyzing the question. I kept looking at the code, and saying to myself, "None of the rows repeat enough time to get the word three seven times, etc etc...

I ruled out the insert error because the error that comes back is about inserting into column a, not b.




--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Post #1037199
Posted Monday, December 20, 2010 7:49 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, September 12, 2014 8:38 AM
Points: 3,675, Visits: 72,434
Carlo Romagnano (12/20/2010)
philip.cullingworth (12/20/2010)
da-zero (12/20/2010)

Your output has 7 rows and the column d contains "three" for the 7th row, so it should be answer D.


But Answer D is
7 rows where column d = three

I only have 1 row where column d = three

Please, read carefully the question: it asks how many rows are in the results and what is the value of column "d" on the last row.


Yes, and the answer, the correct answer, states in a sentance that there will be 7 rows where the value of column d will be three.




--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Post #1037201
Posted Monday, December 20, 2010 7:58 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:01 AM
Points: 1,232, Visits: 1,046
mtassin (12/20/2010)

They should be listed as
7 rows returned, the last row has column d = three

or something like that.


You are absolutely right about that.
Post #1037208
Posted Monday, December 20, 2010 8:36 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 24, 2013 9:59 PM
Points: 1,354, Visits: 1,299
a	b	c	d
4 join1 4 join2
4 join1 4 join2
2 join3 2 one
2 join3 2 one
1 one NULL NULL
NULL NULL NULL two
NULL NULL NULL three


The reason I missed the answer is that I thought if you order by column d, "two" would be at the bottom. Doesn't "two" come after "three" in an order clause?

Well, I ignored the "desc" after the order by. Darn it. Details, details.
Post #1037229
Posted Monday, December 20, 2010 8:51 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, August 29, 2014 11:33 AM
Points: 554, Visits: 1,196
Carlo Romagnano (12/20/2010)
ian.grace (12/20/2010)
I don't understand how the first transaction could succeed given the attempt to insert the null value into the not-null field "a". Also, why is the last insert of the second transaction, VALUES(2 ,'one'), invalid? Help, I'm confused.

As posted above by vk-kirov and others, the explanation is terrible with a lot of mistakes.



Ian, I know you've received an explanation already, but I wanted to add to that. You can control the behavior on error as follows in the comments below.

BEGIN TRAN
INSERT INTO [Table1]( a,b)
VALUES(1 ,'one')

INSERT INTO [Table1]( a,b) -- Insert fails because the NOT NULL constraint on column [a]
VALUES(NULL ,'five')
-- Even though the above insert STATEMENT fails, execution of the BATCH continues.
-- To halt execution after error use SET XACT_ABORT = ON (stops the BATCH from executing)
-- To reroute execution after error use TRY CATCH (recommended) or check @@Error and use GOTO

INSERT INTO [Table1]( a,b)
VALUES(4 ,'join1')

INSERT INTO [Table1]( a,b])
VALUES(2 ,'join3')
COMMIT TRAN
GO

Post #1037235
Posted Monday, December 20, 2010 10:03 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 9:55 AM
Points: 21,739, Visits: 15,429
Thanks for the question.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1037270
Posted Tuesday, December 21, 2010 5:10 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 24, 2014 4:28 AM
Points: 1,100, Visits: 4,898
A confusing question, with a confusing explanation!
I read it several times and couldn't work out if it was a true test or some kind of trick
So I didn't learn anything today :-(
Post #1037633
Posted Tuesday, December 21, 2010 7:46 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 1:39 PM
Points: 922, Visits: 197
The question was very good! I almost answered it wrong because I thought it was left outer join instead of FULL outer join. For some reason the explanation does not match the correct answer.
Post #1037730
« Prev Topic | Next Topic »

Add to briefcase «««12345»»»

Permissions Expand / Collapse