CODEWITHSUNDEEP
mysqlsql
user2298075
user2298075

Reputation: 19

Mysql query order columns by order of rows in linked table

i create these tables in

https://www.jdoodle.com/execute-sql-online/

I have the coldefinition assigned to fix cols in the data table, but change the order in the column definition.

here is am saving color in column2 in data, but displaxin it in col3 in table browser, row fater is in third col in data, but in seocnd col in browser display.

insert into coldef values('test','color', 2,3);
insert into coldef values('test','type', 3,2);

I can get the columns order with the last max query, and display the correct order,

BUT

This is the query that return the doldef query in the correct order:

name2|type2|color2|make2|

I will need to swap the col 2 and 3 in my query for the data table, so that the order is consistant with the definition

how can I union the data table, so that the order of the columns will be in same order as the column definition this is the normal uery result

test2|2name one|2red|2type1|2make1
test2|2name two|2blue|2type2|2make2
test2|2name three|2orange|2type3|2make3

thanxs for any advice

create table coldef(tablename chr, name int, colnr int, colorder int);
create table data(tablename chr, '1' chr, '2' chr, '3' chr, '4' chr);
insert into coldef values('test','name', 1,1);
insert into coldef values('test','color', 2,3);
insert into coldef values('test','type', 3,2);
insert into coldef values('test','make', 4,4);
insert into coldef values('test2','name2', 1,1);
insert into coldef values('test2','color2', 2,3);
insert into coldef values('test2','type2', 3,2);
insert into coldef values('test2','make2', 4,4);
insert into data values('test','name one','red','type1','make1');
insert into data values('test','name two','blue','type2','make2');
insert into data values('test','name three','orange','type3','make3');
insert into data values('test2','2name one','2red','2type1','2make1');
insert into data values('test2','2name two','2blue','2type2','2make2');
insert into data values('test2','2name three','2orange','2type3','2make3');
select * from coldef;
select * from data;
select
  max(case when colorder = 1 then name end) name,
  max(case when colorder = 2 then name end) name,
  max(case when colorder = 3 then name end) name,
  max(case when colorder = 4 then name end) name,
  max(case when colorder = 5 then name end) name
from coldef coldef where tablename= 'test2';

Upvotes: 0

Views: 44

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

When you do an insert, always list the column names:

insert into coldef (name2, color2, type2, make2)
    values('test', 'color', 2, 3);

Upvotes: 1

Related Questions