Home » SQL & PL/SQL » SQL & PL/SQL » Help in building a Query
Help in building a Query [message #676204] |
Mon, 20 May 2019 17:23 |
|
spirit3d
Messages: 4 Registered: May 2019
|
Junior Member |
|
|
Dear all,
I need you help to build a query in SQL for oracle.
I will need to create a view and then mount it on oracle form.
I am having difficulty to build the query as the query is display multiple field of same record.
SELECT DISTINCT jobseeker.js_id JOBSEEKER_ID,
jobseeker.js_fname FIRSTNAME,
jobseeker.js_lname LASTNAME,
qualification.qu_name QUALIFICATION,
institution.ins_name INSTITUTION,
workexperience.we_jobtitle JOBTITLE,
employerlist.el_name EMPLOYER
FROM jobseeker,
workexperience,
employerlist,
jsqualification,
institution,
qualification
WHERE jobseeker.js_id = workexperience.js_id
AND jobseeker.js_id = jsqualification.js_id
AND jsqualification.qu_id = qualification.qu_id
AND jsqualification.ins_id = institution.ins_id
AND workexperience.el_id = employerlist.el_id
AND jobseeker.js_enable = 1
ORDER BY jobseeker.js_id
Kindly refer to the attached document job.sql
In the above code record no.2 and no.3 are missing in the result.
I need these information below to appear in all the fields even though the other fields are missing or null
JOBSEEKER.JS_ID, JOBSEEKER.JS_FNAME, JOBSEEKER.JS_LNAME
rdg
James
-
Attachment: job.sql
(Size: 12.15KB, Downloaded 1935 times)
[Updated on: Mon, 20 May 2019 17:43] by Moderator Report message to a moderator
|
|
|
Re: Help in building a Query [message #676205 is a reply to message #676204] |
Mon, 20 May 2019 17:40 |
|
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
--script to create Jobseeker database
-- Date : 27/4/19
DROP TABLE jsqualification CASCADE CONSTRAINTS;
DROP TABLE institution CASCADE CONSTRAINTS;
DROP TABLE qualification CASCADE CONSTRAINTS;
DROP TABLE workexperience CASCADE CONSTRAINTS;
DROP TABLE employerlist CASCADE CONSTRAINTS;
DROP TABLE jsvacancy CASCADE CONSTRAINTS;
DROP TABLE jobvacancy CASCADE CONSTRAINTS;
DROP TABLE category CASCADE CONSTRAINTS;
DROP TABLE employer CASCADE CONSTRAINTS;
DROP TABLE jobseeker CASCADE CONSTRAINTS;
DROP TABLE town CASCADE CONSTRAINTS;
DROP TABLE district CASCADE CONSTRAINTS;
CREATE TABLE district
(district_id NUMBER(5),
district_name varchar2(20),
CONSTRAINT district_district_id_pk PRIMARY KEY (district_id));
CREATE TABLE town
(town_id NUMBER(5),
town_name VARCHAR2(30),
district_id NUMBER(5),
CONSTRAINT town_town_id_pk PRIMARY KEY(town_id),
CONSTRAINT town_district_id_fk FOREIGN KEY (district_id) REFERENCES district(district_id));
CREATE TABLE jobseeker
(js_id NUMBER(5),
js_fname VARCHAR2(50) NOT NULL,
js_lname VARCHAR2(50) NOT NULL,
js_nid CHAR(14) NOT NULL,
js_street VARCHAR2(100),
town_id NUMBER(5),
js_phone NUMBER(,
js_email VARCHAR2(30),
js_dob DATE,
jb_gender CHAR(1) NOT NULL,
js_enable NUMBER(1),
js_username VARCHAR2(10) NOT NULL UNIQUE,
js_password VARCHAR2(10) NOT NULL,
js_dateregistered DATE,
CONSTRAINT jobseeker_js_id_pk PRIMARY KEY (js_id),
CONSTRAINT jobseeker_js_nid_uk UNIQUE (js_nid),
CONSTRAINT jobseeker_id_town_fk FOREIGN KEY (town_id) REFERENCES town(town_id));
CREATE TABLE employer
(emp_id NUMBER(5),
emp_name VARCHAR2(100) NOT NULL UNIQUE,
emp_contactperson VARCHAR2(50) NOT NULL,
emp_phone NUMBER(,
emp_email VARCHAR2(30),
emp_website VARCHAR2(50),
emp_desc VARCHAR2(100),
emp_street VARCHAR2(100),
town_id NUMBER(5),
emp_incorporationno VARCHAR2(10) NOT NULL UNIQUE,
emp_dateincorporation DATE NOT NULL,
emp_brn NUMBER(7) NOT NULL UNIQUE,
emp_enable NUMBER(1),
emp_username VARCHAR2(10) NOT NULL UNIQUE,
emp_password VARCHAR2(10) NOT NULL,
emp_dateregistered DATE,
CONSTRAINT employer_emp_id_pk PRIMARY KEY (emp_id),
CONSTRAINT employer_town_id_fk FOREIGN KEY (town_id) REFERENCES town(town_id));
CREATE TABLE category
(
cat_id NUMBER(5),
cat_name VARCHAR2(100) NOT NULL UNIQUE,
CONSTRAINT category_cat_id_pk PRIMARY KEY (cat_id)
);
CREATE TABLE jobvacancy
(jv_id NUMBER(5),
jv_jobtitle VARCHAR2(30) NOT NULL,
jv_desc VARCHAR2(100),
jv_startdate DATE NOT NULL,
jv_closingdate DATE NOT NULL,
jv_yrexperience NUMBER(2),
jv_requirement VARCHAR2(100),
jv_salary NUMBER(7),
jv_durationtype CHAR(1),
cat_id NUMBER(5),
emp_id NUMBER(5),
jv_dateadvert DATE,
CONSTRAINT jobvacancy_jv_id_pk PRIMARY KEY (jv_id),
CONSTRAINT jobvacancy_cat_id_fk FOREIGN KEY (cat_id) REFERENCES category(cat_id),
CONSTRAINT jobvacancy_emp_id_fk FOREIGN KEY (emp_id) REFERENCES employer(emp_id));
CREATE TABLE jsvacancy
(jsv_id NUMBER(5),
js_id NUMBER(5),
jv_id NUMBER(5),
jsv_dateapplication DATE,
CONSTRAINT jsvacancy_jsv_id_pk PRIMARY KEY(jsv_id),
CONSTRAINT jsvacancy_jsid_jvid_uk UNIQUE (js_id, jv_id),
CONSTRAINT jsvacancy_js_id_fk FOREIGN KEY (js_id) REFERENCES jobseeker(js_id),
CONSTRAINT jsvacancy_jv_id_fk FOREIGN KEY (jv_id) REFERENCES jobvacancy(jv_id)
);
CREATE TABLE employerlist
(el_id NUMBER(5),
el_name VARCHAR2(100) NOT NULL,
CONSTRAINT employerlist_el_id_pk PRIMARY KEY (el_id));
CREATE TABLE workexperience
(we_id NUMBER(5),
we_jobtitle VARCHAR2(100) NOT NULL,
we_startdate DATE,
we_enddate DATE,
js_id NUMBER(5),
el_id NUMBER(5),
CONSTRAINT workexperience_we_id_pk PRIMARY KEY (we_id),
CONSTRAINT workexperience_js_id_fk FOREIGN KEY (js_id) REFERENCES jobseeker(js_id),
CONSTRAINT workexperience_el_id_fk FOREIGN KEY (el_id) REFERENCES employerlist(el_id));
CREATE TABLE qualification
(
qu_id NUMBER(5),
qu_name VARCHAR2(100) NOT NULL,
CONSTRAINT qualification_qu_id_pk PRIMARY KEY(qu_id)
);
CREATE TABLE institution
(ins_id NUMBER(5),
ins_name VARCHAR2(100) NOT NULL,
CONSTRAINT institution_ins_id_pk PRIMARY KEY (ins_id));
CREATE TABLE jsqualification
(jsq_id NUMBER(5),
js_id NUMBER(5),
qu_id NUMBER(5),
ins_id NUMBER(5),
jsq_dategraduated DATE,
CONSTRAINT jsqualification_jsq_id_pk PRIMARY KEY(jsq_id),
CONSTRAINT jsqualification_js_id_fk FOREIGN KEY (js_id) REFERENCES jobseeker(js_id),
CONSTRAINT jsqualification_qu_id_fk FOREIGN KEY (qu_id) REFERENCES qualification(qu_id),
CONSTRAINT jsqualification_ins_id_fk FOREIGN KEY (ins_id) REFERENCES institution(ins_id));
---inserting records into district
INSERT INTO district VALUES (1, 'Black River');
INSERT INTO district VALUES (2, 'Flacq');
INSERT INTO district VALUES (3, 'Grand Port');
INSERT INTO district VALUES (4, 'Moka');
INSERT INTO district VALUES (5, 'Pamplemousses');
INSERT INTO district VALUES (6, 'Plaines-Wilhems');
INSERT INTO district VALUES (7, 'Port-Louis');
INSERT INTO district VALUES (8, 'Rivière du Rempart');
INSERT INTO district VALUES (9, 'Savanne');
---inserting records into town
INSERT INTO town VALUES (1, 'Goodlands', 2);
INSERT INTO town VALUES (2, 'Union Park', 3);
INSERT INTO town VALUES (3, 'Reduit', 4);
INSERT INTO town VALUES (4, 'Volmar', 1);
INSERT INTO town VALUES (5, 'bambous', 1);
---inserting records into jobseeker
INSERT INTO jobseeker VALUES
(1, 'Peerboccus', 'Nasreen','N1504907192829', 'Nissan Road', 1, '57745308', 'pnsreen@live.com', to_date('4/15/1990', 'mm/dd/yyyy'), 'F', 1, 'xtrang', 'whites', to_date('2/20/2019', 'mm/dd/yyyy'));
INSERT INTO jobseeker VALUES
(2, 'Sujeeun', 'Vinay','V2010002457152', 'Kentury Road', 2, '59758456', 'vinays@hotmail.com', to_date('10/20/2000', 'mm/dd/yyyy'), 'M', 1, 'toomuch', 'finger', to_date('2/20/2019', 'mm/dd/yyyy'));
INSERT INTO jobseeker VALUES
(3, 'Paul', 'Jean','J0312752435648', 'Pink Street', 3, '58542367', 'jeanpaul@gmail.com', to_date('12/3/1975', 'mm/dd/yyyy'), 'M', 1, 'skyblue', 'blue3b', to_date('4/3/2019', 'mm/dd/yyyy'));
INSERT INTO jobseeker VALUES
(4, 'Kim', 'Mary','M2305804578545', 'Railroad Ave 3', 2, '59863528', 'kim123@outlook.com', to_date('5/23/1980', 'mm/dd/yyyy'), 'F', 1, 'global', 'rainbow', to_date('5/4/2018', 'mm/dd/yyyy'));
INSERT INTO jobseeker VALUES
(5, 'Cox', 'Matheo','M1011955874526', 'Lagrement', 5, '57892369', 'coxma45@um.net', to_date('11/10/1995', 'mm/dd/yyyy'), 'M', 1, 'attack', 'plus4u', to_date('5/10/2018', 'mm/dd/yyyy'));
---inserting records into employer
INSERT INTO employer VALUES
(1, 'IBL', 'Mr. Raj Hamgobin', '2064589', 'ibl@hotmail.com', 'http://ibl.com', 'Management', 'Inova Riche Terre Business Park', 2, 'L120364778', to_date('02/25/2000', 'mm/dd/yyyy'), '2458762', 1, 'tracywong', 'wong123',to_date('2/10/2019', 'mm/dd/yyyy') );
INSERT INTO employer VALUES
(2, 'Adecco Mauritius', 'Mrs. Amrita Bunjun', '4069604', 'contact@adecco.mu', 'http://www.adecco.mu', 'Call Centre', '111, 1st Floor', 4, 'A566339994', to_date('06/13/1999', 'mm/dd/yyyy'), '1457458', 1, 'bigbrother', 'brozer21',to_date('2/12/2019', 'mm/dd/yyyy') );
INSERT INTO employer VALUES
(3, 'Exotic Design Ltd', 'Mr. khan Alan Wan', '4671132', 'vacancies@exoticgroup.net', 'http://exotic.com', 'Construction', 'Rue de la Canelle', 3, 'E777123455', to_date('1/16/2009','mm/dd/yyyy'), '7562354', 1, 'elohibro', 'brohello',to_date('4/23/2019', 'mm/dd/yyyy') );
INSERT INTO employer VALUES
(4, 'Linkbynet Indian Ocean', 'Miss. Alice Anderson', '6605247', 'lio-rh@linkbynet.com', 'http://jobs.linkbynet.com', 'IT', 'Block B, La Tour Koenig', 1, 'L456752963', to_date('09/28/2012','mm/dd/yyyy'), '4574568', 1, 'fishhunger', 'hungry1',to_date('6/4/2018', 'mm/dd/yyyy') );
INSERT INTO employer VALUES
(5, 'Infomil (Mauritius) Ltd', 'Mrs. Kiran Emrith', '4648884', 'infomil@live.com', 'http://www.infomil.mu', 'IT', 'Eben Road, CyberTower1', 1, 'I235678963', to_date('03/17/2013','mm/dd/yyyy'), '4512458', 0, 'totototo', 'tomsawyer',to_date('8/10/2018', 'mm/dd/yyyy') );
---inserting records into category
INSERT INTO category VALUES (1, 'ICT / IT / Web');
INSERT INTO category VALUES (2, 'Human Resource');
INSERT INTO category VALUES (3, 'Science');
INSERT INTO category VALUES (4, 'Engineering / Electronics / Mechanics Jobs');
INSERT INTO category VALUES (5, 'Marketing / Sales Jobs');
---inserting records into jobvacancy
INSERT INTO jobvacancy VALUES
(1, 'IT Engineer', 'To manage all our Servers', to_date('03/25/2019', 'mm/dd/yyyy'), to_date('04/24/2019', 'mm/dd/yyyy'), 2, 'Degree in IT', '40000', 'P', 1, 1, to_date('3/3/2019', 'mm/dd/yyyy') );
INSERT INTO jobvacancy VALUES
(2, 'Laboratory Technician', 'To assist the Laboratory Manager in his duties', to_date('4/3/2019', 'mm/dd/yyyy'), to_date('04/26/2019', 'mm/dd/yyyy'), 1, 'Diploma / Degree in Science / Chemistry', '25000', 'P', 3, 2, to_date('2/25/2019', 'mm/dd/yyyy') );
INSERT INTO jobvacancy VALUES
(3, 'Marketing Officer', 'To create marketing campaign for the company', to_date('04/17/2019', 'mm/dd/yyyy'), to_date('05/17/2019', 'mm/dd/yyyy'), 2, 'Diploma in Sales / Marketing', '20000', 'T', 5, 3, to_date('4/15/2019', 'mm/dd/yyyy') );
INSERT INTO jobvacancy VALUES
(4, 'HR Manager', 'To recruit staff, To ensure staff welfare', to_date('04/16/2019', 'mm/dd/yyyy'), to_date('04/23/2019', 'mm/dd/yyyy'), 5, 'Master Degree in HR', '60000', 'P', 2, 1, to_date('4/10/2019', 'mm/dd/yyyy') );
INSERT INTO jobvacancy VALUES
(5, 'Biology Teacher', 'To teach from Grade 10 - Grade 15', to_date('04/15/2019', 'mm/dd/yyyy'), to_date('05/15/2019', 'mm/dd/yyyy'), 0, 'Degree in Biology', '23000', 'T', 3, 4, to_date('4/10/2019', 'mm/dd/yyyy') );
---inserting records into jsvacancy
INSERT INTO jsvacancy VALUES (1, 1, 2, to_date('3/29/2019', 'mm/dd/yyyy'));
INSERT INTO jsvacancy VALUES (2, 3, 1, to_date('4/18/2019', 'mm/dd/yyyy'));
INSERT INTO jsvacancy VALUES (3, 5, 2, to_date('3/30/2019', 'mm/dd/yyyy'));
INSERT INTO jsvacancy VALUES (4, 1, 5, to_date('4/20/2019', 'mm/dd/yyyy'));
---inserting records into employerlist
INSERT INTO employerlist VALUES (1, 'Global Insurance Ltd');
INSERT INTO employerlist VALUES (2, 'Marketing Board');
INSERT INTO employerlist VALUES (3, 'National Empowerment Foundation');
INSERT INTO employerlist VALUES (4, 'Lolipop Kindergarten ');
INSERT INTO employerlist VALUES (5, 'Airport of Mauritius Ltd');
---inserting records into workexperience
INSERT INTO workexperience VALUES
(1, 'Health and Safety Specialist', to_date('03/25/2019', 'mm/dd/yyyy'), to_date('04/24/2019', 'mm/dd/yyyy'), 1, 1);
INSERT INTO workexperience VALUES
(2, 'Supervisor - Mobile Phone Repairs', to_date('4/16/2010', 'mm/dd/yyyy'), to_date('04/23/2017', 'mm/dd/yyyy'), 2, 1);
INSERT INTO workexperience VALUES
(3, 'Senior Software Test Engineer', to_date('4/30/2017', 'mm/dd/yyyy'), to_date('04/26/2019', 'mm/dd/yyyy'), 2, 2);
INSERT INTO workexperience VALUES
(4, 'Human Resource Officer', to_date('4/17/2018', 'mm/dd/yyyy'), to_date('05/17/2019', 'mm/dd/yyyy'), 4, 1);
INSERT INTO workexperience VALUES
(5, 'Sales Executive', to_date('4/30/2018', 'mm/dd/yyyy'), to_date('4/15/2019', 'mm/dd/yyyy'), 5, 5);
---inserting records into qualification
INSERT INTO qualification VALUES (1, 'Degree in Computer Science');
INSERT INTO qualification VALUES (2, 'Degree in Biology');
INSERT INTO qualification VALUES (3, 'Master Degree in Human Resource');
INSERT INTO qualification VALUES (4, 'Diploma in Human Anatomy');
INSERT INTO qualification VALUES (5, 'Post-Graduate Diploma in Robot Technology');
---inserting records into institution
INSERT INTO institution VALUES (1, 'University of Maurtius');
INSERT INTO institution VALUES (2, 'University of Tecnology');
INSERT INTO institution VALUES (3, 'Open University of Mauritius');
INSERT INTO institution VALUES (4, 'Université des Mascareignes');
INSERT INTO institution VALUES (5, 'Middlesex University in Mauritius');
---inserting records into jsqualification
INSERT INTO jsqualification VALUES (1, 1, 2, 1, to_date('1/30/2015', 'mm/dd/yyyy'));
INSERT INTO jsqualification VALUES (2, 1, 5, 2, to_date('4/25/2018', 'mm/dd/yyyy'));
INSERT INTO jsqualification VALUES (3, 3, 1, 2, to_date('9/15/2000', 'mm/dd/yyyy'));
INSERT INTO jsqualification VALUES (4, 4, 4, 2, to_date('3/3/2014' , 'mm/dd/yyyy'));
INSERT INTO jsqualification VALUES (5, 5, 2, 4, to_date('11/2/2018', 'mm/dd/yyyy'));
COMMIT;
|
|
|
|
|
Re: Help in building a Query [message #676219 is a reply to message #676211] |
Tue, 21 May 2019 06:50 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
Is this what you are looking for?
SELECT Jobseeker.Js_id Jobseeker_id,
Js_fname Firstname,
Js_lname Lastname,
Qu_name Qualification,
Ins_name Institution,
We_jobtitle Jobtitle,
El_name Employer
FROM Jobseeker
LEFT OUTER JOIN Workexperience
ON (Jobseeker.Js_id = Workexperience.Js_id)
LEFT OUTER JOIN Jsqualification
ON (Jobseeker.Js_id = Jsqualification.Js_id)
LEFT OUTER JOIN Institution
ON (Jsqualification.Ins_id = Institution.Ins_id)
LEFT OUTER JOIN Qualification
ON (Jsqualification.Qu_id = Qualification.Qu_id)
LEFT OUTER JOIN Employerlist
ON (Workexperience.El_id = Employerlist.El_id)
WHERE Js_enable = 1
ORDER BY Jobseeker_id
|
|
|
Goto Forum:
Current Time: Sat May 11 10:16:41 CDT 2024
|