Syntactically, this will never work. No kind of join that I know of will allow this (employee e,training tr) cartesian construct. In addition, there is no relationship between either "employee" or "training" and the table "Node". They have "appeared out of nowhere". Joins are designed to establish relationships. Here, there is none. You can only join related tables, sub queries, table functions or whatever. There must be a foreign key in either "employee" or "training" that relates to "Node", and then a foreign key in either "employee" or "training" that relates one to the other. It seems to me that there are tables missing here that relate trainees to courses, courses to nodes, whatever. That's a nasty bit of recursion on the "Node" table too. I reckon this could be done much more elegantly so that there is no limit to the number of levels. Looks a bit like an assignment task.

select
ln.node_Id,
ln.node_Name,
tn.node_Id,
tn.node_Name,
pn.node_Id,
pn.node_Name,
e.emp_Id,
e.emp_first_Name,
e.emp_last_Name
from
Node pn
Inner Join Node tn On pn.node_Id = tn.node_parent_Node_Id
Inner Join Node ln ON ln.node_parent_Node_Id = tn.node_Id
Inner join courses c on pn.node_Id = c.training_center_Id
Inner join training tr on c.course_Id = tr.course_Id
Inner Join employee e ON e.emp_Id = tr.trng_trainee_Id


-----Original Message-----
From: SangeethaPriya R. [mailto:db2udbdba-ezmlmshield-x26622108.[Email address protected]
Sent: Monday, 29 October 2007 3:47 PM
To: LazyDBA Discussion
Subject: Reg. Query for Inner Join



Hi All,

Please help me to do inner join with the following query.


SELECT ln.node_Id,
ln.node_Name,
tn.node_Id,
tn.node_Name,
pn.node_Id,
pn.node_Name,
e.emp_Id,
e.emp_first_Name,
e.emp_last_Name,
FROM Node pn

Inner Join Node tn

On pn.node_Id = tn.node_parent_Node_Id
Inner Join Node ln

ON ln.node_parent_Node_Id = tn.node_Id

Inner Join employee e,training tr
ON e.emp_Id = tr.trng_trainee_Id

In the above query the area marked in Red Color is giving problem for me.

How to join more the one table in the same inner join because tbe ON condition in the next line to the Inner Join, Includes two new tables employee e and training tr

which are not referred previously.

Please Help. Its urgent for me.

Thanks and Regards,

R. Sangeetha Priya



---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a dba job: http://jobs.lazydba.com To Subscribe : http://www.LazyDBA.com To unsubscribe: http://www.lazydba.com/unsubscribe.html


DB2 & UDB email list listserv db2-l LazyDBA home page