joining tables

Question

scias23 1 Junior Poster in Training

14 Years Ago

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

mssql

2 Contributors
13 Replies
228 Views
4 Days Discussion Span
Latest Post 14 Years Ago Latest Post by jbisono

All 13 Replies

jbisono 51 Posting Pro in Training

14 Years Ago

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

I may be wrong but you can show a little bit more of your table structure to give a more accurate answer.

regards

jbisono 51 Posting Pro in Training

14 Years Ago

When you say document>certificate what exactly is certificate for you here, because i do not see any certificate table?

jbisono 51 Posting Pro in Training

14 Years Ago

Ok.

SELECT *
FROM DOCUMENT INNER JOIN
CLEARANCE ON CLEARANCE.DOC_ID = DOCUMENT.DocNo LEFT OUTER JOIN
SEDULA ON SEDULA.DOC_ID = CLEARANCE.DOC_ID

Try that.

jbisono 51 Posting Pro in Training

14 Years Ago

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

notice that in order to get information from person in officer table you have to make the links.

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

scias23 1 Junior Poster in Training · Answer 1 · 2010-02-08T20:28:41+00:00

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.

scias23 1 Junior Poster in Training · Answer 2 · 2010-02-08T20:42:45+00:00

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?

scias23 1 Junior Poster in Training · Answer 3 · 2010-02-08T20:57:21+00:00

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

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

scias23 1 Junior Poster in Training · Answer 4 · 2010-02-08T21:03:09+00:00

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

thanks

jbisono 51 Posting Pro in Training · Answer 5 · 2010-02-08T21:06:36+00:00

jbisono 51 Posting Pro in Training

14 Years Ago

Ok. Glad to know.

scias23 1 Junior Poster in Training · Answer 6 · 2010-02-08T22:21:20+00:00

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

i 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?

jbisono 51 Posting Pro in Training · Answer 7 · 2010-02-08T22:36:33+00:00

Then you cannot reference the sedula table thru the clearance table, just make the left outer join with document table. that should do it.

scias23 1 Junior Poster in Training · Answer 8 · 2010-02-12T14:50:55+00:00

scias23 1 Junior Poster in Training

14 Years Ago

i don't get it, any sample?

jbisono 51 Posting Pro in Training · Answer 9 · 2010-02-12T19:21:09+00:00

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.

joining tables

Recommended Answers Collapse Answers

All 13 Replies

Recommended Answers