Some questions are timeless and they never grow old; no matter how much they grow old their interest never dies. Earlier, I asked a simple puzzle based on a conversation on SQLAuthority Page, and have received an overwhelming response from readers. I still get emails related to this puzzle every day.
Before we move on, I suggest you read the question and its initial solution here:
The puzzle had three parts and the users had to fill up the second part, which I had left it for them to complete. I received many responses, and here is another response which is absolutely valid and worthy of acknowledgment. Once again, before you read the solution, I suggest you give the puzzle a quick try. Perhaps you’d find another neat way to solve this puzzle.
The puzzle had three parts:
- Fill in the blanks – T-SQL code
- Query and Resultset
Just like this puzzle, there are many other learning nuggets in our SQL Server Questions and Answers course online.
Complete Solution Query
-- Sample Table
CREATE TABLE FirstTable (Col1 INT)
CREATE TABLE SecondTable (Col1 INT)
ALTER TABLE FirstTable ADD CONSTRAINT
UX_FirstTable UNIQUE NONCLUSTERED (Col1);
ALTER TABLE SecondTable ADD CONSTRAINT
UX_SecondTable UNIQUE NONCLUSTERED (Col1);
INSERT INTO FirstTable (Col1)
INSERT INTO SecondTable (Col1)
FROM FirstTable ft
RIGHT JOIN SecondTable st ON st.Col1 = ft.Col1
FROM FirstTable ft
LEFT JOIN SecondTable st ON st.Col1 = ft.Col1
DROP TABLE FirstTable;
DROP TABLE SecondTable;
Now the reason I did not selected this as a valid answer is because if you notice original solution required to insert one row into the FirstTable. However, when you look at carefully, you can notice even this solution is valid solution. There were many other solution which require to change T-SQL which I initially provided, however as per the puzzle that was not allowed.
Any other solution out there?
Reference: Pinal Dave (http://blog.sqlauthority.com)