MySql not displaying all columns in the result I am trying to teach myself DBA. when I execute this query: 

SELECT *, CASE WHEN Trans_type LIKE '%C%' 
             THEN 'Credit Card' 
             ELSE NULL 
          END AS type 
FROM Transactions 
WHERE cust_no=2 &amp;&amp; Trans_type LIKE '%C%';


I get the following:

+---------+------------+-------------------+----------------------+--------+------------+-------------+
| cust_no | date       | Category          | Subcategory          | Amount | Trans_Type | type        |
+---------+------------+-------------------+----------------------+--------+------------+-------------+
         | Credit Card | Bills &amp; Utilities | Bills &amp; Utilities Ot | 5      | C
         | Credit Card | Household/Home    | DIY/Home Improvement | 15     | C
         | Credit Card | Shopping          | Clothing &amp; Accessori | 215    | C
         | Credit Card | Shopping          | Clothing &amp; Accessori | 135    | C
+---------+------------+-------------------+----------------------+--------+------------+-------------+

4 rows in set (3.53 sec)


Why are the cust_no, date not displaying and also the CASE type displaying at the start in my mysql console?

Thanks.

The Table Transactions Description below:

+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| cust_no     | varchar(10) | YES  | MUL | NULL    |       |
| date        | date        | YES  |     | NULL    |       |
| Category    | varchar(20) | YES  |     | NULL    |       |
| Subcategory | varchar(20) | YES  |     | NULL    |       |
| Amount      | varchar(6)  | YES  |     | NULL    |       |
| Trans_Type  | varchar(5)  | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
Kind of looks like there's a CR (carriage return, ASCII 13) after the C in Trans_Type. Try specifying the column names, and put that first; it would disappear, but the other columns would be visible.

You can explicitly check for non-printing characters like this using the ORD() function. Let's say Trans_Type is a CHAR(3) (or NCHAR(3)):

SELECT ORD(SUBSTR(TransType,1,1)) as FirstChar
      ,ORD(SUBSTR(TransType,2,1)) as SecondChar
      ,ORD(SUBSTR(TransType,3,1)) as ThirdChar
  FROM Transactions 
 WHERE cust_no=2 &amp;&amp; Trans_type LIKE '%C%';


would show you the character codes for each character; check the appropriate code page to see what the characters actually are (as noted, for ASCII, a 13 represents a carriage return, which would move the cursor to the start of the current line in Windows at least).

If this is the problem, you should be able to use REPLACE(Trans_Type, @BadChar, '') to remove the offending character from your results. For ASCII and the carriage return character, @BadChar would actually be CHAR(13 using ascii). So, your query would be:

SELECT `cust_no`, `date`, `Category`, `Subcategory`, `Amount`
      ,REPLACE(Trans_Type, CHAR(13 USING ascii), '') as Trans_Type
      ,CASE WHEN Trans_type LIKE '%C%' 
         THEN 'Credit Card' 
         ELSE NULL 
       END AS type 
  FROM Transactions 
 WHERE cust_no=2 &amp;&amp; Trans_type LIKE '%C%';


Note: mostly untested, as I don't have your data.