SQL & Sybase

[Raptors Fan]

Hi all,

I am querying a Sybase database in Access through an SQL - Pass Through Query. The problem is that I need to run a LEFT OUTER JOIN but for some reason I get errors using the key words INNER JOIN or LEFT OUTER JOIN when querying the Sybase database. My guess is maybe it doesn't support these keywords.

My sql is long so I will give you a made up example to show you what I mean:

SELECT P.prod_id, P.name, P.desc, K.key_code
FROM dbo.product P LEFT INNER JOIN dbo.prod_keyword K
ON P.prod_id = K.prod_id

I want it to return the full set of data from the PRODUCT table. However, the PROD_KEYWORD table has prod_id and key_code fields... thus containing only the products that have been given a keyword.

This is what I originally had:

SELECT P.prod_id, P.name, P.desc, K.key_code
FROM dbo.product P, dbo.prod_keyword K
WHERE P.prod_id = K.prod_id

The above however does not return all products, only those products that appear in both tables. So, I did some reading and found out about OUTER JOINS.

I also have tried the following but it doesn't work:

SELECT P.prod_id, P.name, P.desc, K.key_code
FROM dbo.product P, dbo.prod_keyword K
WHERE P.prod_id(+) = K.prod_id

But again I get an error.

Please help me, I'm stuck, I can't see how else to write this query so it will return all the products, AND show the key_code of those products that have been assigned one.

Maybe there is another way to write the query other than doing an OUTER JOIN?

Many thanks,

RF

P.S. By the way, I didn't create the tables, and they cannot be changed.

[Clearcode]

The syntax for an inner join in Sybase is *= i.e.

SELECT P.prod_id, P.name, P.desc, K.key_code
FROM dbo.product P, dbo.prod_keyword K
WHERE P.prod_id *= K.prod_id




HTH,
Duncan

-------------------------------------------------
Ex. Datis: Duncan Jones
Merrion Computing Ltd
http://www.merrioncomputing.com

[Raptors Fan]

Thanks a lot!