Mysql – full outer join not working

join;MySQL

I Want to do a full outer join on 2 tables but I get this error, what am I doing wrong?

mysql> select * from cajas full outer join almacenes on almacenes.codigo = cajas.almacen;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'outer join almacenes on almacenes.codigo = cajas.almacen' at line 1
mysql>

Best Answer

So far as I know, you can't do a full outer join in MySQL, so just run it as the UNION of a LEFT JOIN and a RIGHT JOIN as

SELECT * FROM 
cajas LEFT JOIN almacenes ON almacenes.codigo = cajas.almacen
UNION
SELECT * FROM 
cajas RIGHT JOIN almacenes ON almacenes.codigo = cajas.almacen;

I think this would fix your problem.

EDIT: After a quick Googling, I realized that the above may not be perfect for all cases. Instead you could do

SELECT * FROM 
cajas LEFT JOIN almacenes ON almacenes.codigo = cajas.almacen
UNION ALL
SELECT * FROM 
cajas RIGHT JOIN almacenes ON almacenes.codigo = cajas.almacen
WHERE cajas.almacen IS NULL;

See this blog post for reference.

Related Solutions

Mysql – Query WHERE clause and JOIN

Here is your original query

SELECT * FROM `CallDetailRecord`
WHERE `StartTime` >=1357102799000
AND `StartTime` <=1357880399000
JOIN `CallEvent` ON `EventID` = `CallEventID`
LIMIT 1000

In theory, you can do the following

Subquery that has WHERE on the StartTime
Add ORDER BY StartTime
Do the LIMIT inside the subsquery
Specify a LEFT JOIN

Here is such a query

SELECT * FROM
(
    SELECT * FROM `CallDetailRecord`
    WHERE `StartTime` >=1357102799000
    AND `StartTime` <=1357880399000
    ORDER BY StartTime
    LIMIT 0,1000
) A LEFT JOIN `CallEvent` B ON `EventID` = `CallEventID`;

If the query is slow, then add an index on StartTime

ALTER TABLE CallDetailRecord ADD INDEX (StartTime);

Give it a Try !!!

MYSQL Trigger not working

The first problem is that you don't have an END IF;.

The next problem (assuming that you aren't doing this and just didn't include it in the question) is that when you have a compound statement, you have to change the statement delimiter from ; to something else so that the client will send the entire CREATE TRIGGER statement to the server as a single statement.

Finally, the column "first_name" probably needs to be qualified with NEW (assume you're referencing a column in wp_usermeta called "first_name".

DELIMITER $$
DROP TRIGGER IF EXISTS update_customers $$
CREATE TRIGGER update_customers AFTER INSERT ON wp_usermeta
FOR EACH ROW 
BEGIN
  IF NEW.wp_metakey = NEW.first_name THEN 
    INSERT INTO test_customer(first_name) values (NEW.wp_meta_value);
  END IF;
END $$
DELIMITER ;

Best Answer

Related Solutions

Mysql – Query WHERE clause and JOIN

MYSQL Trigger not working

Related Question