i have supertype-subtype:
supertype: document
subtype: clearance, sedula
this query:
SELECT Document.DocNo,
(Person.Givenname + SPACE(1) + Person.Middlename + SPACE(1) + Person.Lastname) AS Name,
Person.Address,
CASE WHEN DATEADD(YEAR, DATEDIFF (YEAR, Person.DOB, CURRENT_TIMESTAMP), Person.DOB) > CURRENT_TIMESTAMP
THEN DATEDIFF(YEAR, Person.DOB, CURRENT_TIMESTAMP) - 1
ELSE DATEDIFF(YEAR, Person.DOB, CURRENT_TIMESTAMP)
END AS 'Age',
Person.Status, Person.Picture, Person.Citizenship,
Clearance.Findings, Clearance.Purpose,
Sedula.CTCNo, Document.PlaceIssued, Document.DateIssued AS CTCDIssued,
Receipt.ORno, Receipt.Amount, Receipt.DateIssued AS ReceiptDIssued,
(Officer.Givenname + SPACE(1) + Officer.Middlename + SPACE(1) + Officer.Lastname) AS Officer
FROM Document
INNER JOIN CLEARANCE ON CLEARANCE.DocID = DOCUMENT.DocNo
INNER JOIN Receipt ON Receipt.DocID = DOCUMENT.ORNo
LEFT OUTER JOIN Sedula ON Sedula.DocID = Clearance.CTCNo
INNER JOIN Officer ON Officer.OfficerID = Document.OfficerID
INNER JOIN Person ON Person.PersonID = Document.PersonIDjoins document>clearance and document>sedula
however, this line:
Document.PlaceIssued, Document.DateIssued AS CTCDIssued,should display the PlaceIssued and DateIssued field of the document>sedula. but i returns the PlaceIssued and DateIssued field of the document>clearance instead.
any thoughts bout this?
this is my database
CREATE DATABASE Lias
GO
USE Lias
Go
CREATE TABLE Person (
PersonID INT IDENTITY(1,1) CONSTRAINT pk_pid PRIMARY KEY,
Lastname VARCHAR(20) NOT NULL,
Givenname VARCHAR(25) NOT NULL,
Middlename VARCHAR(20) NOT NULL,
[Address] VARCHAR(50) NOT NULL,
[Status] VARCHAR(20) NOT NULL,
Citizenship VARCHAR(20) NOT NULL,
DOB DATETIME NOT NULL,
POB VARCHAR(50) NOT NULL,
Height INT NOT NULL,
[Weight] INT NOT NULL,
Business VARCHAR(50) NULL,
Profession VARCHAR(50) NULL,
Occupation VARCHAR(50) NULL,
Picture IMAGE )
GO
CREATE TABLE Receipt (
ORno INT IDENTITY(1,1) CONSTRAINT pk_orno PRIMARY KEY,
FACode VARCHAR(50) NOT NULL,
Amount INT NOT NULL,
Nature VARCHAR(50) NOT NULL,
PaymentType VARCHAR(30) NOT NULL,
DateIssued DATETIME NOT NULL )
GO
CREATE TABLE Officer (
OfficerID INT IDENTITY(1,1) CONSTRAINT pk_officerid PRIMARY KEY,
Lastname VARCHAR(20) NOT NULL,
Givenname VARCHAR(25) NOT NULL,
Middlename VARCHAR(20) NOT NULL )
GO
CREATE TABLE Clearance (
DocID INT CONSTRAINT pk_Cid PRIMARY KEY,
Findings VARCHAR(200) NOT NULL,
Purpose VARCHAR(200) NOT NULL )
GO
CREATE TABLE Sedula (
DocID INT CONSTRAINT pk_Sid PRIMARY KEY,
CTCNo INT NOT NULL,
TIN VARCHAR NULL,
OwnerEarning INT NULL,
EmployeeEarning INT NULL,
RPIncome INT NULL )
GO
CREATE TABLE BClearance (
DocID INT CONSTRAINT pk_BCid PRIMARY KEY,
BName VARCHAR(50) NOT NULL,
[Address] VARCHAR(50) NOT NULL,
Position VARCHAR(50) NOT NULL )
GO
CREATE TABLE ABClearance (
DocID INT CONSTRAINT pk_ABCid PRIMARY KEY,
BName VARCHAR(50) NOT NULL,
[Address] VARCHAR(50) NOT NULL,
GrossSales INT NOT NULL,
Capital INT NOT NULL,
Renew BIT NOT NULL,
Conform BIT,
Rules BIT,
NotBanned BIT,
NoObjection BIT )
GO
CREATE TABLE Document (
DocNo INT IDENTITY(1,1) CONSTRAINT pk_docno PRIMARY KEY,
PersonID INT CONSTRAINT fk_pid references Person(PersonID),
Orno INT CONSTRAINT fk_orno references Receipt(ORno),
OfficerID INT CONSTRAINT fk_officerid references Officer(OfficerID),
DateIssued DATETIME NOT NULL,
PlaceIssued VARCHAR(50) NOT NULL )
GO
ALTER TABLE Clearance ADD CONSTRAINT fk_Cid FOREIGN KEY(DocID) REFERENCES Document(DocNo)
GO
ALTER TABLE sedula ADD CONSTRAINT fk_Sid FOREIGN KEY(DocID) REFERENCES Document(DocNo)
GO
ALTER TABLE BClearance ADD CONSTRAINT fk_BCid FOREIGN KEY(DocID) REFERENCES Document(DocNo)
GO
ALTER TABLE ABClearance ADD CONSTRAINT fk_ABCid FOREIGN KEY(DocID) REFERENCES Document(DocNo)
GO
ALTER TABLE Receipt ADD DocID INT FOREIGN KEY REFERENCES Document(DocNo)
GO
ALTER TABLE Clearance ADD CTCNo INT FOREIGN KEY REFERENCES Sedula(DocID)