Avatar of Lou1
Lou1
Flag for United States of America

asked on 

ERROR: RIGHT JOIN is only supported with merge-joinable join conditions

i have the following query:

SELECT DISTINCT
               orderitem.orderitemid,
               shippableitem.shippableitemid,
               shippableitem.title,
               shippableitem.scancode,
               shippableitem.author,
               suppliertype.description,
               incomingorder.incomingorderid,
               incomingorder.referencenumber,
               incomingorder.invoicenumber,
               incomingorder.dateordered,
               incomingorder.billingtypeid,
               incomingorder.studentid,
               itemtype.description AS itemtype,
               orderitem.copiesordered,
               orderitem.copiesreceived,
               orderitem.cost,
               orderitem.comments,
               course.prefix,
               course.coursenumber,
               courseinstance.instanceidentifier,
               orderitemstatus.description AS orderitemstatusdesc
   FROM
       course,
       courseinstance,
       incomingorder LEFT JOIN supplier ON supplier.supplierid = incomingorder.supplierid
                             LEFT JOIN suppliertype ON (supplier.supplierid = incomingorder.supplierid AND suppliertype.suppliertypeid = supplier.suppliertypeid),
       itemtype,
       orderitem,
       orderitemstatus,
       shippableitem
  WHERE
       incomingorder.incomingorderid = orderitem.incomingorderid
  AND  shippableitem.shippableitemid = orderitem.shippableitemid
  AND  courseinstance.courseinstanceid = orderitem.courseinstanceid
  AND  course.courseid = courseinstance.courseid
  AND  orderitemstatus.orderitemstatusid = orderitem.orderitemstatusid
  AND  itemtype.itemtypeid = shippableitem.itemtypeid;


and i am getting the error:

"ERROR:  RIGHT JOIN is only supported with merge-joinable join conditions"

on the following line:

"LEFT JOIN suppliertype ON (supplier.supplierid = incomingorder.supplierid AND suppliertype.suppliertypeid = supplier.suppliertypeid),"

I know this because when i remove this line it works fine, but when i add it back i get the error.

Why does it say RIGHT JOIN in the error message? I don't have a RIGHT JOIN anywhere in the query.

I am using PostgreSQL 7.4.3.

Thanks much.
PostgreSQL

Avatar of undefined
Last Comment
Lou1

8/22/2022 - Mon