SELECT table_grouping_code, gui_field_code, gui_interface_id, dictionary_code, property_name, position INTO @test
FROM table_grouping_layout
WHERE company_code = "TEST";

SELECT table_grouping_code, gui_field_code, gui_interface_id, dictionary_code, property_name, position INTO @doximtrx
FROM table_grouping_layout
WHERE company_code = "DOXIMTRX";

select @test = @doximtrx;

I tried this, that seems very logica to me, but mysql says: ERROR CODE 1222: the used select statement have a different number of columns. How can it be possible?? I copied the query, only changing the code.

Example of data in the table

CodePudding user response：

You are trying to select many columns into a single variable. The correct syntax for that part of your query is:

SELECT
    GROUP_CONCAT(table_grouping_code ORDER BY table_grouping_code)
,   GROUP_CONCAT(gui_field_code ORDER BY gui_field_code)
,   GROUP_CONCAT(gui_interface_id ORDER BY gui_interface_id)
,   GROUP_CONCAT(dictionary_code ORDER BY dictionary_code)
,   GROUP_CONCAT(property_name ORDER BY property_name)
,   GROUP_CONCAT(position ORDER BY position)
FROM
    table_grouping_layout
WHERE
    company_code = 'TEST'
INTO
    @t1, @t2, @t3, @t4, @t5, @t6;

SELECT
    GROUP_CONCAT(table_grouping_code ORDER BY table_grouping_code)
,   GROUP_CONCAT(gui_field_code ORDER BY gui_field_code)
,   GROUP_CONCAT(gui_interface_id ORDER BY gui_interface_id)
,   GROUP_CONCAT(dictionary_code ORDER BY dictionary_code)
,   GROUP_CONCAT(property_name ORDER BY property_name)
,   GROUP_CONCAT(position ORDER BY position)
FROM
    table_grouping_layout
WHERE
    company_code = 'DOXIMTRX'
INTO
    @d1, @d2, @d3, @d4, @d5, @d6;

How to compare results is a different story:

SELECT ifNull(
      @t1 = @d1
  and @t2 = @d2
  and @t3 = @d3
  and @t4 = @d4
  and @t5 = @d5
  and @t6 = @d6
, false
) as result;

TEST DATA:

select * from table_grouping_layout;

QUERY RESULT:

CodePudding user response：

Variables are meant to hold one value. Neither one row nor one column nor one table.

Comparing two data sets can be achieved with a combination of UNIONand EXCEPT or with a full outer join. Unfortunately, MySQL does neither support EXCEPT nor FULL OUTER JOIN.

Here is a workaround:

with t as (select * from table_grouping_layout where company_code = 'TEST')
   , d as (select * from table_grouping_layout where company_code = 'DOXIMTRX')
select 'MISMATCH' as status, t.position
from table_grouping_layout t
join table_grouping_layout d 
  on t.position = d.position
  and not 
  (
    t.table_grouping_code <=> t.table_grouping_code  and
    t.gui_field_code      <=> t.gui_field_code       and
    t.gui_interface_id    <=> t.gui_interface_id     and
    t.dictionary_code     <=> t.dictionary_code      and
    t.property_name       <=> t.property_name
  )
union all
select 'TEST MISSING' as status, position
from d
where position not in (select position from t)
union all
select 'DOXIMTRX MISSING' as status, position
from t
where position not in (select position from d)
order by position;

Another approach using aggregation:

select
  position,
  case 
    when sum(company_code = 'TEST') = 0 then 'TEST MISSING'
    when sum(company_code = 'DOXIMTRX') = 0 then 'DOXIMTRX MISSING'
    when not
    (
      max(table_grouping_code) <=> min(table_grouping_code) and count(table_grouping_code) in (0,2)
      max(gui_field_code)      <=> min(gui_field_code)      and count(gui_field_code)      in (0,2)
      max(gui_interface_id)    <=> min(gui_interface_id)    and count(gui_interface_id)    in (0,2)
      max(dictionary_code)     <=> min(dictionary_code)     and count(dictionary_code)     in (0,2)
      max(property_name)       <=> min(property_name)       and count(property_name)       in (0,2)
    ) then 'MISMATCH'
    else 'MATCH' end as status
from table_grouping_layout
where company_code in ('TEST', 'DOXIMTRX')
group by position
order by position;