Sql Join syntax on two tables MySql

Question

I use as RDBMS the version 5.5.24-log of MySql.

With this sql query in output I have all records presents on the two tables tbl_1 and tbl_2:

SELECT * FROM `tbl_1` CB
JOIN `tbl_2` A ON A.xCode = CB.xCode
AND RIGHT (A.xElement, 1) = CB.xElement
WHERE
    CB.xType IN ('A')
AND MONTH(xDate) BETWEEN 1 AND 4
ORDER BY xDate DESC;

Now I need extract from tbl_1 all records not presents in tbl_2 and I have tried this sql query but I have error

Lost connection to MySQL server during query

SELECT i.* FROM
    `tbl_1` i
LEFT JOIN `tbl_2` o ON o.xCode = i.xCode_cabina
AND RIGHT (o.xElement, 1) = i.xElement
WHERE
    i.xType IN ('A')
AND MONTH(xDate) BETWEEN 1 AND 4
AND o.xElement IS NULL ;

Can you please help me figure out the problem?

Thanks in advance.

Are you having internet connectivity issues? – Eli Sadoff May 27 '16 at 14:42 — Eli Sadoff, May 27 '16 at 14:42
Consider using `WHERE NOT EXISTS` instead of a join. – Niet the Dark Absol May 27 '16 at 14:42 — Niet the Dark Absol, May 27 '16 at 14:42

score 0 · Answer 1 · answered May 27 '16 at 14:52

0

Select i.* From tbl_1 i WHERE i.xType in ('A') and MONTH(i.xDate) bewteen 1 and 4 and i.xCode_cabina not exist (select o.xCode from tbl_2 o where o.xElement is null);

answered May 27 '16 at 14:52

Vince De Giorgio

126
5

thanks but in your syntax [Err] 1064 - 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 'EXISTS ( – Antonio Mailtraq May 27 '16 at 15:03

score 0 · Answer 2 · edited May 23 '17 at 12:15

0

Are you looking to do something like this?

SELECT * FROM tbl_1 
WHERE xCode NOT IN (SELECT xCode FROM tbl_2)

Source: Mysql: Select rows from a table that are not in another

edited May 23 '17 at 12:15

Community

1
1

answered May 27 '16 at 14:59

Justin Lachapelle

61
1
10

Thank you, but in my case I need also check the column **xElement** over that the column **xCode** – Antonio Mailtraq May 30 '16 at 06:28

Sql Join syntax on two tables MySql

2 Answers2