Thread: INNER JOIN query with Postgre 6.5

I'm also running Cold Fusion on Linux and INNER JOIN is working quite
fine :)

But the syntax is not the same as in MS Access.

It should be:
<CFQUERY DATASOURCE="#datasource#" NAME="GetProducts">
SELECT Products.*, ProductInfo.*
FROM Products, ProductInfo
WHERE GroupID = #GroupID# AND ProductInfo.Show = 'x' AND
Products.ProductID = ProductInfo.ProductID
ORDER BY ProductInfo.InfoID
</CFQUERY>

Notice that the "ON Products.ProductID = ProductInfo.ProductID" has been
moved to the WHERE clause.

You better tell Allaire this so they won't misinform other CF+PostgreSQL
users....

Poul L. Christiansen

> Aarni Ruuhimäki wrote:
>
> Hi everybody !
>
> I have been reading Postgre-Novice mailing list for a little while now
> and got answers to all of my questions. Million thanks to everybody !
>
> I have RedHat6.2, Postgre 6.5, Cold Fusion 4.5 Pro Server for Linux
> and Apache up and running quite OK now, still a lot to learn, coming
> from Windows world.
>
> I ran into a problem again:
>
> When executing a Cold Fusion template with INNER JOIN query I get an
> error saying JOIN: expression not implemented yet.
>
>
> This is my query in cfml:
>
> <CFQUERY DATASOURCE="#datasource#" NAME="GetProducts">
>  SELECT Products.*, ProductInfo.*
>  FROM Products
>  INNER JOIN ProductInfo
>  ON Products.ProductID = ProductInfo.ProductID
>  WHERE GroupID = #GroupID# AND ProductInfo.Show = 'x'
>  ORDER BY ProductInfo.InfoID
> </CFQUERY>
>
> The idea is to read (into a web page / shopping cart) basic product
> info from one table and all different sizes ,colors etc and their
> different prizes for the same product from another table. Sizes,
> colors etc. then populate a <SELECT> drop-down list for the customer
> to choose. The query works fine in NT and Access drivers.
>
> This is the error Cold Fusion gives:
>
>
> Error Diagnostic Information
>
> ODBC Error Code = 08S01 (Communication link failure)
>
> Error while executing the query; ERROR: JOIN expressions are not yet
> implemented
>
> The error occurred while processing an element with a general
> identifier of (CFQUERY), occupying document position (27:1) to
> (27:52).
>
> Date/Time: Tue Oct 24 23:39:21 2000 Browser: Mozilla/4.72 [en] (X11;
> U; Linux 2.2.14-5.0 i586) Remote Address: 127.0.0.1 HTTP Referer:
> http://localhost/vasen.cfm Query String: GroupID=3
>
>
>
> This is what Allaire said:
>
> >>>>>>>>
>
> Sounds like the PostgreSQL DB doesn't support this. Can you do it from
> the psql command line program?
>
> A quick search on www.postgresql.org turned up some join related info,
> but nothing conclusive.
>
> Allaire Development
> >>>>>>>>
>
> Is it really true that Postgre does not support INNER JOIN ?
>
> How can I try that from the psql command line program ?
>
> Where can I look for Postgre error logs on this ?
>
> Searching the site I found something on this, it seems that JOIN
> queries are possible ?
>
>
>
>
>
> Best regards,
>
>
>
> Aarni