SQL Join based on Ven Diagram

Hi Guys, Hope You  all  are doing well !!!
Today i am going to put some light on sql join .
 At first  I am starting  with what is sql join ?
The SQL Joins clause is used to combine records from two or more tables in a database. A JOIN is a
means for combining fields from two tables by using values common to each.
Now why should we use join?
 if you have some experience in database programming you know we can run queries one by one, use output of each in successive queries. Of course, that is possible. But using JOINs, you can get the work done by using only a one query with any search parameters.
   On the other hand MySQL can achieve better performance with JOINs as it can use Indexing. Simply use of single JOIN query instead running multiple queries do reduce server overhead. Using multiple queries instead that leads more data transfers between MySQL and applications (software). Further it requires more data manipulations in application end also.
It is clear that we can achieve better MySQL and application performances by use of JOINs.
I am going to discuss seven different ways you can return data from two relational tables. I will be excluding cross Joins and self referencing Joins.
The seven Joins I will discuss are shown below:

1. INNER JOIN
2. LEFT JOIN
3. RIGHT JOIN
4. OUTER JOIN
5. LEFT JOIN EXCLUDING INNER JOIN
6. RIGHT JOIN EXCLUDING INNER JOIN
7. OUTER JOIN EXCLUDING INNER JOIN

Before I start sql join  query suppose we have  following table created in database . 

table_a and table_b . Data in table_a

pk       value                   and   table_b

1          A1                              pk        value

2          A2                               1            B1

3          A3                               2            B2

6         A6                                3            B3

7         A7                                6            B6

4         A4                                 7           B7

5         A5                                 8          B8

10       A10                               9           B9

1. Inner Join
This is the simplest, most understood Join and is the most common. This query will return all of the records in the left table (table A) that have a matching record in the right table (table B). This Join is written as follows:

INNER JOIN
SELECT A.PK AS A_PK, A.Value AS A_Value, B.Value AS B_Value, B.PK AS B_PK FROM Table_A A INNER JOIN Table_B B ON A.PK = B.PK

Results Output

A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
   1  A1        B1          1
   2  A2        B2          2
   3  A3        B3          3
   6  A6        B4          6 
   7  A7        B7          7

2. Left Join  or  Left outer join 
This query will return all of the records in the left table (table A) regardless if any of those records have a match in the right table (table B). It will also return any matching records from the right table. This Join is written as follows:

LEFT JOIN

SELECT A.PK AS A_PK, A.Value AS A_Value,B.Value AS B_Value, B.PK AS B_PK FROM Table_A A LEFT JOIN Table_B B ON A.PK = B.PK

Result will be

A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
   1 A1         B1           1
   2 A2         B2           2
   3 A3         B3           3
   4 A4       NULL         NULL
   5 A5       NULL         NULL
   6 A6       B6            6
   7 A7       B7            7
  10 A10     NULL         NULL

(8 row(s) affected)

3. Right  Join or Right outer  Join
This query will return all of the records in the right table (table B) regardless if any of those records have a match in the left table (table A). It will also return any matching records from the left table. This Join is written as follows:

RIGHT JOIN
SELECT A.PK AS A_PK, A.Value AS A_Value, B.Value AS B_Value, B.PK AS B_PK FROM Table_A A
RIGHT JOIN Table_B B ON A.PK = B.PK

Result will be

A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
   1  A1        B1           1
   2  A2        B2           2
   3  A3        B3           3
   6  A6        B6           6
   7  A7        B7           7
NULL  NULL      B8           8
NULL  NULL      B9           9
NULL  NULL      B11          11

(8 row(s) affected)

4. Full Join or full outer join 
This Join can also be referred to as a FULL OUTER JOIN or a FULL JOIN. This query will return all of the records from both tables, joining records from the left table (table A) that match records from the right table (table B). This Join is written as follows:

OUTER JOIN
SELECT A.PK AS A_PK, A.Value AS A_Value,B.Value AS B_Value, B.PK AS B_PK FROM Table_A A
FULL OUTER JOIN Table_B B ON A.PK = B.PK

Result Will be

A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
   1 A1        B1           1
   2 A2        B2           2
   3 A3        B3           3
   6 A6        B4           6
   7 A7        B5           7
NULL NULL      B8           8
NULL NULL      B9           9
NULL NULL      B11         11
   5 A5       NULL       NULL
   4          NULL       NULL
  10 A10      NULL       NULL

(11 row(s) affected)

5. Left Excluding JOIN
This query will return all of the records in the left table (table A) that do not match any records in the right table (table B). This Join is written as follows:

LEFT EXCLUDING JOIN
SELECT A.PK AS A_PK, A.Value AS A_Value,B.Value AS B_Value, B.PK AS B_PK FROM Table_A A
LEFT JOIN Table_B B ON A.PK = B.PK WHERE B.PK IS NULL

Result will be

A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
   4  A4        NULL       NULL
   5  A5        NULL       NULL
  10  A10       NULL       NULL
(3 row(s) affected)

6. Right Excluding JOIN
This query will return all of the records in the right table (table B) that do not match any records in the left table (table A). This Join is written as follows:

RIGHT EXCLUDING JOIN
SELECT A.PK AS A_PK, A.Value AS A_Value,B.Value AS B_Value, B.PK AS B_PK FROM Table_A A
RIGHT JOIN Table_B B ON A.PK = B.PK WHERE A.PK IS NULL

Result will be

A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
NULL  NULL       B8         8
NULL  NULL       B9         9
NULL  NULL       B11       11

(3 row(s) affected)

7.Outer Excluding JOIN
This query will return all of the records in the left table (table A) and all of the records in the right table (table B) that do not match. I have yet to have a need for using this type of Join, but all of the others, I use quite frequently. This Join is written as follows:

OUTER EXCLUDING JOIN
SELECT A.PK AS A_PK, A.Value AS A_Value,B.Value AS B_Value, B.PK AS B_PK FROM Table_A A
FULL OUTER JOIN Table_B B ON A.PK = B.PK WHERE A.PK IS NULL OR B.PK IS NULL

Result will be

A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
NULL  NULL       B8         8
NULL  NULL       B9         9
NULL  NULL       B11        11
   5  A5         NULL       NULL
   4  A4         NULL       NULL
  10  A10        NULL       NULL

(6 row(s) affected)

Summary:

• JOINS allow us to combine data from more than one table into a single result set.
• JOINS have better performance compared to sub queries
• INNER JOINS only return rows that meet the given criteria.
• OUTER JOINS can also return rows where no matches have been found. The unmatched rows are returned with the NULL keyword.
• The major JOIN types include Inner, Left Outer, Right Outer, Cross JOINS etc.
• The frequently used clause in JOIN operations is “ON”. “USING” clause requires that matching columns be of the same name.
• JOINS can also be used in other clauses such as GROUP BY, WHERE, SUB QUERIES, AGGREGATE FUNCTIONS etc.

               Happy Coding !!!

Draw Graph in Android

Graph play a very important role in showing data in  Graphical view. User generally find comparison  in a graph  very interesting. It gives a clear view of something going up or down or how things related to each other or what is previous record  and is next coming forecast for that  etc.
  But in android there is no graph  related API . We can draw graph in android by using general  android  API.  The other way is to use third party graph library to draw the graph.  So  finally we have to two option
 (1.) Draw graph by using general android API  and
 (2.) Draw the graph by using Third party graph library
       (As  for Example AChartEngine, AndroidPlot, Google Chart , AFreeChart etc)

Here I am going to show a example of Pie chart  in both scenario  :
Firstly I am using case A ( by using general android API to draw a Graph)
These are the  4 files and class we need to draw the graph :-

1. pie_chart_activity.xml ( pie chart view purpose)
2. PieChartActivity.java ( main class to draw a pie chart )
3. Piechart.java ( pie chart properties class)
4. PieItem.java ( attributes class )

Step 1.
Create a Project in Your Eclipse IDE. Then copy and paste following file and java class in your application .
pie_chart_activity.xml

<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="match_parent"
android:layout_height="match_parent"
tools:context=".PieChartActivity" >

<LinearLayout
    android:id="@+id/pie_container"
    android:layout_width="fill_parent"
    android:layout_height="wrap_content"
    android:layout_centerHorizontal="true"
    android:layout_centerVertical="true"
    android:orientation="vertical" >
</LinearLayout>

<RelativeLayout
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:layout_alignRight="@+id/pie_container"
    android:layout_centerHorizontal="true"
    android:layout_centerVertical="true" >

    <TextView
        android:id="@+id/parta"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:background="#444544"
        android:paddingLeft="5dp"
        android:paddingRight="5dp"
        android:text="PartA"
        android:textStyle="bold" />

    <TextView
        android:id="@+id/partb"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_below="@+id/parta"
        android:background="#878887"
        android:paddingLeft="5dp"
        android:paddingRight="5dp"
        android:text="PartB"
        android:textStyle="bold" />

    <TextView
        android:id="@+id/partc"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_below="@+id/partb"
        android:background="#98967F"
        android:paddingLeft="5dp"
        android:paddingRight="5dp"
        android:text="PartC"
        android:textStyle="bold" />

    <TextView
        android:id="@+id/partd"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_below="@+id/partc"
        android:background="#A1A1A1"
        android:paddingLeft="5dp"
        android:paddingRight="5dp"
        android:text="PartD"
        android:textStyle="bold" />

    <TextView
        android:id="@+id/parte"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_below="@+id/partd"
        android:background="#0000FF"
        android:paddingLeft="5dp"
        android:paddingRight="5dp"
        android:text="PartE"
        android:textStyle="bold" />
</RelativeLayout>

PieChartActivity.java

package com.sks.mks.pieexample;
public class PieChartActivity extends Activity {

List<PieItem> piedata = new ArrayList<PieItem>(0);

 @Override
 public void onCreate(Bundle savedInstanceState) {
  super.onCreate(savedInstanceState);
  setContentView(R.layout.activity_pie_chart);

  PieItem item;
  int maxCount = 0;
  int itemCount = 0;
  int items[] = { 20, 40, 10, 15, 5 };
  int colors[] = { -6777216, -16776961, -16711681, -12303292, -7829368 };
  String itemslabel[] = { "part A 100", "part B 200",
    "Part C 300", "Part D  400", "Part E 500" };
  for (int i = 0; i < items.length; i++) {
   itemCount = items[i];
   item = new PieItem();
   item.count = itemCount;
   item.label = itemslabel[i];
   item.color = colors[i];
   piedata.add(item);
   maxCount = maxCount + itemCount;
  }

  int size = 155;
  int BgColor = 0xffa11b1;
  Bitmap mBaggroundImage = Bitmap.createBitmap(size, size,Bitmap.Config.ARGB_8888);

  Piechart piechart = new Piechart(this);
  Piechart.setLayoutParams(new LayoutParams(size, size));
  piechart.setGeometry(size, size, 2, 2, 2, 2, 2130837504);
  piechart.setSkinparams(BgColor);
  piechart.setData(piedata, maxCount);
  piechart.invalidate();
  piechart.draw(new Canvas(mBaggroundImage));
  piechart = null;
  ImageView mImageView = new ImageView(this);
  mImageView.setLayoutParams(new LayoutParams(LayoutParams.WRAP_CONTENT,LayoutParams.WRAP_CONTENT));
  mImageView.setBackgroundColor(BgColor);
  mImageView.setImageBitmap(mBaggroundImage);
  LinearLayout finalLayout = (LinearLayout) findViewById(R.id.pie_container);
  finalLayout.addView(mImageView);
 }
}

Piechart.java

package com.sks.mks.pieexample;

public class Piechart {

public static final int WAIT = 0;
 public static final int IS_READY_TO_DRAW = 1;
 public static final int IS_DRAW = 2;
 private static final float START_INC = 30;
 private Paint mBagpaints = new Paint();
 private Paint mLinePaints = new Paint();

 private int mWidth;
 private int mHeight;
 private int mGapTop;
 private int mGapBottm;
 private int mBgcolor;
 private int mGapleft;
 private int mGapright;
 private int mState = WAIT;
 private float mStart;
 private float mSweep;
 private int mMaxConnection;
 private List<PieItem> mdataArray;

 public Piechart(Context context) {
  Log.w(" single cons ", " single cons");
 }

 public Piechart(Context context, AttributeSet attr) {
  Log.w(" double cons ", " double cons");
 }


 protected void onDraw(Canvas canvas) {
  if (mState != IS_READY_TO_DRAW) {
   return;
  }
  canvas.drawColor(mBgcolor);
  mBagpaints.setAntiAlias(true);
  mBagpaints.setStyle(Paint.Style.FILL);
  mBagpaints.setColor(0x88FF0000);
  mBagpaints.setStrokeWidth(0.0f);
  mLinePaints.setAntiAlias(true);
  mLinePaints.setColor(0xff000000);
  mLinePaints.setStrokeWidth(3.0f);
  mLinePaints.setStyle(Paint.Style.STROKE);
  RectF mOvals = new RectF(mGapleft, mGapTop, mWidth - mGapright, mHeight
    - mGapBottm);
  mStart = START_INC;
  PieItem item;
  for (int i = 0; i < mdataArray.size(); i++) {
   item = (PieItem) mdataArray.get(i);
   mBagpaints.setColor(item.color);
   mSweep = (float) 360* ((float) item.count / (float) mMaxConnection);
   canvas.drawArc(mOvals, mStart, mSweep, true, mBagpaints);
   canvas.drawArc(mOvals, mStart, mSweep, true, mLinePaints);
   mStart = mStart + mSweep;
  }

  mState = IS_DRAW;
 }

 public void setGeometry(int width, int height, int gapleft, int gapright,
   int gaptop, int gapbottom, int overlayid) {

  mWidth = width;
  mHeight = height;
  mGapleft = gapleft;
  mGapright = gapright;
  mGapBottm = gapbottom;
  mGapTop = gaptop;

 }

 public void setSkinparams(int bgcolor) {
  Log.w(" Set bg color  : ", bgcolor + "");
  mBgcolor = bgcolor;
 }

 public void setData(List<PieItem> data, int maxconnection) {
  mdataArray = data;
  mMaxConnection = maxconnection;
  Log.w(" Max Connection  ", maxconnection + " " + "  Adataarray :"
    + data.toString());
  mState = IS_READY_TO_DRAW;
 }

 public void setState(int state) {
  mState = state;
 }

 public int getColorValues(int index) {
  if (mdataArray == null) {
   return 0;
  }

  else if (index < 0)
   return ((PieItem) mdataArray.get(0)).color;
  else if (index > mdataArray.size())
   return ((PieItem) mdataArray.get(mdataArray.size() - 1)).color;
  else
   return ((PieItem) mdataArray.get(mdataArray.size() - 1)).color;

 }

}

PieItem.java

package com.sks.mks.pieexample;
public class PieItem {
 public int count, color;
 public float percent;
 public String label;
}

Now I am going to draw graph by using Third party library. Here i am using AchartEngin to draw graph :-
At first download AchartEngin and put it on libs folder of Your Eclipse project. You can download it from Here

Now  Create two java class in src and one  xml file in res folder.
1. DrawChart.java
2.GraphActivity.java
3. activity_graph.xml
copy and paste following code in your DrawChart.java

package com.sks.mks.piechart;

import org.achartengine.ChartFactory;

import org.achartengine.GraphicalView;

import org.achartengine.model.CategorySeries;

import org.achartengine.renderer.DefaultRenderer;

import org.achartengine.renderer.SimpleSeriesRenderer;

import android.content.Context;

import android.content.Intent;

import android.graphics.Color;

import android.widget.RelativeLayout;

public class DrawChart {

private GraphicalView ChartView1;

private GraphicalView mChartView2;

static int count=5;

int[] Mycolors = new int[] { Color.RED, Color.parseColor("#FA58F4"),

Color.parseColor("#0B0B61"),

Color.parseColor("#800080"),Color.parseColor("#008000"),Color.GRAY };

public Intent execute(Context context,RelativeLayout parent) {

int[] colors = new int[count];

for(int i=0;i<count;i++)

{

colors[i]=Mycolors[i];

}

DefaultRenderer renderer = buildCategoryRenderer(colors);

renderer.setPanEnabled(false);// Disable User Interaction

renderer.setLabelsColor(Color.BLACK);

renderer.setShowLabels(true);

//renderer.setChartTitle("Total Assets");

renderer.setLabelsTextSize(12);

CategorySeries categorySeries = new CategorySeries("Fruits");

categorySeries.add("Apple", 36);

categorySeries.add("Banana", 23);

categorySeries.add("Grapes", 30);

categorySeries.add("Guava", 8);

categorySeries.add("Orange", 3);

ChartView1=ChartFactory.getPieChartView(context, categorySeries,renderer);

parent.addView(ChartView1);

return ChartFactory.getPieChartIntent(context, categorySeries, renderer,null);

}

protected DefaultRenderer buildCategoryRenderer(int[] colors)

{

DefaultRenderer renderer = new DefaultRenderer();

for (int color : colors) {

SimpleSeriesRenderer r = new SimpleSeriesRenderer();

r.setColor(color);

renderer.addSeriesRenderer(r);

}

return renderer;

} 

} 

Now  put following code in GraphActivity.java

package com.sks.mks.piechart;

import android.os.Bundle;

import android.app.Activity;

import android.content.Intent;

import android.view.Menu;

import android.widget.RelativeLayout; 

public class GraphActivity extends Activity {

       RelativeLayout LayoutToDisplayChart;

       @Override

       protected void onCreate(Bundle savedInstanceState) {

              super.onCreate(savedInstanceState);

              setContentView(R.layout.activity_graph);

              LayoutToDisplayChart=(RelativeLayout)findViewById(R.id.relative);

              Intent achartIntent = new DrawChart().execute(GraphActivity.this, LayoutToDisplayChart);

                          

       }

}

lastly put following code in xml file:-

<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"

    xmlns:tools="http://schemas.android.com/tools"

    android:layout_width="match_parent"

    android:layout_height="match_parent"

    tools:context=".GraphActivity" >

<RelativeLayout

android:id="@+id/relative"

android:layout_width="wrap_content"

android:layout_height="wrap_content" >

</RelativeLayout>

</RelativeLayout> 

Happy Coding !!!