May 24, 2011 at 12:16 pm
I recently came across a TSQL statement that I haven't seen in so long I've forgotten how it works. Can someone explain what happens when the from clause is as such:
From TableA, TableB
where ....
May 24, 2011 at 12:19 pm
It's a form of join.
select a.col1,b.col1
FROM A,B
WHERE a.col1 = b.col1
same as
select a.col1,b.col1
from a
inner join b
on a.col1 = b.col1
pretty sure I have that correct
May 24, 2011 at 12:20 pm
Then the following query doesn't have a join condition?
select distinct substring(nu.LocCode, 4, 1), c.Phone, c.GroupName, nu.Office, nu.*
from MEDAPPSDB.Lawson_Conv.dbo.NewUserLDAP nu,
MEDCREDENT.VisualCactus.dbo.v_Provider_Info_Complete c
WHERE nu.LocCode is not null
and nu.RMComplete = 0 and nu.Complete = 0
and len(nu.WorkPhone) < 1
and substring(nu.LocCode, 4, 1) = 'C'
and len(nu.LocCode) > 6
-- and (cast(substring(nu.LocCode, 5, 3) as int) = cast(c.Acct_Office_ID as int))
and substring(nu.LocCode, 5, 3) = c.Acct_Office_ID
-- AND nu.EmpID in (12113, 12114)
May 24, 2011 at 12:24 pm
dndaughtery (5/24/2011)
Then the following query doesn't have a join condition?select distinct substring(nu.LocCode, 4, 1), c.Phone, c.GroupName, nu.Office, nu.*
from MEDAPPSDB.Lawson_Conv.dbo.NewUserLDAP nu,
MEDCREDENT.VisualCactus.dbo.v_Provider_Info_Complete c
WHERE nu.LocCode is not null
and nu.RMComplete = 0 and nu.Complete = 0
and len(nu.WorkPhone) < 1
and substring(nu.LocCode, 4, 1) = 'C'
and len(nu.LocCode) > 6
-- and (cast(substring(nu.LocCode, 5, 3) as int) = cast(c.Acct_Office_ID as int))
and substring(nu.LocCode, 5, 3) = c.Acct_Office_ID
-- AND nu.EmpID in (12113, 12114)
In effect, the join condition is nu.LocCode = c.Acct_Office_ID (but with a SUBSTRING on LocCode).
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
May 25, 2011 at 3:02 am
to add.... These types of queries have variations, eg:
Select a.*,b.* from
TableA a,TableB b
where a.ColName *= b.ColName --"*=" implies a left outer join and "=*' implies a right outer join.
These are called Non ANSI type of joins and are not supported from SQL 2005 onwards.[unless u set the compatibility level to 80.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply