StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
primarykey
Id
11294912
data
AcceptedAnswerId
0
AnswerCount
0
ClosedDate
CommentCount
0
CommunityOwnedDate
CreationDate
2012-07-02T13:41:26.167
FavoriteCount
0
LastActivityDate
2012-07-02T13:41:26.167
LastEditDate
LastEditorUserId
0
OwnerUserId
111424
ParentId
11292511
PostTypeId
2
Score
0
ViewCount
0
LastEditorDisplayName
text
Body
Why your query returns two rows To understand why your query returns two rows instead of the one you expect, it helps to understand the logical steps that SQL Server theoretically takes when executing your query. The first logical step is to process the FROM clause. Your query specifies a cross join between the two tables <code>dbo.Test1</code> and <code>dbo.corr_table</code> to produce an intermediate virtual table (let's call it <code>V1</code>) containing 8 rows: <pre><code>ID COUNTRY PERIODKEY A B C FIELD VALUE START_DATE END_DATE 1 E 201201 1 5 9 a 4 201201 201202 1 E 201202 1 5 9 a 4 201201 201202 3 G 201203 3 7 11 a 4 201201 201202 4 H 201204 4 8 12 a 4 201201 201202 1 E 201201 1 5 9 b 6 201201 201202 1 E 201202 1 5 9 b 6 201201 201202 3 G 201203 3 7 11 b 6 201201 201202 4 H 201204 4 8 12 b 6 201201 201202 </code></pre> The second logical step is to process the WHERE clause. Your query specifies that only rows with an ID of 1, a Country of 'E', and a PeriodKey of 201201 should pass. Two rows in the intermediate table meet this condition to populate another intermediate table (let's call it <code>V2</code>): <pre><code>ID COUNTRY PERIODKEY A B C FIELD VALUE START_DATE END_DATE 1 E 201201 1 5 9 a 4 201201 201202 1 E 201201 1 5 9 b 6 201201 201202 </code></pre> The third and final logical step is to process the SELECT clause. Your query specifies that two new columns <code>a</code> and <code>b</code> should be computed based on values in existing columns, and that all the columns from table <code>dbo.Test1</code> should be returned unmodified. The SELECT clause is basically a list of columns that defines the structure of the result set. It does not control how many rows are in the result set. This intermediate table (let's call it <code>V3</code>) is the same as the result set: <pre><code>a b id Country PeriodKey a b c 4 5 1 E 201201 1 5 9 1 6 1 E 201201 1 5 9 </code></pre> The two rows in <code>V2</code> both contain elements of the single row that you expect to see in your result set. All the columns from the base table <code>dbo.Test1</code> contain the same value, because the values in these columns were copied from just one row in <code>dbo.Test1</code>. The values in the first columns <code>a</code> and <code>b</code> are copied from two different rows in <code>dbo.corr_table</code>. If we were able to group these two rows in <code>V2</code> together, we could make one row that matches our expectations. Fortunately, we can express this easily in SQL. How to produce your expected result set In SQL, A SELECT statement has the GROUP BY clause that groups multiple rows into one row that represents the group. Logically, it is processed after the WHERE clause and before the SELECT clause. To your query I added a GROUP BY clause and invoked an aggregate function for each of the computed columns. This query produces your expected result: <pre><code>SELECT MAX( CASE WHEN dbo.corr_table.Field = 'a' THEN dbo.corr_table.Value END ) AS corr_a, MAX( CASE WHEN dbo.corr_table.Field = 'b' THEN dbo.corr_table.Value END ) AS corr_b, dbo.Test1.* FROM dbo.Test1 INNER JOIN dbo.corr_table ON dbo.Test1.id = dbo.corr_table.id WHERE dbo.Test1.id = 1 and dbo.Test1.Country = 'E' and dbo.Test1.PeriodKey in (201201) GROUP BY dbo.Test1.id, dbo.Test1.Country, dbo.Test1.PeriodKey, dbo.Test1.a, dbo.Test1.b, dbo.Test1.c; </code></pre> I have no more time to explain just now, but I will expand my answer later with an explanation of why this works. For now, I recommend that you read about logical query processing and the SELECT statement. Itzik Ben-Gan published a helpful <a href="http://www.sql.co.il/books/insidetsql2008/Logical%20Query%20Processing%20Poster.pdf" rel="nofollow noreferrer">PDF flow chart</a> explaining the logical steps. It is reproduced as an image below, copied from <a href="http://sqlwithmanoj.wordpress.com/2010/10/28/sql-logical-query-processing-order/" rel="nofollow noreferrer">sqlwithmanoj.wordpress.com</a>: <img src="https://i.stack.imgur.com/SqryD.jpg" alt="enter image description here">
Tags
Title
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. POStrange sql query behaviour
 singulars
 PostTypePostTypeId
 PTQuestion
PostTypePostTypeId
1. PTAnswer
UserLastEditorUserId
1. This table or related slice is empty.
UserOwnerUserId
1. USIain Samuel McLean Elder
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. This table or related slice is empty.
VotesPostIdCreationDate
1. This table or related slice is empty.
CommentsPostId
1. This table or related slice is empty.

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.