i have a supertype-subtype:
supertype: DOCUMENT
subtypes: CLEARANCE, TAX CERTIFICATE, RECEIPT, BUSINESS CLEARANCE
each clearance subtype may have a TAX CERTIFICATE, how could i join using join?
in other words,
document>clearance
document>tax certificate
i want to join document>clearance and document>tax certificate into a SINGLE view
help. thanks
I do not know if i got you right, anyway you said that a clearance may have a tax certificate sounds to me that you cannot make direct reference between the tax certificate and document but thru clearance table. if this is the case you will have something like this.
SELECT *
FROM DOCUMENT INNER JOIN
CLEARANCE ON CLEARANCE.DOCUMENT_ID = DOCUMENT.ID LEFT OUTER JOIN TAX_CERTIFICATE ON TAX_CERTIFICATE.ID_CLEARANCE = CLEARANCE.IDI may be wrong but you can show a little bit more of your table structure to give a more accurate answer.
regards
maybe this can help you help me :)
i want to join document>certificate and document>sedula. document>certificate may have a document>sedula.
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,
Age INT 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 NOT NULL )
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)I do not know if i got you right, anyway you said that a clearance may have a tax certificate sounds to me that you cannot make direct reference between the tax certificate and document but thru clearance table. if this is the case you will have something like this.
When you say document>certificate what exactly is certificate for you here, because i do not see any certificate table?
oh fudge. sorry. it's document>clearance. :)
When you say document>certificate what exactly is certificate for you here, because i do not see any certificate table?
Ok.
SELECT *
FROM DOCUMENT INNER JOIN
CLEARANCE ON CLEARANCE.DOC_ID = DOCUMENT.DocNo LEFT OUTER JOIN
SEDULA ON SEDULA.DOC_ID = CLEARANCE.DOC_IDTry that.
hey thanks it works. i want to display these:
SELECT Document.DocNo, Document.PersonID, Clearance.DocID, Sedula.DocID, Receipt.DocID, Officer.OfficerID,
(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,
Receipt.ORno, Receipt.Amount, Receipt.DateIssued,
(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.CTCNobut i get errors,
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Officer.OfficerID" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "Person.Givenname" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "Person.Middlename" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "Person.Lastname" could not be bound.
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "Person.Address" could not be bound.
Msg 4104, Level 16, State 1, Line 4
The multi-part identifier "Person.DOB" could not be bound.
Msg 4104, Level 16, State 1, Line 4
The multi-part identifier "Person.DOB" could not be bound.
Msg 4104, Level 16, State 1, Line 5
The multi-part identifier "Person.DOB" could not be bound.
Msg 4104, Level 16, State 1, Line 6
The multi-part identifier "Person.DOB" could not be bound.
Msg 4104, Level 16, State 1, Line 8
The multi-part identifier "Person.Status" could not be bound.
Msg 4104, Level 16, State 1, Line 8
The multi-part identifier "Person.Picture" could not be bound.
Msg 4104, Level 16, State 1, Line 8
The multi-part identifier "Person.Citizenship" could not be bound.
Msg 4104, Level 16, State 1, Line 12
The multi-part identifier "Officer.Givenname" could not be bound.
Msg 4104, Level 16, State 1, Line 12
The multi-part identifier "Officer.Middlename" could not be bound.
Msg 4104, Level 16, State 1, Line 12
The multi-part identifier "Officer.Lastname" could not be bound.oh nevermind, it's fine now.
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.PersonIDthanks
SELECT *
FROM DOCUMENT INNER JOIN
CLEARANCE ON CLEARANCE.DOC_ID = DOCUMENT.DocNo LEFT OUTER JOIN
SEDULA ON SEDULA.DOC_ID = CLEARANCE.DOC_ID INNER JOIN PERSON ON PERSON.PersonID = DOCUMENT.PesonID INNER JOIN
OFFICER ON OFFICER.OfficerID = DOCUMENT.OfficerIDnotice that in order to get information from person in officer table you have to make the links.
Ok. Glad to know.
Ok. Glad to know.
oops. i think there's a problem
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.PersonIDi want the code on line 10 to display the place and date issued of the sedula document
[B]Document.PlaceIssued, Document.DateIssued AS CTCDIssued,[/B]but when i run the query, it returns the place and date issued of the clearance,not the sedula.
suggestions?
Then you cannot reference the sedula table thru the clearance table, just make the left outer join with document table. that should do it.
i don't get it, any sample?
Try replacing the left outer join with this
"LEFT OUTER JOIN Sedula ON Sedula.DocID = Document.DocNo" if that does not work I think you have to go over the design again, maybe if you explain a little bit what is the relation between these three tables i can help you finding any design problem.
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.