http://www.hosinc.com/products/interfaces/interface_documentation.htm
http://www.7edit.com/manual/tutorials/hl7-format-intro/
http://www.otechimg.com/pdf/hl7messaging_ch1.pdf
http://www.doh.state.fl.us/irm/apps/ihis/JADS/elr/JAD%20Documentation/02-18-00/hl7format.pdf
Threading
A multithreaded program contains two or more parts that can run concurrently. Each part of such a program is called a thread, and each thread defines a separate path of execution. Thus, multithreading is a specialized form of multitasking.
Multithreaded applications provide the illusion that numerous activities are happening at more or less the same time. But the reality is the CPU uses something known as “Time Slice” to switch between different threads.
The principal advantage of multithreading is that it enables you to write very efficient programs because it lets you utilize the idle time that is present in most programs. But using too many threads in our programs can actually “degrade” performance, as the CPU must switch between the active threads in the process which takes time. When a thread’s time slice is up, the existing thread is suspended to allow other thread to perform its business. For a thread to remember what was happening before it was kicked out of the way, it writes information to its local storage and it is also provided with a separate call stack, which again put extra load on the CPU.
The differences between process-based and thread-based multitasking can be summarized like this:
Process-based multitasking handles the concurrent execution of programs, while Thread-based multitasking deals with the concurrent execution of pieces of the same program.
Process-based: Example — running word processor at the same time you are browsing the net.
Thread-based: Example — A text editor can be formatting text at the same time that it is printing.
The .NET Framework defines two types of threads: foreground and background.
By default when you create a thread, it is a foreground thread, but you can change it to a background thread. The only difference between a foreground and background thread is that a background thread will be automatically terminated when all foreground threads in its process have stopped.
The “Foreground” threads have the ability to prevent the current application from terminating. The CLR will not shutdown an application until all foreground threads have ended. The “Background” threads are viewed by the CLR as expandable paths of execution that can be ignored at any point of time even if they are laboring over some unit of work. Thus, if all foreground threads have terminated, any background threads operating are automatically killed when the application terminates.
All processes have at least one thread of execution, which is usually called the main thread because it is the one that is executed when your program begins. From the main thread you can create other threads.
The classes that support multithreaded programming are defined in the
Thus, you will usually include this statement at the start of any multithreaded program:
Collapse

Now we can see, as the thread scheduler is swapping threads in the background each thread is telling the Printer to print the numerical data. We are getting inconsistent output as the access of these threads to the Printer object is synchronized. There are various synchronization options which we can use in our programs to enable synchronization of the shared resource among multiple threads.
Syntax:
Collapselock (objecttobelocked) { objecttobelocked.somemethod(); } Here
If we want to lock down the code in a static method, we need to provide the
Collapsepublic void PrintNumbers() { lock (this) { for (int i = 0; i < 5; i++) { Thread.Sleep(100); Console.Write(i + ","); } Console.WriteLine(); } }


In this article I have tried to make the explanation as simple as possible. I have referred the book written by Andrew Troelson published by Apress. Please provide your valuable comments and suggestions for improvement of this article. I am always open for future upgradation of the article.
Note that a synchronized object holds several references, including a reference to the thread that currently holds the lock, a reference to the ready queue, which contains the threads that are ready to obtain the lock, and a reference to the waiting queue, which contains the threads that are waiting for notification of a change in the object's state. The Pulse, PulseAll, and Wait methods must be invoked from within a synchronized block of code. The remarks for Wait(Object, Int32) address an issue that arises when Pulse is invoked before Wait.
To signal multiple threads, use the PulseAll method.
Multithreaded applications provide the illusion that numerous activities are happening at more or less the same time. But the reality is the CPU uses something known as “Time Slice” to switch between different threads.
The principal advantage of multithreading is that it enables you to write very efficient programs because it lets you utilize the idle time that is present in most programs. But using too many threads in our programs can actually “degrade” performance, as the CPU must switch between the active threads in the process which takes time. When a thread’s time slice is up, the existing thread is suspended to allow other thread to perform its business. For a thread to remember what was happening before it was kicked out of the way, it writes information to its local storage and it is also provided with a separate call stack, which again put extra load on the CPU.
Multithreading Fundamentals
There are two distinct types of multitasking: process-based and thread-based.The differences between process-based and thread-based multitasking can be summarized like this:
Process-based multitasking handles the concurrent execution of programs, while Thread-based multitasking deals with the concurrent execution of pieces of the same program.
Process-based: Example — running word processor at the same time you are browsing the net.
Thread-based: Example — A text editor can be formatting text at the same time that it is printing.
The .NET Framework defines two types of threads: foreground and background.
By default when you create a thread, it is a foreground thread, but you can change it to a background thread. The only difference between a foreground and background thread is that a background thread will be automatically terminated when all foreground threads in its process have stopped.
The “Foreground” threads have the ability to prevent the current application from terminating. The CLR will not shutdown an application until all foreground threads have ended. The “Background” threads are viewed by the CLR as expandable paths of execution that can be ignored at any point of time even if they are laboring over some unit of work. Thus, if all foreground threads have terminated, any background threads operating are automatically killed when the application terminates.
All processes have at least one thread of execution, which is usually called the main thread because it is the one that is executed when your program begins. From the main thread you can create other threads.
The classes that support multithreaded programming are defined in the
System.Threading namespace. Thus, you will usually include this statement at the start of any multithreaded program:
Methods
Suspend()-> Suspends the execution of a thread till Resume() is called on that.Resume()-> Resumes a suspended thread. Can throw exceptions for bad state of the thread.Sleep()-> A thread can suspend itself by calling Sleep(). Takes parameter in form of milliseconds. We can use a special timeout 0 to terminate the current time slice and give other thread a chance to use CPU timeJoin()-> Called on a thread makes other threads wait for it till it finishes its task.
States of a Thread
States of a thread can be checked usingThreadState enumerated property of the Thread object which contains a different value for different states.- Aborted -> Aborted already.
- AbortRequested -> Responding to an
Abort()request. - Background -> Running in background. Same as
IsBackgroundproperty. - Running -> Running after another thread has called the
start() - Stopped -> After finishing
run()orAbort()stopped it. - Suspended -> Suspended after
Suspend()is called. - Unstarted -> Created but
start()has not been called. - WaitSleepJoin ->
Sleep()/Wait()on itself andjoin()on another thread. If a thread Thread1 callssleep()on itself and callsjoin()on the thread Thread2 then it enters WaitSleepJoin state. The thread exists in this state till the timeout expires or another thread invokesInterrupt()on it.
Properties of a Thread
Thread.CurrentThread-> Static method gives the reference of the thread object which is executing the current code.- Name -> Read/Write Property used to get and set the name of a thread
- ThreadState -> Property used to check the state of a thread.
- Priority -> Property used to check for the priority level of a thread.
- IsAlive -> Returns a Boolean value stating whether the thread is alive or not.
- IsBackground -> Returns a Boolean value stating the running in background or foreground.
PriorityLevels of Thread
Priority levels of thread is set or checked by using an enumeration i.e.ThreadPriority. The valid values are for this enumeration are; - Highest
- AboveNormal
- Normal
- BelowNormal
- Lowest
Synchronization in Threads
Monitor
Mutex
Thread Pool
When we have multiple threads that share data, we need to provide synchronized access to the data. We have to deal with synchronization issues related to concurrent access to variables and objects accessible by multiple threads at the same time. This is controlled by giving one thread a chance to acquire a lock on the shared resource at a time. We can think it like a box where the object is available and only one thread can enter into and the other thread is waiting outside the box until the previous one comes out.using System;
using System.Threading;
namespace CSharpThreadExample
{
class Program
{
static void Main(string[] arg)
{
Console.WriteLine("*****Multiple Threads*****");
Printer p=new Printer();
Thread[] Threads=new Thread[3];
for(int i=0;i<3;i++)
{
Threads[i]=new Thread(new ThreadStart(p.PrintNumbers));
Threads[i].Name="Child "+i;
}
foreach(Thread t in Threads)
t.Start();
Console.ReadLine();
}
}
class Printer
{
public void PrintNumbers()
{
for (int i = 0; i < 5; i++)
{
Thread.Sleep(100);
Console.Write(i + ",");
}
Console.WriteLine();
}
}
} In the above example, we have created three threads in the main method and all the threads are trying to use the PrintNumbers() method of the same Printer object to print to the console. Here we get this type of output:Now we can see, as the thread scheduler is swapping threads in the background each thread is telling the Printer to print the numerical data. We are getting inconsistent output as the access of these threads to the Printer object is synchronized. There are various synchronization options which we can use in our programs to enable synchronization of the shared resource among multiple threads.
Using the Lock Keyword
In C# we uselock(object) to synchronize the shared object.Syntax:
objecttobelocked is the object reference which is used by more than one thread to call the method on that object. The lock keyword requires us to specify a token (an object reference) that must be acquired by a thread to enter within the lock scope. When we are attempting to lock down an instance level method, we can simply pass the reference to that instance. (We can use this keyword to lock the current object) Once the thread enters into a lock scope, the lock token (object reference) is inaccessible by other threads until the lock is released or the lock scope has exited.If we want to lock down the code in a static method, we need to provide the
System.Type of the respective class.Converting the Code to Enable Synchronization using the Lock Keyword
OUTPUT
Using the Monitor Type
The C# lock keyword is just a notation for usingSystem.Threading.Monitor class type. The lock scope actually resolves to the Monitor class after being processed by the C# compiler.Converting the Code to Enable Synchronization using the Monitor Class
public void PrintNumbers() { Monitor.Enter(this); try { for (int i = 0; i < 5; i++) { Thread.Sleep(100); Console.Write(i + ","); } Console.WriteLine(); } finally { Monitor.Exit(this); } }Monitor.Enter() method is the ultimate recipient of the thread token. We need to write all code of the lock scope inside a try block. The finally clause ensures that the thread token is released(using the Monitor.Exit() method), regardless of any runtime exception.OUTPUT
In this article I have tried to make the explanation as simple as possible. I have referred the book written by Andrew Troelson published by Apress. Please provide your valuable comments and suggestions for improvement of this article. I am always open for future upgradation of the article.
synchronization can be accomplished using the C# lock keyword and the Pulse method of the Monitor object. The Pulse method notifies a thread in the waitingqueue of a change in the object's state (for more details on pulses, see the Monitor.Pulse Method). Only the current owner of the lock can signal a waiting object using Pulse.The thread that currently owns the lock on the specified object invokes this method to signal the next thread in line for the lock. Upon receiving the pulse, the waiting thread is moved to the ready queue. When the thread that invoked Pulse releases the lock, the next thread in the ready queue (which is not necessarily the thread that was pulsed) acquires the lock.Note that a synchronized object holds several references, including a reference to the thread that currently holds the lock, a reference to the ready queue, which contains the threads that are ready to obtain the lock, and a reference to the waiting queue, which contains the threads that are waiting for notification of a change in the object's state. The Pulse, PulseAll, and Wait methods must be invoked from within a synchronized block of code. The remarks for Wait(Object, Int32) address an issue that arises when Pulse is invoked before Wait.
To signal multiple threads, use the PulseAll method.
The example creates a Cell object that has two methods: ReadFromCell and WriteToCell.Two other objects are created from classes CellProd and CellCons; these objects both have amethod ThreadRun whose job is to call ReadFromCell and WriteToCell. Synchronization is accomplished by waiting for "pulses" from the Monitor
object, which come in order. That is, first an item is produced (the
consumer at this point is waiting for a pulse), then a pulse occurs,
then the consumer consumes the production (while the producer is waiting
for a pulse), and so on. Join
How can we find out…
Note that, to join with a table, that table must be in the FROM clause or must already be joined with the table in the FROM clause. Consider the following.
The above code would break because it attempts to join table3 with table2 before table2 has been joined with table1.
- Which products are provided by which suppliers?
- Which customers placed which orders?
- Which customers are buying which products?
Syntax
SELECT table1.column, table2.column FROM table1 JOIN table2 ON (table1.column=table2.column) WHERE conditions
SELECT Employees.EmployeeID, Employees.FirstName, Employees.LastName, Orders.OrderID, Orders.OrderDate FROM Employees JOIN Orders ON (Employees.EmployeeID = Orders.EmployeeID) ORDER BY Orders.OrderDate;
Table Aliases
Using full table names as prefixes can make SQL queries unnecessarily wordy. Table aliases can make the code a little more concise. The example below, which is identical in functionality to the query above, illustrates the use of table aliases.SELECT e.EmployeeID, e.FirstName, e.LastName, o.OrderID, o.OrderDate FROM Employees e JOIN Orders o ON (e.EmployeeID = o.EmployeeID) ORDER BY o.OrderDate;
Multi-table Joins
Multi-table joins can get very complex and may also take a long time to process, but the syntax is relatively straightforward.Syntax
SELECT table1.column, table2.column, table3.column FROM table1 JOIN table2 ON (table1.column=table2.column) JOIN table3 ON (table2.column=table3.column) WHERE conditions
SELECT table1.column, table2.column, table3.column FROM table1 JOIN table3 ON (table2.column=table3.column) JOIN table2 ON (table1.column=table2.column) WHERE conditions
/* Create a report showing the Order ID, the name of the company that placed the order, and the first and last name of the associated employee. Only show orders placed after January 1, 1998 that shipped after they were required. Sort by Company Name. */ /****************************** Both of the queries below will work in SQL Server MySQL ******************************/ SELECT o.OrderID, c.CompanyName, e.FirstName, e.LastName FROM Orders o JOIN Employees e ON (e.EmployeeID = o.EmployeeID) JOIN Customers c ON (c.CustomerID = o.CustomerID) WHERE o.ShippedDate > o.RequiredDate AND o.OrderDate > '1998-01-01' ORDER BY c.CompanyName;
Example
---------------
USE pubs
SELECT a.au_lname, a.au_fname, t.title
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id JOIN titles t
ON ta.title_id = t.title_id
WHERE t.type = 'trad_cook'
ORDER BY t.title ASC Sql fundamental
Operators Allowed in the WHERE Clause
| Operator | Description |
|---|---|
| = | Equal |
| <> | Not equal |
| > | Greater than |
| < | Less than |
| >= | Greater than or equal |
| <= | Less than or equal |
| BETWEEN | Between an inclusive range |
| LIKE | Search for a pattern |
| IN | If you know the exact value you want to return for at least one of the columns |
SQL AND & OR Operators
The AND & OR operators are used to filter records based on more than one condition.The AND & OR Operators
The AND operator displays a record if both the first condition and the second condition is true.The OR operator displays a record if either the first condition or the second condition is true.
SELECT * FROM Persons WHERE FirstName='Tove' AND LastName='Svendson'
SELECT * FROM Persons WHERE FirstName='Tove' OR FirstName='Ola' Combining AND & OR
SELECT * FROM Persons WHERE LastName='Svendson' AND (FirstName='Tove' OR FirstName='Ola')
The ORDER BY Keyword
The ORDER BY keyword is used to sort the result-set by a specified column.The ORDER BY keyword sort the records in ascending order by default.If you want to sort the records in a descending order, you can use the DESC keyword.
SELECT column_name(s) FROM table_name ORDER BY column_name(s) ASC|DESC
SELECT * FROM Persons ORDER BY LastName
SELECT * FROM Persons ORDER BY LastName DESC
The INSERT INTO Statement
Insert Data Only in Specified Columns
INSERT INTO Persons (P_Id, LastName, FirstName) VALUES (5, 'Tjessem', 'Jakob')
Insert data into all columns
INSERT INTO Persons VALUES (4,'Nilsen', 'Johan', 'Bakken 2', 'Stavanger')
SQL UPDATE Statement
UPDATE Persons SET Address='Nissestien 67', City='Sandnes' WHERE LastName='Tjessem' AND FirstName='Jakob'
SQL DELETE Statement
DELETE FROM Persons WHERE LastName='Tjessem' AND FirstName='Jakob'
Delete All Rows
It is possible to delete all rows in a table without deleting the table. This means that the table structure, attributes, and indexes will be intact:DELETE FROM table_name
or
DELETE * FROM table_name
Note: Be very careful when deleting records. You cannot undo this statement!
SQL TOP Clause
The TOP clause is used to specify the number of records to return.The TOP clause can be very useful on large tables with thousands of records. Returning a large number of records can impact on performance.
Note: Not all database systems support the TOP clause.
SELECT TOP 2 * FROM Persons
SQL TOP PERCENT Example
Now we want to select only 50% of the records in the table above.
We use the following SELECT statement:SELECT TOP 50 PERCENT * FROM Persons
SQL LIKE Operator
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
Now we want to select the persons living in a city that starts with "s" from the table above.We use the following SELECT statement:
SELECT * FROM Persons WHERE City LIKE 's%'
The "%" sign can be used to define wildcards (missing letters in the pattern) both before and after the pattern.
Next, we want to select the persons living in a city that ends with an "s" from the "Persons" table.We use the following SELECT statement:
SELECT * FROM Persons WHERE City LIKE '%s'
Next, we want to select the persons living in a city that contains the pattern "tav" from the "Persons" table.We use the following SELECT statement:
SELECT * FROM Persons WHERE City LIKE '%tav%'
It is also possible to select the persons living in a city that NOT contains the pattern "tav" from the "Persons" table, by using the NOT keyword.We use the following SELECT statement:
SELECT * FROM Persons WHERE City NOT LIKE '%tav%'
SQL Wildcards
SQL wildcards can substitute for one or more characters when searching for data in a database.SQL wildcards must be used with the SQL LIKE operator.
With SQL, the following wildcards can be used:
| Wildcard | Description |
|---|---|
| % | A substitute for zero or more characters |
| _ | A substitute for exactly one character |
| [charlist] | Any single character in charlist |
| [^charlist]or [!charlist] | Any single character not in charlist |
Using the % Wildcard
Now we want to select the persons living in a city that starts with "sa" from the "Persons" table.We use the following SELECT statement:
| SELECT * FROM Persons WHERE City LIKE 'sa%' |
| P_Id | LastName | FirstName | Address | City |
|---|---|---|---|---|
| 1 | Hansen | Ola | Timoteivn 10 | Sandnes |
| 2 | Svendson | Tove | Borgvn 23 | Sandnes |
We use the following SELECT statement:
| SELECT * FROM Persons WHERE City LIKE '%nes%' |
Using the _ Wildcard
Now we want to select the persons with a first name that starts with any character, followed by "la" from the "Persons" table.
We use the following SELECT statement:
| SELECT * FROM Persons WHERE FirstName LIKE '_la' |
Next, we want to select the persons with a last name that starts with "S", followed by any character, followed by "end", followed by any character, followed by "on" from the "Persons" table.
We use the following SELECT statement:
| SELECT * FROM Persons WHERE LastName LIKE 'S_end_on' |
Using the [charlist] Wildcard
Now we want to select the persons with a last name that starts with "b" or "s" or "p" from the "Persons" table.We use the following SELECT statement:
| SELECT * FROM Persons WHERE LastName LIKE '[bsp]%' |
Next, we want to select the persons with a last name that do not start with "b" or "s" or "p" from the "Persons" table.
We use the following SELECT statement:
| SELECT * FROM Persons WHERE LastName LIKE '[!bsp]%' |
SQL IN Operator
The IN operator allows you to specify multiple values in a WHERE clause.
IN Operator Example
The "Persons" table:| P_Id | LastName | FirstName | Address | City |
|---|---|---|---|---|
| 1 | Hansen | Ola | Timoteivn 10 | Sandnes |
| 2 | Svendson | Tove | Borgvn 23 | Sandnes |
| 3 | Pettersen | Kari | Storgt 20 | Stavanger |
We use the following SELECT statement:
| SELECT * FROM Persons WHERE LastName IN ('Hansen','Pettersen') |
The result-set will look like this:
| P_Id | LastName | FirstName | Address | City |
|---|---|---|---|---|
| 1 | Hansen | Ola | Timoteivn 10 | Sandnes |
| 3 | Pettersen | Kari | Storgt 20 | Stavanger |
SQL BETWEEN Operator
The BETWEEN operator selects a range of data between two values. The values can be numbers, text, or dates.| P_Id | LastName | FirstName | Address | City |
|---|---|---|---|---|
| 1 | Hansen | Ola | Timoteivn 10 | Sandnes |
| 2 | Svendson | Tove | Borgvn 23 | Sandnes |
| 3 | Pettersen | Kari | Storgt 20 | Stavanger |
We use the following SELECT statement:
| SELECT * FROM Persons WHERE LastName BETWEEN 'Hansen' AND 'Pettersen' |
The result-set will look like this:
| P_Id | LastName | FirstName | Address | City |
|---|---|---|---|---|
| 1 | Hansen | Ola | Timoteivn 10 | Sandnes |
Note: The BETWEEN operator is treated differently in different databases!
In some databases, persons with the LastName of "Hansen" or "Pettersen" will not be listed, because the BETWEEN operator only selects fields that are between and excluding the test values.
In other databases, persons with the LastName of "Hansen" or "Pettersen" will be listed, because the BETWEEN operator selects fields that are between and including the test values.
And in other databases, persons with the LastName of "Hansen" will be listed, but "Pettersen" will not be listed (like the example above), because the BETWEEN operator selects fields between the test values, including the first test value and excluding the last test value.
Therefore: Check how your database treats the BETWEEN operator.
Example 2
To display the persons outside the range in the previous example, use NOT BETWEEN:| SELECT * FROM Persons WHERE LastName NOT BETWEEN 'Hansen' AND 'Pettersen' |
The result-set will look like this:
| P_Id | LastName | FirstName | Address | City |
|---|---|---|---|---|
| 2 | Svendson | Tove | Borgvn 23 | Sandnes |
| 3 | Pettersen | Kari | Storgt 20 | Stavanger |
SQL Alias
You can give a table or a column another name by using an alias. This can be a good thing to do if you have very long or complex table names or column names.An alias name could be anything, but usually it is short.
SQL Alias Syntax for Tables
| SELECT column_name(s) FROM table_name AS alias_name |
SQL Alias Syntax for Columns
| SELECT column_name AS alias_name FROM table_name |
Alias Example
Assume we have a table called "Persons" and another table called "Product_Orders". We will give the table aliases of "p" and "po" respectively.Now we want to list all the orders that "Ola Hansen" is responsible for.
We use the following SELECT statement:
| SELECT po.OrderID, p.LastName, p.FirstName FROM Persons AS p, Product_Orders AS po WHERE p.LastName='Hansen' AND p.FirstName='Ola' |
| SELECT Product_Orders.OrderID, Persons.LastName, Persons.FirstName FROM Persons, Product_Orders WHERE Persons.LastName='Hansen' AND Persons.FirstName='Ola' |
As you'll see from the two SELECT statements above; aliases can make queries easier to both write and to read.
SQL Joins
The JOIN keyword is used in an SQL statement to query data from two or more tables, based on a relationship between certain columns in these tables.Tables in a database are often related to each other with keys.
A primary key is a column (or a combination of columns) with a unique value for each row. Each primary key value must be unique within the table. The purpose is to bind data together, across tables, without repeating all of the data in every table.
Look at the "Persons" table:
| P_Id | LastName | FirstName | Address | City |
|---|---|---|---|---|
| 1 | Hansen | Ola | Timoteivn 10 | Sandnes |
| 2 | Svendson | Tove | Borgvn 23 | Sandnes |
| 3 | Pettersen | Kari | Storgt 20 | Stavanger |
Note that the "P_Id" column is the primary key in the "Persons" table. This means that no two rows can have the same P_Id. The P_Id distinguishes two persons even if they have the same name.
Next, we have the "Orders" table:
| O_Id | OrderNo | P_Id |
|---|---|---|
| 1 | 77895 | 3 |
| 2 | 44678 | 3 |
| 3 | 22456 | 1 |
| 4 | 24562 | 1 |
| 5 | 34764 | 15 |
Note that the "O_Id" column is the primary key in the "Orders" table and that the "P_Id" column refers to the persons in the "Persons" table without using their names.
Notice that the relationship between the two tables above is the "P_Id" column.
Different SQL JOINs
Before we continue with examples, we will list the types of JOIN you can use, and the differences between them.- JOIN: Return rows when there is at least one match in both tables
- LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table
- RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table
- FULL JOIN: Return rows when there is a match in one of the tables
SQL INNER JOIN Keyword
The INNER JOIN keyword return rows when there is at least one match in both tables.SQL INNER JOIN Syntax
| SELECT column_name(s) FROM table_name1 INNER JOIN table_name2 ON table_name1.column_name=table_name2.column_name |
SQL INNER JOIN Example
The "Persons" table:| P_Id | LastName | FirstName | Address | City |
|---|---|---|---|---|
| 1 | Hansen | Ola | Timoteivn 10 | Sandnes |
| 2 | Svendson | Tove | Borgvn 23 | Sandnes |
| 3 | Pettersen | Kari | Storgt 20 | Stavanger |
| O_Id | OrderNo | P_Id |
|---|---|---|
| 1 | 77895 | 3 |
| 2 | 44678 | 3 |
| 3 | 22456 | 1 |
| 4 | 24562 | 1 |
| 5 | 34764 | 15 |
We use the following SELECT statement:
| SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons INNER JOIN Orders ON Persons.P_Id=Orders.P_Id ORDER BY Persons.LastName |
The result-set will look like this:
| LastName | FirstName | OrderNo |
|---|---|---|
| Hansen | Ola | 22456 |
| Hansen | Ola | 24562 |
| Pettersen | Kari | 77895 |
| Pettersen | Kari | 44678 |
SQL LEFT JOIN Keyword
The LEFT JOIN keyword returns all rows from the left table (table_name1), even if there are no matches in the right table (table_name2).SQL LEFT JOIN Syntax
| SELECT column_name(s) FROM table_name1 LEFT JOIN table_name2 ON table_name1.column_name=table_name2.column_name |
SQL LEFT JOIN Example
The "Persons" table:| P_Id | LastName | FirstName | Address | City |
|---|---|---|---|---|
| 1 | Hansen | Ola | Timoteivn 10 | Sandnes |
| 2 | Svendson | Tove | Borgvn 23 | Sandnes |
| 3 | Pettersen | Kari | Storgt 20 | Stavanger |
The "Orders" table:
| O_Id | OrderNo | P_Id |
|---|---|---|
| 1 | 77895 | 3 |
| 2 | 44678 | 3 |
| 3 | 22456 | 1 |
| 4 | 24562 | 1 |
| 5 | 34764 | 15 |
Now we want to list all the persons and their orders - if any, from the tables above.
We use the following SELECT statement:
| SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons LEFT JOIN Orders ON Persons.P_Id=Orders.P_Id ORDER BY Persons.LastName |
The result-set will look like this:
| LastName | FirstName | OrderNo |
|---|---|---|
| Hansen | Ola | 22456 |
| Hansen | Ola | 24562 |
| Pettersen | Kari | 77895 |
| Pettersen | Kari | 44678 |
| Svendson | Tove |
SQL RIGHT JOIN Keyword
The RIGHT JOIN keyword returns all the rows from the right table (table_name2), even if there are no matches in the left table (table_name1).SQL RIGHT JOIN Syntax
| SELECT column_name(s) FROM table_name1 RIGHT JOIN table_name2 ON table_name1.column_name=table_name2.column_name |
SQL RIGHT JOIN Example
The "Persons" table:| P_Id | LastName | FirstName | Address | City |
|---|---|---|---|---|
| 1 | Hansen | Ola | Timoteivn 10 | Sandnes |
| 2 | Svendson | Tove | Borgvn 23 | Sandnes |
| 3 | Pettersen | Kari | Storgt 20 | Stavanger |
| O_Id | OrderNo | P_Id |
|---|---|---|
| 1 | 77895 | 3 |
| 2 | 44678 | 3 |
| 3 | 22456 | 1 |
| 4 | 24562 | 1 |
| 5 | 34764 | 15 |
Now we want to list all the orders with containing persons - if any, from the tables above.
We use the following SELECT statement:
| SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons RIGHT JOIN Orders ON Persons.P_Id=Orders.P_Id ORDER BY Persons.LastName |
The result-set will look like this:
| LastName | FirstName | OrderNo |
|---|---|---|
| Hansen | Ola | 22456 |
| Hansen | Ola | 24562 |
| Pettersen | Kari | 77895 |
| Pettersen | Kari | 44678 |
| 34764 |
Subscribe to:
Posts (Atom)