Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2010

    Unanswered: Question on SQL Statement

    Dear forum,

    can you help me on this query?

    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?


  2. #2
    Join Date
    Apr 2006
    Provided Answers: 11
    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-V11 Fundamentals- DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified

  3. #3
    Join Date
    Dec 2007
    Richmond, VA
    Provided Answers: 5
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts