1) select * from schema.table1 --> First of all this select lists all the rows of my table
2) select * from schema.table1 o left join schema.table2 c on (o.ID=? and o.ID = c.custom_id) --> But appearently this select is giving me an error, because the custom_id will be read over and over again now matter in which row he is at the moment.
3= ID:int --> From this column comes the "?" within the select query ....
The solution was:
select* from schema.table1 o left join schema.table2 c on (o.ID=c.custom_id) where o.ID=?
I only suspect that possibly dates from the table schema.table2 be overwritten by data from schema.table_comment by LEFT JOIN - is that possible?
Or would you decline same named (in the case could be CustomerName) record in the second table, and then leave but empty?
the solution is at it should have been..
what is the problem/question
more details ..
Best Regards, Guy Przytula
Database Software Consultant
Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
Information Server Datastage Certified http://www.infocura.be
I would suggest you look up an old blog entry on LEFT OUTER JOINs by Robert Catterall. He, also, points to a couple of papers written by Terry Purcell which are probably the most informational for people trying to understand a LEFT OUTER JOIN.
Just to edify your First attempt above.
2) select * from schema.table1 o left join schema.table2 c on (o.ID=? and o.ID = c.custom_id)
You are asking the database to outer join to table2 only when the O.ID equals your value and that ID equals your custom id. And you want to see all rows from table1 regardless of the ID.
Keep in mind the ON CLAUSE filters what is seen from the table you are OUTER JOINING. Also, another item to remember is that any condition on the OUTER JOINED table in the WHERE clause you not only adversely affect your performance, but what data is returned as well.