CODEWITHSUNDEEP
sqlsqliteview
GivenPie
GivenPie

Reputation: 1489

How can I write query to output this format in SQLite?

I would like to output in this format: 

e.EE_id e.FNAME e.LNAME SUPer_id s.FNAME s.LNAME
--- --------- -------------- --- ------------- -------------------
1    Ziqiao    Li
2    Charlie   Li     1      Ziqiao   Li
3    George    Pee    2      Charlie  Li
4    Jason     Dee    2      Charlie  Li
5    Petey     Wee    2      Charlie  Li

From this table created : I need to display the Primary key and foreign key in the same results while displaying the foreign key name values for the primary key names. 

Create table Employees(
ee_id integer,
fname varchar(20),
lname varchar(20),
super_id integer,
Constraint emp_Pk Primary Key (ee_id),
Constraint emp_Fk Foreign Key (super_id) references employees (ee_id)
);

INSERT INTO Employees VALUES(1,'Charlie','Li',null);
INSERT INTO Employees VALUES(2,'Ziqiao','Lee',1);
INSERT INTO Employees VALUES(3,'George','Pee',2);
INSERT INTO Employees VALUES(4,'Jason','Dee',2);
INSERT INTO Employees VALUES(5,'Petey','Wee',2);

Select ee_id, fname, lname, super_id from employees;

  ee_id       fname       lname       super_id
----------  ----------  ----------  ----------
1           Charlie     Li
2           Ziqiao      Lee         1
3           George      Pee         2
4           Jason       Dee         2
5           Petey       Wee         2

Do I need to create a view?

Upvotes: 0

Views: 89

Answers (2)

Lamak
Lamak

Reputation: 70648

You need a JOIN:

Select e1.ee_id, e1.fname, e1.lname, e1.super_id, e2.fname AS Super_fname, e2.lname AS Superlname
from employees e1
left join employees e2
ON e1.super_id = e2.ee_id

Upvotes: 1

Adriano Carneiro
Adriano Carneiro

Reputation: 58615

Just join the table to itself:

select e1.ee_id       
       e1.fname EmployeeFirstName,
       e1.lname EmployeeLastName,
       e1.super_id,
       e2.fname SuperFirstName,
       e2.lname SuperLastName
from Employees e1
left join Employees e2 on e1.super_id = e2.ee_id

The first mention (e1) will get data for the employee, the second (e2) will bring the data for the supervisor.

The LEFT JOIN will ensure a employee with no supervisor (ee_id = 1) will be present in the results.

Upvotes: 1

Related Questions