CODEWITHSUNDEEP
mysqlsqlview
alirakiyan
alirakiyan

Reputation: 428

How can I create a view with variant columns in mysql

I have table tbl_settings that always has only one record. and a field show_messages_in_log in it. and another table tbl_logs stores all logs. Of course, tbl_logs has a message field.

I need a view to show logs:

Can any body help me please?

Upvotes: 0

Views: 286

Answers (3)

ShyJ
ShyJ

Reputation: 4650

Maybe just join the tables, like this

create table tbl_settings 
(show_messages_in_log boolean);

create table tbl_logs
(message varchar(200));

insert into tbl_logs
values ('msg1'), ('msg2');

insert into tbl_settings 
values (true);

select tbl_logs.message
from tbl_logs inner join tbl_settings on tbl_settings.show_messages_in_log = TRUE

This will return all the rows.

update tbl_settings set show_messages_in_log = false;

select tbl_logs.message
from tbl_logs inner join tbl_settings on tbl_settings.show_messages_in_log = TRUE

This will return no row at all.

Upvotes: 1

phoniq
phoniq

Reputation: 238

IF tbl_settings.show_messages_in_log = TRUE THEN tbl_logs.message
ELSE NULL
END IF

Upvotes: 0

user330315
user330315

Reputation:

You can't. In a relational database relations (tables, views) always have to have a fixed number of columns.

The only thing you can do is to always have that column, but display it as NULL in the view. For that you will need to join the two tables and the use a case expression in the view:

case 
   when show_messages_in_log = 'true' then tbl_logs.message
   else null
end as message

Upvotes: 1

Related Questions