three table join using standard ansi sql

Question

I am trying to query some data from my sys tables and I am having trouble using standard ansi 92 (the new ansi standard) since this is what we are encouraged to use at our shop. The problem is that I can write queries using new ansi sql between two tables but my query involves three tables and when I use the old t1 = t2 syntax which I am used to the query works fine but I cannot covert it to ansi sql using inner join syntax. Enuf said here is the query how I wrote it and it works:

 select t.name,t.parent_id,o.name
    from syscomments c ,
         sys.triggers t ,
         sysobjects o 
    WHERE c.id = t."object_id"
      and t.parent_id = o.id
    and "text" LIKE '%master_key%'
    order by o.name 

the above query works fine and I get the right number of rows (about 50 rows which is right)

however when i converted this same query to standard ansi using inner joins, this is how i wrote it and now it returns 4800 rows, obviosuly I m missing a join but how do you join three tables like above using ansi sql:

select t.name,t.parent_id,o.name
    from syscomments c inner join sys.triggers t on c.id = t."object_id",
         sysobjects o inner join sys.triggers tr on o.id = tr.parent_id
    WHERE "text" LIKE '%master_key%' 

Can someone help fix it? thanks

Answer 1

select t.name,t.parent_id,o.name
    from syscomments c 
    inner join sys.triggers t on c.id = t."object_id"
    inner join sysobjects o on o.id = t.parent_id
    WHERE "text" LIKE '%master_key%' 

---

Chuck Pedretti | Magenic – North Region | magenic.com

Answer 2

try this 

select t.name,t.parent_id,o.name
    from syscomments c inner join sys.triggers t on c.id = t."object_id"
         INNER JOIN sysobjects o  on o.id = t.parent_id
    WHERE "text" LIKE '%master_key%'

---

Hope it Helps!!

Answer 3

You are still kind of mixing the two.  This worked for me (I replaced the text with something that would return in my database):

Select	t.Name
		,t.Parent_Id
		,o.Name
From	syscomments c
join	sys.triggers t
	on	c.id = t.[object_id]
join	sysobjects o
	on	t.Parent_Id = o.Id
Where	[Text] Like '%master_key%'
Order By o.Name

Answer 4

The current standard is 2011 and it too recent to be used. Stick to SQL-99; SQL-92 is super safe. Why did you have Triggers twice in the DML?

SELECT T.name AS triggger_name, T.parent_id AS parent_triggger_name,  O.name AS object_name
  FROM syscomments AS C,
       sys.triggers AS T,
       sysobjects AS O
 WHERE O.id = T.parent_id
   AND "text" LIKE '%master_key%'
   AND C.id = T."object_id";

The infixed notation is an option and not required at all!

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Answer 5

thanks to all as always CELKO's comments are much appreciated, always to the point. Other members suggestion and fix they provided worked great as well. I know using sql092 is super safe and I can write any type of query using that syntax but at my work i m forced to use sql-99 just for consistency reasons. thanks to all