LEFT JOIN problem

[Guy Brom]

I have the following tables:

languages
language_id int,
language_title varchar(80)

objects
object_id int
object_language_id int
object_title varchar(100)

I want to select ALL available languages, and match the translated object 
(if it is exists) for a specific object_id. If it does not exists for that 
specific language_id, I want to have NULL.


Because I'm using mysql 4.0, no subqueries allowed. Is there any way to 
rewrite the following?


SELECT
language_id,
language_title,
(SELECT object_title FROM objects WHERE object_id = ? AND object_language = 
l.id) AS object_title

FROM
languages l

Thanks! 



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

compound foreign key(s)

[Guy Brom]

I have the following innoDb tables:
country has `id` as PK
lang has `id` as PK

I want to have a 3rd innoDb table:
countrylang with `cid,lang` as compound key with both `cid` and `lang` as 
foreign (cascade-delete) keys to the master tables.


However mysql throws error (Cannot create table..) when I try to define 
`cid` as a foreign key to country.id


country.id and countrytext.cid are both smallint(5)

Any help? 



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]