sql left join for two tables using a CONCAT string as the argument

Question

I need to get a title from table 2, table 2 has title and id column. Table 1 has some data and three of these columns concatenated together makeup the id that can be found in table 1.

I used CONCAT_WS() function and gave this column an alias name and need to use the Alias for the on argument(At least this is what I understood I needed to do)

I thought this could be a simple left join, yet it is not working for me.

This is my query

SELECT
    table_openers.mail,
    table_openers.f_name,
    table_openers.l_name,
    table_openers.Quality,
    CONCAT_WS('-',
            table_openers.esp,
            table_openers.acc,
            table_openers.group) as 't1aid',
    table_groups.aid,
    table_groups.group_name
FROM
    lance_mailstats.table_openers
        LEFT JOIN
    lance_mailstats.table_groups ON table_groups.aid = t1aid;

I get results for mail, f_name, l_name, Quality and t1aid, but the aid and group_name columns of the second table return null.

If I dont use the Alias 't1aid' and instead use the full CONCAT this works. which is fine and it means I could move on... but how would one perform this operation with aliases? — Beyerz, Mar 13 '12 at 10:48
You cannot use aliases from the field list within the ON clause. — user1191247, Mar 13 '12 at 11:16
This nasty join is indicative of some issues with your database structure. It sounds like you definitely need to normalise your tables. This may work ok now but it will bite you in the backside when your dataset grows. — user1191247, Mar 13 '12 at 11:19
I am struggling to find a reference for this but I am fairly sure that it is because the JOIN is evaluated before the field list. I am sure someone will correct me if I am wrong. — user1191247, Mar 13 '12 at 15:24

Luca · Accepted Answer · 2012-03-13T20:28:43.543

I feel like you can't use an alias in the ON clause. Try doing

LEFT JOIN
    lance_mailstats.table_groups ON table_groups.aid = CONCAT_WS('-',
            table_openers.esp,
            table_openers.acc,
            table_openers.group);

"You can use the alias in GROUP BY, ORDER BY, or HAVING clauses to refer to the column" (from dev.mysql.com/doc/refman/5.0/en/problems-with-alias.html).

And "The conditional_expr used with ON is any conditional expression of the form that can be used in a WHERE clause" (from dev.mysql.com/doc/refman/5.1/en/join.html).

So as a logical inference you're not allowed to use aliases in ON clauses.

This is what I have done in the end and it solves my needs, Im just concerned for when I get to more complex queries. — Beyerz, Mar 13 '12 at 12:11

score 0 · Answer 2 · answered Mar 13 '12 at 12:01

try to use a subquery.. it goes like this.........

ex.

SELECT tbl1.mail, tbl1.f_name, tbl1.l_name,tbl1.Quality, tbl1.t1aid,table_groups.aid, table_groups.group_name

    FROM

       (SELECT
              table_openers.mail,
              table_openers.f_name,
              table_openers.l_name,
              table_openers.Quality,
                CONCAT_WS('-',
                   table_openers.esp,
                   table_openers.acc,
                   table_openers.group) as 't1aid',
             FROM
             lance_mailstats.table_openers )tbl1

       LEFT JOIN
       lance_mailstats.table_groups ON table_groups.aid = tbl1.t1aid;

tried this out, but the sub-query also didn't work out for me. I think it just comes down to not being able to use aliases for the argument in a join. But This really doesn't sound plausible. but I couldn't find any documentation on it. Has anyone else found any documentation? — Beyerz, Mar 13 '12 at 13:37

sql left join for two tables using a CONCAT string as the argument

2 Answers2