Why does this self join give no rows?

[bdotzour]

Given a table defined like this:

MyTable

ItemID SubItemID SubItemValue
0 0 0
0 0 15
0 1 20
0 1 25

If I execute this self-join:

select t1.subItemID, t2.SubItemValue
from mytest t1, mytest t2
where t1.subitemid = 1 and
t2.itemid = t1.itemid and
t2.subitemvalue = 0

I get back:

SubItemID SubItemValue
1 0
1 0

which I understand.

But if I do this:

select t1.subItemID, t2.SubItemValue
from mytest t1, mytest t2
where t1.subitemid = 1 and
t2.itemid = t1.itemid and
t2.subitemvalue = 3

I get no rows back at all. I expected to get:

SubItemID SubItemValue
1 NULL
1 NULL

Can anyone explain to me what is going on there?

 

[tlbroadbent]

You must use a LEFT JOIN to get the results you want. A LEFT JOIN returns all rows from the table on the left and only matching rows from the table on the right. I recommend using ANSI JOIN syntax as the older SYSNTAX may not work in later versions of SQL. In fact, OUTER JOINs may return incorrect results in SQL 2000 if you use the old syntax.

Note that you'll also need to test for null values in the right table in order to return null values.

select t1.subItemID, t2.SubItemValue
from mytest t1
left join mytest t2
On t2.itemid = t1.itemid
where t1.subitemid = 1
and (t2.subitemvalue = 3
or t2.itemid is null)

Another method is to use a sub-query to select records from one or both tables and join the sub-queries. I prefer this method because the selection criteria for each table are isolated. SQL Server handles this type of query very efficiently.

select q1.subItemID, q2.SubItemValue
from
(Select ItemID, SubItemId
From mytest where subitemid = 1) q1
left join
(Select ItemId, SubItemValue
From mytest where subitemvalue = 3) q2
On q1.itemid = q2.itemid Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[Guest_imported]

Why do you expect NULL values? If you join the two tables you'll get a temporary table which contains all possible combinations of the left table with the right table. In this case 16 combinations:

table 1 table 2
0 0 0 0 0 0
0 0 15 0 0 15
0 1 20 0 1 20
0 1 25 0 1 25
0 0 0 0 0 15
0 0 15 0 1 20
0 1 20 0 1 25
0 1 25 0 0 0
0 0 0 0 1 20
0 0 15 0 1 25
0 1 20 0 0 0
0 1 25 0 0 15
0 0 0 0 1 25
0 0 15 0 0 0
0 1 20 0 0 15
0 1 25 0 1 20

One of the tests you do in your where part is testing for rows where the subitemvalue of table 2 equals 3. This means that no rows will be returned at all because there aren't any rows that match this constraint. (You'll indead get those two rows for the first query)

A (full outer)join will create a temporary table that contains all possible combinations of the rows in the original table. This will be treated as one table by the rest of the query. In this table there are no NULL values, this means that if you do a normal select on it, there's no way you can get NULL values.

What i just stated above is the theory of it, i don't
know what the database's inner calculations are. But in the end it simply means that what you expected to be the outcome was wrong.

I don't know what you're trying to do, but i do know that with this query you won't get the values you expected.

 

[Andel]

You can use Terri's 2nd example, it works.
But I'm just wondering why you break your head for if I understand you correctly, you just want to display only SubItemID = 1 and SubItemValue = 3, and if not equal 3 then you want null?

You can also use this.

select SubItemId,
SubItemValue = case when SubItemValue = 3 then 3 else null end
from MyTable
where SubItemId = 1

Andel
andel@barroga.net

http://andyb.8m.com

 

[tlbroadbent]

DarkFalkon,

bdotzour can get what he wants using the query I provided. You've suggested a full or cross join when he needs a Left (or Right) join.

You state, "One of the tests you do in your where part is testing for rows where the subitemvalue of table 2 equals 3. One of the tests you do in your where part is testing for rows where the subitemvalue of table 2 equals 3. This means that no rows will be returned at all because there aren't any rows that match this constraint."

You are correct in that statement. That is the purpose of the IS NULL test. When you perform a Left Join, if matching data on the right doesn't exist, the query does return NULL in the columns. If the result is to contain records that don't meet the criteria, the test for IS NULL is essential.

Running a query doesn't create a temporary table. It returns a result set. Sometimes a sub-query is referred to as a derived table. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[bdotzour]

tlbroadbent hit it with the second query. What I want is, indeed, to do a left join on a table with itself. In fact, I want to do it several times. The scenario I have is this, I have been given a table with data in rows that I want to convert to columns.

Example:

RecordID FieldName FieldValue
1 Name 'Tom'
1 ID 14367
2 Name 'Jasper'
2 ID 13658
2 Phone '555-1212'


Now, say I want to get a recordset that looks like this:

Name ID Phone
'Tom' 14367 NULL
'Jasper' 13658 '555-1212'


And there could be MANY more fields than just those 3. So I guess I want to do a LEFT join on the first two fields, then do a LEFT join on that result with the 3rd field, then do a LEFT join on that result with the 4th field and so on.

Well actually I don't want to do this, but it seems like the only way.