use CTE instead of subquery-CodePudding

I tried to re-write a SQL query using subquery to one using common table expression (CTE). The former is as below

select accounting_id, object_code, 'active', name
from master_data md
where md.id in (
    select MIN(md1.id)
    from master_data md1
    where md1.original_type = 'tpl'
    group by md1.object_code
);

The one below failed with

Error Code: 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 'distinct_object_code_id' at line 9

with distinct_object_code_id(id) as (
    select MIN(md1.id)
    from master_data md1
    where md1.original_type = 'tpl'
    group by md1.object_code
) 
select md.accounting_id, md.object_code, 'active', md.name
from master_data md
where md.id in distinct_object_code_id;

This one also failed

with distinct_object_code_id as (
    select MIN(md1.id)
    from master_data md1
    where md1.original_type = 'tpl'
    group by md1.object_code
) 
select md.accounting_id, md.object_code, 'active', md.name
from master_data md
join distinct_object_code_id using(id)
;

whereas this one below works (by explicitly specify the column_list)

with distinct_object_code_id(id) as (
    select MIN(md1.id)
    from master_data md1
    where md1.original_type = 'tpl'
    group by md1.object_code
) 
select md.accounting_id, md.object_code, 'active', md.name
from master_data md
join distinct_object_code_id using(id)
;

I tried another one but it also failed (using subquery inside CTE)

with distinct_object_code_id as (
    select accounting_id, object_code, 'active', name
    from master_data md
    where md.id in (
        select MIN(md1.id)
        from master_data md1
        where md1.original_type = 'tpl'
        group by md1.object_code
)
select * from distinct_object_code_id;

The MYSQL database version is 8.0.29-0ubuntu0.20.04.3.
what did I do wrong when using CTE?

EDIT: I add a third part, in which I inserted the data from those queries above into another table tpl_transferred_debtor like below

with distinct_object_code_id as (
    select MIN(id) as id
    from master_data
    where original_type = 'tpl'
    group by object_code
), 
tmp_tpl_table as (
    select md.accounting_id, md.object_code, 'active', md.name
    from master_data md
    join distinct_object_code_id using(id)
)
insert into tpl_transferred_debtor(debtor_id, tpl_code, status, description)
select * from tmp_tpl_table;

This one failed with

Error Code: 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 'insert into tpl_transferred_debtor(debtor_id,   tpl_code, status, description) sel' at line 12

I tried to write a simple one to check if the second CTE tmp_tpl_table and it is ok

with distinct_object_code_id as (
    select MIN(id) as id
    from master_data
    where original_type = 'tpl'
    group by object_code
), 
tmp_tpl_table as (
    select md.accounting_id, md.object_code, 'active', md.name
    from master_data md
    join distinct_object_code_id using(id)
)
select * from tmp_tpl_table;

CodePudding user response：

Your first query fails because you cannot use IN to reference cte you have to join or the correct syntax for IN ie in (select..from cte) Your second query fails because you didn't alias MIN(md1.id) Your third query works because you referenced MIN(md1.id) by providing (ID)

Personally I prefer join..on over join..using