I am new to SQL and I realize I have a lot to learn. I am in the midst of converting a set of ISAM files into MySQL tables. Some of the tables I am creating have hundreds of columns. To make it easier to see if my conversions are working properly, I would like to display only a subset of columns at a time in my SELECT results.
For example, I would like to see only Dates or only Amounts (i.e., Decimals). This would make it easier to see if correct values are in those fields.
I am working from the MySQL command line and I would rather not specify a long list of column names if it can be avoided. Is there a way to get my Select statements to display only the types of columns I am interested in?
I used a variant of Uueerdo's syntax to create a view:
create view dates_view as concat('select ', group_concat(column_name), ' ',' from ', table_schema, '.', table_name, ';') from information_schema.columns where table_name = 'binders_tbl' and data_type in ('date');
When I enter select * from dates_view; I get this value:
select binder_effectiveDate,binder_expirationDate,binder_submissionCreatedDate,binder_firstQuotedDate,binder_boundDate,binder_invoicedDate,binder_terminatedDate,binder_cancelledDate from aesdb.binders_tbl;
I tried but could not find a way to use this view with "select * from binders_tbl;" to display only the above date fields and their values.
If anyone can guide me to a "join" or "subquery" solution, I would appreciate it. For now, I am going to stick with listing all the individual columns as needed. Plus, I will study more basic and intermediate SQL. Thank you to those who responded.
I found a sort-of work-around to my problem
. Here it is in case it helps someone else:
CREATE VIEW DateCols_View AS SELECT Table1_SelectionCriteriaField, Table1_Date1, Table1_Date2, Table1_Date3 FROM Table1;
SELECT * FROM DateCols_View WHERE Table1_SelectionCriteriaField (matches some criteria);
My work-around has 2 drawbacks
. The first
is that I have to include any selection criteria fields that I plan to use later. But with a little forethought I can create multiple views with different sets of columns for different displays. This will let me display only those columns I wish to see while working with a set of records. I plan to create separate views for Dates, Amounts, Flags, etc.
The other drawback
is that I have to create a separate set of views for each table or join combination I plan to work with.
I hope as I learn more about SQL, I will find a way to generalize this technique (to make it more like a script or macro) and simplify it further.