Select query fetch data from 2 tables : sqlite

Question

I have two tables "Student_Info Table" and "Training_Status Table"

Student_Info contains information about name , groupId of student , group roll number of student , age etc. Training_Status Table contains entries of status of training of different students

Structure of tables is as follow:

Student_Info Table

groupId    GroupRollNo     Name      Age     Level
- - - - - - - - - - - - - - - -  - - -  - -  -  - -  
G1             1           Sam       24        4 
G1             2           David     25        2 
G1             3           Robin     26        3 
G1             4           John      28        4 
G2             1           Harry     26        3 
G2             2           Jerry     28        4 
G3             1           Michel    28        3
G3             2           Jerry     28        4 
G4             1           Michel    28        3

Students are divided into different groups G1 , G2 , G3 Students in the same group can not have same GroupRollNo. but students from different groups can have same GroupRollNo.

Training_Status Table

groupId     GroupRollNo    TrainingStatus
- - - - - - - - - - - - - - - - - - - - - 
 G1             2            InProgress
 G1             3            OnHold
 G3             1            InProgress
 G3             2            Completed

As the training of a student starts Entry is made into "Training_Status Table" with groupId and GroupRollNo and Training Status. and the table is updated with the status of training (OnHold , completed)

Considering the Scenario

There are 100 students in the Student_Info Table divided into different groups. (say G1 , G2 , G3 ). Training_Status Table has only 10 entries (only of those students whose training has started ) from different groups with current status.

I need to fetch the Training Status of all students from a particular group with full details of student from two tables.
Like I need to fetch the Training status of all students of G1 (Group 1) with full details of students from a Sqlite DB query.

Expected desired result of query

groupId    GroupRollNo     Name        Age     Level    TrainingStatus
- - - - - - - - - - - - - - - -  - - -  - -  -  - - - - - - - - - - - - -
 G1             1          Sam         24        4      NULL(means training not started)
 G1             2          David       25        2      InProgress
 G1             3          Robin       26        3      OnHold
 G1             4          John        28        4      NULL(means training not started)

What sqlite Query should I write So that I get the result in the desired format from Two Tables.

luis_js · Accepted Answer · 2014-05-29 10:23:51Z

1

I just created a sample to illustrate how to do it based on your data:

-- Set up sqlite
.mode column
.headers ON
--
DROP TABLE IF EXISTS Student_Id;
CREATE TABLE Student_Id (
   groupId TEXT,
   GroupRollNo INTEGER,
   Name TEXT,
   Age INTEGER,
   Level INTEGER,
   UNIQUE(groupId, GroupRollNo));
--
DROP TABLE IF EXISTS Training_Status;
CREATE TABLE Training_Status (
   groupId TEXT,
   GroupRollNo INTEGER,
   TrainingStatus TEXT,
   UNIQUE(groupId, GroupRollNo));
--
-- Create Data
INSERT INTO Student_Id (groupId, GroupRollNo, Name, Age, Level) VALUES("G1", 1, "Sam", 24, 4);
INSERT INTO Student_Id (groupId, GroupRollNo, Name, Age, Level) VALUES("G1", 2, "Sam", 24, 4);
INSERT INTO Student_Id (groupId, GroupRollNo, Name, Age, Level) VALUES("G1", 3, "Sam", 24, 4);
INSERT INTO Student_Id (groupId, GroupRollNo, Name, Age, Level) VALUES("G1", 4, "Sam", 24, 4);
INSERT INTO Student_Id (groupId, GroupRollNo, Name, Age, Level) VALUES("G2", 1, "Sam", 24, 4);
INSERT INTO Student_Id (groupId, GroupRollNo, Name, Age, Level) VALUES("G2", 2, "Sam", 24, 4);
INSERT INTO Student_Id (groupId, GroupRollNo, Name, Age, Level) VALUES("G3", 1, "Sam", 24, 4);
INSERT INTO Student_Id (groupId, GroupRollNo, Name, Age, Level) VALUES("G3", 2, "Sam", 24, 4);
INSERT INTO Student_Id (groupId, GroupRollNo, Name, Age, Level) VALUES("G4", 1, "Sam", 24, 4);
INSERT INTO Student_Id (groupId, GroupRollNo, Name, Age, Level) VALUES("G4", 2, "Sam", 24, 4);
-- 2nd Table
INSERT INTO Training_Status (groupId, GroupRollNo, TrainingStatus) VALUES("G1", 2, "InProgress");
INSERT INTO Training_Status (groupId, GroupRollNo, TrainingStatus) VALUES("G1", 3, "OnHold");
INSERT INTO Training_Status (groupId, GroupRollNo, TrainingStatus) VALUES("G3", 1, "InProgress");
INSERT INTO Training_Status (groupId, GroupRollNo, TrainingStatus) VALUES("G3", 2, "Completed");

Queries Section:

-- Check data
SELECT * FROM Student_Id;
SELECT * FROM Training_Status;

-- 1.- Query (inner JOIN: only records that are on both tables)
SELECT t1.groupId AS groupId, t1.GroupRollNo AS GroupRollNo, t1.Name AS Name, 
   t1.Age AS Age, t1.Level AS Level, t2.TrainingStatus AS TrainingStatus
FROM Student_Id AS t1, Training_Status AS t2
WHERE t1.groupID = t2.groupId AND t1.GroupRollNo = t2.GroupRollNo AND t1.groupId = "G1"
ORDER BY t1.groupId, t1.GroupRollNo;

-- 2.- Query (outer join: Both tables are combined together showing all records)
SELECT t1.groupId AS groupId, t1.GroupRollNo AS GroupRollNo, t1.Name AS Name, 
   t1.Age AS Age, t1.Level AS Level, t2.TrainingStatus AS TrainingStatus
FROM Student_Id AS t1 LEFT JOIN Training_Status AS t2
ON t1.groupID = t2.groupId AND t1.GroupRollNo = t2.GroupRollNo
WHERE t1.groupId = "G1"
ORDER BY t1.groupId, t1.GroupRollNo;

-- 3.- Query for NOT NULL (outer join: Both tables are combined together showing all records)
SELECT t1.groupId AS groupId, t1.GroupRollNo AS GroupRollNo, t1.Name AS Name, 
   t1.Age AS Age, t1.Level AS Level, t2.TrainingStatus AS TrainingStatus
FROM Student_Id AS t1 LEFT JOIN Training_Status AS t2
ON t1.groupID = t2.groupId AND t1.GroupRollNo = t2.GroupRollNo
WHERE t1.groupId = "G1" AND t2.TrainingStatus IS NOT NULL
ORDER BY t1.groupId, t1.GroupRollNo;

-- 4.- Query for NULL (outer join: Both tables are combined together showing all records)
SELECT t1.groupId AS groupId, t1.GroupRollNo AS GroupRollNo, t1.Name AS Name, 
   t1.Age AS Age, t1.Level AS Level, typeof(t2.TrainingStatus) AS TrainingStatus
FROM Student_Id AS t1 LEFT JOIN Training_Status AS t2
ON t1.groupID = t2.groupId AND t1.GroupRollNo = t2.GroupRollNo
WHERE t1.groupId = "G1" AND t2.TrainingStatus IS NULL
ORDER BY t1.groupId, t1.GroupRollNo;

Please note the following:

UNIQUE(...) constrain will prevent two entries with the same groupId and GroupRollNo.

Query joint both tables using explicitly the common columns groupId and GroupRollNo.

NULL values need to be queried using IS NULL instead of = NULL.

edited May 29, 2014 at 10:23

answered May 28, 2014 at 22:03

luis_js

6415 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Dinesh Kaushik Over a year ago

Hi @luis_js your query only gives the result of those students Who have their status in Training_Status Table. But I want to fetch the Status of all the students of a Group Even if the student training has not started (means no status for that student in Tarining_Status)

luis_js Over a year ago

Done. Check 2nd query, the one with the LEFT JOIN. This is a type of an outer join, what means that both tables are placed together side by side, linking together those fields with the same Id.

luis_js Over a year ago

I was struggling while querying for NULL values, I just figure out that it is necessary to use IS NULL instead of = NULL to make it work. Updated on the answer.

Dinesh Kaushik Over a year ago

Thanks , I also figured it out Using the LEFT OUTER JOIN

Collectives™ on Stack Overflow

Select query fetch data from 2 tables : sqlite

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related