INSERT INTO Return value // LAST_INSERT_ID() problem

Hi everybody,

I'm working on a relational database and am facing a problem to build
a SQL query which in ONE query that updates all the necessary table
fields or fetches the appropriate data from existing entries.

I have an organization table, an email table and a user table. When I
create a user I bind that user to the specified organization and to
the specified email address. In some cases the organization or email
address already exists in the database and hence the new user should
be linked to the existing entrie. I can't use transactions due to
other reasons. My idea of a SQL for that job looks something like
this:

SELECT
IF (
`email`.`email_id` IS NOT NULL,
`email`.`email_id`,
(SELECT IF (((INSERT INTO `email` (`email_address`) VALUES
('info@example.org')) = ''), -1, LAST_INSERT_ID()))
) as `email_id`,
IF (
`organization`.`organization_id` IS NOT NULL,
`organization`.`organization_id`,
(SELECT IF (((INSERT INTO `organization`
(`organization_name`,`organization_url_name`) VALUES ('Example org',
'exampleorg')) = ''), -1, LAST_INSERT_ID()))
) as `organization_id`
FROM
`email`, `organization`
WHERE
`email`.`email_address` = 'info@example.org'
AND
`organization`.`organization_url_name` = 'exampleorg'

If the value for email_id is unequal to NULL than we select email_id
else we insert a new entry in the email table and return the
LAST_INSERT_ID. Same goes for the organization table

I understand that the problem lies within the SELECT IF(INSERT
INTO[....]) stuff but it is not entirely clear to me why..

Can somebody help me out here? Do you have all the required info? Let
me know.

Thanks in advance.

Marijn

> I have an organization table, an email table and a user table. When I
> create a user I bind that user to the specified organization and to
> the specified email address. In some cases the organization or email
> address already exists in the database and hence the new user should
> be linked to the existing entrie.

See the thread "Return Values From INSERT" just a few days ago.

Regards,
--
Willem Bogaerts

Application smith
Kratz B.V.
http://www.kratz.nl/