Old style Join conversion
-
SELECT t2.data, t2.id, t1.data
from table1 t1, table2 t2
where t2.id *= t1.id and t2.data=2 and t1.data=5
I am trying to convert the above old style join. The attempt I made is below. Unfortunately it does not return the same data set. According to BOL I have made the conversion correctly, I think. The above old style join returns all records in table2 and matching records in table1 with nulls where table1 does not have a record. The statement below only returns those records that have matching data in both tables.
SELECT t2.data,t2.id,t1.data
from table1 t1 left outer join table2 t2
on t1.id=t2.id
where t2.data=2 and t1.data=5
Any help would be appreciated.
Thanks in advance
Steve
-
SELECT t2.data,t2.id,t1.data
from table1 t1 left outer join table2 t2
on t1.id=t2.id and t2.data=2 and t1.data=5
-
Thanks but I don't think that is correct either. One, because I tried it and it produced yet another completly wrong recordset and two, because t2.data=2 and t1.data=5 should be conditionals of the whole statement and not just conditions of the join.
Any other thoughts?
Steve
-
Ever considered that the previous statement is the one giving out wrong results?
Or are we not seeing the whole picture?
-
SELECT t2.data,t2.id,t1.data
from table2 t2 left outer join table1 t1
on t1.id=t2.id
where t2.data=2 and t1.data=5
-
Doh, missed the inversion of the table names... but you stil have to move the outer table conditions from the where part to the join part or it'll fail.
-
The statement with the *= join syntax is the correct and desired output. Here is the english version of what I am trying to accomplish. I have two tables that have an ID field. Table 2 is a master table on which I filter the IDs with the Table 2 data column. The ID column in Table 1 contain records that match some but not all of the IDs in Table 2.
Table1 | Table2
ID Data | ID Data
1 5 | 1 2
2 5 | 2 2
2 6 | 3 2
3 6 | 4 3
I expect to see the following when I filter the data such that T2.Data=2 and T1.Data=5
T2.Data,T2.ID,T1.Data
2,1,5
2,2,5
2,3,NULL
Steve
-
Nice try,
I went that route as well. I tried
from table2 t2 left outer join table1 t1 on t1.id=t2.id
from table2 t2 left outer join table1 t1 on t2.id=t1.id
from table1 t1 left outer join table2 t2 on t1.id=t2.id
from table1 t1 left outer join table2 t2 on t2.id=t1.id
All these options returned the same dataset. All records that match in both tables, which is not the desired result.
My real concern is that the * join syntax is deprecated in Sql 2k5, otherwise I would just go with what works.
Steve
-
SELECT t2.data,t2.id,t1.data
from table2 t2 left outer join table1 t1
on t1.id=t2.id and t1.data=5
where t2.data=2
Having "t1.data=5" in where clause you eliminate NULLs and make it INNER JOIN.
_____________
Code for TallyGenerator -
I don't want to eliminate the nulls. That is the point the *= syntax does not eliminate the nulls which is the desired result. I am not trying to change the output, I am trying to create the ANSI Join without using the *= syntax.
Steve
-
Just to recap. Below is a script that will produce the environment. Once you have the environment created, write a sql statement that will do the same as the following statement without using the *= Syntax.
Select t1.id1,t1.data1, t2.data2
From Table1 t1, Table2 t2
Where t1.id1*=t2.id2 and t1.data1 = 2 and t2.data2=20
Create Table Table1
(
id1 int,
data1 int
)
Create Table Table2
(
id2 int,
data2 int
)
insert into Table1 Values(1,1)
insert into Table1 Values(2,2)
insert into Table1 Values(3,1)
insert into Table1 Values(4,3)
insert into Table1 Values(5,2)
insert into Table1 Values(6,2)
insert into Table1 Values(7,3)
insert into Table1 Values(8,2)
insert into Table1 Values(9,3)
insert into Table2 Values(2,10)
insert into Table2 Values(2,20)
insert into Table2 Values(2,30)
insert into Table2 Values(3,20)
insert into Table2 Values(4,30)
insert into Table2 Values(4,20)
insert into Table2 Values(8,30)
insert into Table2 Values(8,20)
Steve
-
Using your first sample data:
SELECT t2.data,t2.id, t1.data
from (SELECT id, data FROM table2 WHERE data = 2) t2 left outer join
(SELECT id, data FROM table1 WHERE data = 5) t1
on t1.id=t2.id
where t2.data=2
Mark
-
The more I think about this, the more it makes sense. Microsoft states that old join style using *= will return "ambiguous" results. This is a proof case!
You have an additional constraint on t2.data2 which impares the LEFT JOIN. The old syntax does not make the thorough checks on the data that should be coded and performed. My select using the correct syntax works because I check for the NULLs when the additional constraint is placed upon t2.data2. In other words, the old style allows for sloppy coding which can return unexpected records or records which should in fact not be returned with the additional constraints placed upon this query...
DECLARE @Table1 TABLE( id1 integer,
data1 integer)
DECLARE @Table2 TABLE( id2 integer,
data2 integer)
INSERT INTO @Table1
SELECT 1,1 UNION ALL
SELECT 2,2 UNION ALL
SELECT 3,1 UNION ALL
SELECT 4,3 UNION ALL
SELECT 5,2 UNION ALL
SELECT 6,2 UNION ALL
SELECT 7,3 UNION ALL
SELECT 8,2 UNION ALL
SELECT 9,3
INSERT INTO @Table2
SELECT 2,10 UNION ALL
SELECT 2,20 UNION ALL
SELECT 2,30 UNION ALL
SELECT 3,20 UNION ALL
SELECT 4,30 UNION ALL
SELECT 4,20 UNION ALL
SELECT 8,30 UNION ALL
SELECT 8,20
SELECT t1.id1, t1.data1, t2.data2
FROM @Table1 t1, @Table2 t2
WHERE t1.id1 *= t2.id2
AND t1.data1 = 2
AND t2.data2 = 20
SELECT t1.id1, t1.data1, t2.data2
FROM @Table1 t1
LEFT JOIN @Table2 t2 ON( t1.id1 = t2.id2)
WHERE t1.data1 = 2
AND ISNULL( t2.data2, 20) = 20
I wasn't born stupid - I had to study.
-
SUCCESS
Thanks a ton Farrell. I was able to do it without all those "fancy" declares that you suggested just by changing my contitional a little bit.
The Final Result works like a charm.
Select t1.id1,t1.data1, t2.data2
From Table1 t1 Left Outer Join Table2 t2 on t1.id1 = t2.id2
Where t1.data1=2 and isnull(t2.data2,20)=20
Steve
-
Glad to help!
(The declares make Table Variables and they clean themselves up - learned that here and that is why I used them...)
I wasn't born stupid - I had to study.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply