SQL: Inner Join

Note: This is an experimental tutorial that I am building.

Welcome, my name is Alejandro and today we are reviewing SQL Inner Join.

When making a select query, using inner joins, we obtain a combination of rows from our tables. In other words, based on a join condition that matches obtain certain records coming from those tables. In our case, it is going to be between the table A and the table B.

For the table A, we have a list of clients and from the table B; we have a list of services. Both tables have fields known as columns and each column has records known as rows.

You should take particular attention to the field ID in both tables.This field is used to give a unique identifier to each of their records. Also, take attention to the ClientId field in the Services table. Yes. This field is a Foreign Key field, which holds IDs referring to the IDs in the Client table. We are going to match those IDs from both tables when doing the inner join. Below, I included an example of the result table that we wish to obtain.

Let us being by writing a basic select query. First, we are using the wildcard asterisk; this wildcard indicates that we wish to include all the fields, from all tables, used in our query. Second, we have a condition after the keyword ON. This condition will be used by the INNER JOIN to only pull records in which the ID of the client matches the ID in the ClientId fields from the Services table. The rest of the records should be ignored.

As you can see, we have the fields from the table clients, first; then, we have the fields from the services table.

At the Services table, I am showing an example of the record that will not be shown in the result table since there is not a match.

Now, this table does not resemble yet to the result table we wish to obtain. To display our table, as we want it, we need to indicate which fields from which tables we want to display. Plus, we must use the alias keyword AS, which allow us to show a different name for the fields in our result table.

Let’s see this working.

There are may online websites that will allow you to test your queries without requiring installing any software in your computer. One of my favorites is w3schools.com. This website has tutorials, references, quizzes and more.

Go to this link: W3Schools.com

On your right, you can see a list of tables that are at our disposition. We are going to duplicate what we did previously but using the tables Customers and Orders. From the table Customers, we only care about the fields CustomerId and CustomerName. From the table Orders, we only care for the fields OrderId and OrderDate. The following query will give us the results we wish to see:

Notices once again that we use the alias AS keyword to change the name for the column in our results.

Thank you for your time. I hope short instructional was useful for you. Since this is the first tutorial with video of a bunch I am planning to do. Your feedback is appreciated. Please have in consideration that this is a low budget production which I hope to improve in the long run as income and time allows it.

Share

JIRA: Useful Filters

Work Log Per Week

worklogAuthor = <Your Username> AND worklogDate >= startOfWeek() AND worklogDate <= endOfWeek()

Work Log Last Two Weeks

worklogAuthor = <Your Username> AND worklogDate >= startOfDay(-14d) AND worklogDate <= endOfDay()

All Tickets Where You Are the Assignee

assignee WAS currentUser()

All Defects Created Daily

issuetype in (Defect) AND “Group ID” IS NOT EMPTY AND “Environment” in (Production) AND createDate >= startOfDay(-1d)

Your Open Issues

assignee = currentUser() AND resolution = Unresolved order by updated DESC

Issued Viewed Currently

issuekey IN issueHistory() ORDER BY lastViewed DESC

Share

There are better options than using one array for names and another array for resources.

I encounter a piece of code which bugs me:

int[] images = {
		R.drawable.ic_image_1, R.drawable.ic_image_2, R.drawable.ic_image_3, R.drawable.ic_image_4, R.drawable.ic_image_5
		R.drawable.ic_image_6, R.drawable.ic_image_7, R.drawable.ic_image_8, R.drawable.ic_image_9, R.drawable.ic_image_10
};

String[] imagesNames = {
	"Image One", "Image Two", "Image Three", "Image Four", "Image Five", 
	"Image Six", "Image Seven", "Image Eight", "Image Nine", "Image Ten"
};

Later on, these two array are pass to all kind of method and constructors together for example:

    @Override
    public void onBindViewHolder(MasonryView holder, int position) {
        holder.textView.setText(imagesNames[position]);
        holder.imageView.setImageResource(images[position]);
    }

While this is correct, light on the memory, and use fewer CPU resources (less like of code run, smaller Big O), it welcomes all short of possible bugs, for example, lets say you add an image and forgot to add a name. You get mismatch arrays. Your code breaks. This issue is common if you are building these arrays dynamically; therefore, why not keeping the information together?

There are different ways to handle this

One way would be to create a class which holds these two items (image and image name). Then, you can create an array of objects.

private class Resource {
	public int image;
	public String name;
	Resource(String name, int image){
		this.image = image;
		this.name = name;
	}
}

Resource[] resources = {
	new Resources("Image One", R.drawable.ic_image_1),
	new Resources("Image Two", R.drawable.ic_image_2),
	new Resources("Image Three", R.drawable.ic_image_3),
	...
	new Resources("Image Ten", R.drawable.ic_image_10),
}

Then, you can iterate them:

    @Override
    public void onBindViewHolder(MasonryView holder, int position) {
        holder.textView.setText(resources[position].name);
	    holder.imageView.setImageResource(resources[position].image);
    }

Now, this is cleaner than before.

However, lets say you don’t wish to use a (public or private) class, then what to do? Well, you could use our old friend LinkedHashMap, which works like a HashMap; however, it keeps the order in which the elements were inserted into it.

LinkedHashMap = new LinkedHashMap<String, Integer>();
resources.put("One", R.drawable.ic_image_1);
resources.put("Two", R.drawable.ic_image_2);
...
resources.put("Ten", R.drawable.ic_image_10);

Now, here there is a problem. While you can iterate each of these items and get them based on the key (name), you cannot access them with an index. Do not despair! There is a solution around it by using the entrySet() method which is offer with the LinkedHashMap. The entrySet() method returns a set view of the mappings contained in the map. Lets see how it can help us:

resourcesIndexed = new ArrayList<Map.Entry<String, Integer>>(resources.entrySet());

    @Override
    public void onBindViewHolder(MasonryView holder, int position) {
        holder.textView.setText(resourcesIndexed.get(position).getKey());
        holder.imageView.setImageResource(resourcesIndexed.get(position).getValue());
    }

As you can see, without creating a container, such as a class, we can use an index to obtain the information and keep track of each pair.

 

Share

Android: Multi-threading UI and Database (Room)

Each time I need an application, I encounter that those publish never fit my needs. However, I am lucky! I can make them myself.

Recently, I decided to create an exercise app that fit my purpose. Therefore, I began developing for Android again.

A few months ago, my Microsoft Phone’s battery gave up and a massive deployment to production was coming which required me to be available; therefore, I purchased the first Android I could find. For those who wonder why I had a Microsoft Phone, I was developing apps using Universal Windows Platform using HTML5, JavaScript and the WinJS library. Pretty slick; however, there were bugs and poor designed features which made people drop the Windows Phone. No even Verizon Wireless support it. A shame

Anyways, you folk are not reading this post to listen to my poorly written stories but to find a solution to your problem such as dealing with messages as:

  • Only the original thread that created a view hierarchy can touch its views.
  • Cannot access database on the main thread since it may potentially lock the UI for a long period of time

These error messages are common when working with the UI and trying to do transactions with the database via Room.

In Short

For those who don’t have the time or patience here is the code I use more often from all the other solutions:

final Handler handler = new Handler();
(new Thread(new Runnable() {
    @Override
    public void run() {
        // DO DATABASE TRANSACTION
        handler.post(new Runnable() {
	   @Override
            public void run() {
                // DO UI STUFF HERE.
	    }
        });
    }
})).start();

Other Options

So far, this is the easier and straight forwards solution that have being working for me.

I tried with runOnUiThread:

runOnUiThread(new Runnable() {
    @Override
    public void run() {
		// Run code here
    }
});

Also, I used AsyncTask:

new DatabaseAsync().execute();

private class DatabaseAsync extends AsyncTask<Void, Void, Void>{
    @Override
    protected void onPreExecute(){
    super.onPreExecute();
        // Pre-operation here.
    }

    @Override
    protected Void doInBackground(Void... voids){
        //Perform database operations here.
        return null;
    }

    @Override
    protected void onPostExecute(Void aVoid){
    super.onPostExecute(aVoid);
        // Do UI operations here
    }
}

And used combinations of Thread and runOnUiThread:

new Thread(new Runnable() {
    @Override
    public void run() {
        // Database operation here
        try {
            runOnUiThread(new Runnable() {
                @Override
                public void run() {
                    // UI operation here
                }
            });
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }
}).start();

I even took a look into RxJava which is a “Java VM implementation of Reactive Extensions: a library for composing asynchronous and event-based programs by using observable sequences.”.

Conclusion

There are many ways to tackle this issue as you can see. The trick here is to understand how android handles threading and UI threading; however, such topics are for another post.

Share