SQL and 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.

[Cakkie]

Hmm, strange, SysBase is ANSI entry-level compliant, which means it should know JOINS, besides, joins are a key in a relational database system.
About the inner join, the left keyword isn't ansi, so it could be that Sysbase doesn't accept a left or right keyword with the inner join. That's probably because an inner join means that both tables must have matching records.
About the outer join, Sysbase should know the syntax
SELECT P.prod_id, P.name, P.desc, K.key_code
FROM dbo.product P LEFT OUTER JOIN dbo.prod_keyword K
ON P.prod_id = K.prod_id


If it doesn't, what is the exact error message?

Tom Cannaerts
[email protected]

Programming today is a race between software engineers striving to build bigger and better idot-proof programs, and the universe trying to produce bigger and better idiots. So far, the universe is winning -- Rich Cook

[Clearcode]

The Sybase syntax 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 Clearcode and cakkie for responding. Thanks Clearcode for providing the answer.