Mysql Inner join with concat_ws

Question

I have 2 table.

first table

 | idgroup | namegroup

second table

 | idrequest | col1 | col2 | N1 | N2 | date_extract  |

I want to join the tables with concat_ws

    SELECT
    tb1.*,tb2.*,
    CONCAT_WS("_",tb2.N1, tb2.N2) AS GR,
    FROM
    table2 tb2
    INNER JOIN table1 tb1 ON tb1.namegroup= tb2.GR
    WHERE
    tb2.date_extract = "2015-02-13"

Is it possible? then how?

What happens when you execute this query? Do you see errors? — TJ-, Feb 16 '15 at 08:24

Ataboy Josef · Accepted Answer · 2015-02-16T10:46:17.430

0

I am giving a blind try. Nothing changed much except a date() conversion to tb2.date_extract-taking the date value if date_extract is of type TIMESTAMP or DATETIME.

SELECT
    tb1.*,tb2.*,
    CONCAT_WS("_",tb2.`N1`, tb2.`N2`) AS `GR`,
    FROM
    table2 tb2
    INNER JOIN table1 tb1 ON tb1.`namegroup`= tb2.GR
    WHERE
    date(tb2.`date_extract`) = "2015-02-13"

If this is not your problem, then please post the error message with the question.

UPDATE: You can simply use GR instead of tbl2.GR need to use the namespace for the result

SELECT
    tb1.*, tb2.*, CONCAT_WS("_",tb2.`N1`, tb2.`N2`) AS `GR`,
    FROM table2 tb2
    INNER JOIN table1 tb1 ON tb1.`namegroup`= `GR`
WHERE
    date(tb2.`date_extract`) = "2015-02-13"

-now let me know how it goes?

edited Feb 16 '15 at 10:46

answered Feb 16 '15 at 08:48

Ataboy Josef

2,087
3
22
27

[Err] 1054 - Unknown column 'tb2.GR' in 'on clause' – lbottoni Feb 16 '15 at 08:55
Okay. The problem is with your JOIN(grouping columns for JOIN condition) – Ataboy Josef Feb 16 '15 at 09:01
usermesam23 have you the solution sql? – lbottoni Feb 16 '15 at 09:13
Please show us the CREATE TABLEs you are dealing with ! – Rick James Feb 17 '15 at 02:29
Work if i use "LEFT OUTER JOIN" Thank very much to all – lbottoni Feb 17 '15 at 07:12

Mysql Inner join with concat_ws

1 Answers1