How can I write query to output this format in SQLite?
- by GivenPie
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?
