Join 2 Tables and show fields in only one of the tables without listing all in the query + MYSQL

Hi Fellas,

Yea, I know it's been a while! Been totally swamped with work and hanging out with my signifacant other ;)..

Okay I know you are all not interested in my romantic life so I will go straight to the point.

I promise this will be quick.

If you want to join 2 tables A and B; and you want to see all the fields in table A but none in table B, and you don't want to list the columns (who can be bothered, right?), WELL.....

All you have to do is:

SELECT A.* FROM A JOIN B on A.id = B.id;

This will return all the fields contained in table A.


There you have it, guys!!!! I told you this would be quick!
:)

How to Add Columns to a Table in particular positions in MYSQL

Hi Guys,

I discovered something amazing last week! I never thought this was possible but it sure is.

Okay, I had this table (fictional for my own protection) ;)

CREATE TABLE Employee
(EmployeeID INT,
Surname VARCHAR(50),
FirstName VARCHAR(50),
PhoneNumber VARCHAR(50));

Now, I realised I wanted to add more columns but in-between the already existing columns. I wanted to add DOB after FirstName and Address after DOB. Of course, I know this is possible using whatever GUI you are working with (e.g. SQLYog, Toad for MYSQL, Navicat etc). But I really wanted to write it using codes. So here is what you do:

ALTER TABLE Employee
ADD COLUMN DOB Date AFTER FirstName,
ADD COLUMN Address VARCHAR(255) AFTER DOB;


C'est fini!

Oh the joy!!!

Have a lovely day, y'all!

How to Check if an Excel field contains a particular text

Hi Guys,

Its been a while. Been swamped with work and what-nots.

Anyhoo (does that word exist?), straight to business! To check if an Excel column/field contains a particular text,  use the function below: I am looking for the text 'Ltd' in my fields.

= IF(ISNUMBER(SEARCH("Ltd",A2)),"Limited","NotLimited")


The screen below shows this clearly.

Any company with the text Ltd is flagged as 'Limited' in Column B.


-------------
Note below: 
If you want to check if one column is found in another column, you can just replace the "Ltd" with your field value. E.G.


= IF(ISNUMBER(SEARCH(E2,F2)),"Exists","NotExists")

Cheers fellows!

How to get the column with the greatest value out of many columns in MYSQL

This is funny to me! Cos the answer is in the question, as well as the title of the post.



Heheeheeeee..


Okay, If you have a table of customers containing different columns/fields such as 
- ID
- CustomerName
- JanuarySales
- FebruarySales
- MarchSales
- AprilSales
- MaySales
- JuneSales


You want to get for each customer, the month with the highest Sales. Use the below:


SELECT ID, CustomerName, GREATEST( JanuarySales,  FebruarySales,  MarchSales,  AprilSales,  MaySales,   JuneSales) AS HighestSales FROM Customer; 


This returns the highest of all the sales.


I told you the answer was right in the title!!! Permit me to LOL!! Cos I'm well excited....


Cheers, guys!

How to Order Data by specific field values in MYSQL

Hi Guys,

Yeah I know it's been a while. I've been totally swamped with work. I'm soooo glad its Friday as I'm looking forward to an amazing stress-free weekend!!

Okay, enough rambling! Today, I just want to share this easy and straight-forward information with you.
If you work with MYSQL and you want to retrieve and order your data by specific values, there is a simple syntax to use in your query.

As always, I will give an example.
Say you have a table with customers like below:

And you want to retrieve data from this table ordering by occupation, but with the doctors first, followed by lawyers, teachers, and finally engineers.

SELECT ID, FirstName, Surname, Occupation FROM customer ORDER BY FIELD(Occupation,'Doctor','Lawyer','Teacher','Engineer');
 
The query above returns the following:

One more thing:

If you want to order with 'Teachers' being the priority, but you don't care how the other values are ordered, use the following:

SELECT ID,FirstName,Surname,Occupation FROM customer ORDER BY FIELD(Occupation,'Teacher') DESC;

Finally, if you want to order first by 'Teacher' and then the others ordered alphabetically by occupation, use the below:

SELECT ID,FirstName,Surname,Occupation FROM customer ORDER BY FIELD(Occupation,'Teacher') DESC,Occupation;

And there you have it!!

Hope this proves useful sometime!

Cheers, fellows!!!

Sort list by simply clicking the header in MS Excel

Hi Guys,

I would like to show you how to sort your data in excel without having to go to the Sorts Tab  and wasting your own time.
You have to use a macro to do this though. Okay , down to business!..

Lets say you have this data:

You want to sort this data by id, name or even by age. All you have to do is create, save and run the following macro. I already described how to create and run a macro in my previous post, so please check if you dont know how to.

Anyway, here is the code to sort by name:
-----------------------------------------------
Dim YUpDown As Long
Sub SortY()

        If (YUpDown = 0) Then
          Range("G1:I8").Sort Key1:=Range("H2"), Order1:=xlAscending, Header:= _
         xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
         DataOption1:=xlSortNormal
        
             YUpDown = 1
         Else
             Range("G1:I8").Sort Key1:=Range("H2"), Order1:=xlDescending, Header:= _
         xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
         DataOption1:=xlSortNormal
        
             YUpDown = 0
         End If
End Sub

---------------------------------------------------------
As you can see, my data is within the range of cells G1 to I8 in my excel sheet and my macro code reflects this.

- Next step, you can go to shapes and pick any shape you like, I chose an arrow which I would click on to sort my data. Place your shape on your header/title and right click on the shape.
- Click on 'Assign Macro' and Choose your macro from the list.
- After this, click on your arrow and your data is automatically sorted. If you also want to sort by other fields e.g. age(in my case), go back to your code and duplicate the above code. For the bit that says 'Sort Key1:=Range("H2")', put whatever field you want to sort by, and save. I used H2 because I wanted to sort by name.

- After this, with a single click, you can sort by any of your fields. If you click once, you sort in ascending order. If you click again, it changes to descending order.

Below are examples of my sorting.

First by id in ascending order

 Next by name in descending order:

Next by age in ascending order:

And there you have it!

If you have any questions just hit me up!

Cheers fellows!!!

Using 'BETWEEN' and 'AND' to retrieve dates in MYSQL

Hi Guys,

Im at work now so I have to be quick!!

Quick word of advice before I forget! I was working on a piece of MYSQL code just now and I realised something. When retrieving data within a specific period, if your datatype for the field to specify is 'timestamp' or 'datetime', PLEASE, convert to date first before you specify the interval.

Example
----------
Customer table has fields:
id int,
name varchar(50),
createdTime  timestamp;

To retrieve data for customers who were created between Jan1,2012 and Jan 31, 2012, use;

select * from Customer where DATE(createdTime) BETWEEN '2012-01-01' AND '2012-01-31'; 

If you don't use the DATE function, for some weird reason, some of the right data is excluded from your select.


Alright, gotta go!
Cheers, fellows!