CODEWITHSUNDEEP
databasesqlite
Dinesh Kaushik
Dinesh Kaushik

Reputation: 2987

Select query fetch data from 2 tables : sqlite

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.

Upvotes: 1

Views: 2284

Answers (1)

luis_js
luis_js

Reputation: 611

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.

Upvotes: 1

Related Questions