0

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.PersonID

joins 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)
2
Contributors
1
Reply
2
Views
7 Years
Discussion Span
Last Post by jbisono
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.