Tell me if I am missing anything (PLZ)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Tell me if I am missing anything (PLZ)

Author

Topic

Danny4003
Starting Member

40 Posts

Posted - 2007-01-08 : 14:19:18

I created this Join and I am receiving multiple results could you give me a couple of reasons why this could be happening.

Select TractsTbl.TractNumber,StatesTbl.States,ProjectTbl.ProjectName,OwnersTbl.[Name],OwnerType.OwnType,
ProjectTbl.County_Parish from TractsTbl
INNER JOIN ProjectTbl ON (TractsTbl.ProjectID = ProjectTbl.ProjectID)
INNER JOIN StatesTbl ON (TractsTbl.State = StatesTbl.StatesID)
INNER JOIN OwnersTbl ON (OwnersTbl.ProjectID = ProjectTbl.ProjectID)
INNER JOIN OwnerType ON (OwnerType.OwnTypeID = OwnersTbl.OwnerType)

These are my Results::

TractNum State ProjectName Owner OwnerType Acres County
478956 Texas TestProj1 Jack Harbour Accountant 41.181 Jasper 545698 Texas TestProj1 Jack Harbour Accountant 41.181 Jasper
658985 Texas TestProj1 Jack Harbour Accountant 41.181 Jasper
123458 Oklahoma TestProj3 Jarred Landry Boss Man 273.161 Oklahoma
231654 Oklahoma TestProj3 Jarred Landry Boss Man 273.161 Oklahoma
658987 Alabama TestProj2 JoJo Lodrigue Boss Man 321.454 Alabama
898745 Nebraska TestProj4 Danny Dubroc Boss Man 458.213 ornHusker
478956 Texas TestProj1 Nick Orwell Inspector 41.181 Jasper
545698 Texas TestProj1 Nick Orwell Inspector 41.181 Jasper
658985 Texas TestProj1 Nick Orwell Inspector 41.181 Jasper
123458 Oklahoma TestProj3 Dave Trahan LandMan 273.161 Oklahoma
231654 Oklahoma TestProj3 Dave Trahan LandMan 273.161 Oklahoma
478956 Texas TestProj1 Todd Newbanks LandMan 41.181 Jasper
545698 Texas TestProj1 Todd Newbanks LandMan 41.181 Jasper
658985 Texas TestProj1 Todd Newbanks LandMan 41.181 Jasper

here is my sample database:

CREATE TABLE [TractsTbl] (
[TractID] [int] PRIMARY KEY IDENTITY(1,1)NOT NULL ,
[ProjectID] [int] NULL ,
[TractNumber] [int] NOT NULL ,
[Acreage] [numeric](12, 3) NULL ,
[Location] [varchar](50) NOT NULL ,
[County] [varchar] (50) NULL ,
[State] [int] NULL ,
[Description] [text] NULL ,
[TractImg] [int] NULL ,
[TractCount] [int] NULL ,
[Status] [int] NULL ,
[LocationType] [varchar] (50) NULL,
CONSTRAINT Status_FK1 FOREIGN KEY(Status) REFERENCES StatusTbl (StatusID),
CONSTRAINT State_FK2 FOREIGN KEY (State) REFERENCES StatesTbl (StatesID),
CONSTRAINT Project_FK1 FOREIGN KEY (ProjectID)REFERENCES ProjectTbl (ProjectID)
)

CREATE TABLE [StatusTbl] (
[StatusID] [int] PRIMARY KEY IDENTITY(1,1)NOT NULL ,
[Status] [varchar] (25) NOT NULL
)
INSERT INTO StatusTbl VALUES('Open')
INSERT INTO StatusTbl VALUES('Closed')
INSERT INTO StatusTbl VALUES('Limbo')

CREATE TABLE [StatesTbl] (
[StatesID] [int]PRIMARY KEY IDENTITY(1,1) NOT NULL ,
[States] [varchar] (25) NOT NULL
)
INSERT INTO StatesTbl VALUES('Nebraska')
INSERT INTO StatesTbl VALUES('Alabama')
INSERT INTO StatesTbl VALUES('Texas')
INSERT INTO StatesTbl VALUES('Oklahoma')

CREATE TABLE [ProjectTbl] (
[ProjectID] [int]PRIMARY KEY IDENTITY(1,1) NOT NULL ,
[ProjectName] [varchar] (50) NULL ,
[County_Parish] [varchar] (50) NULL ,
[StateID] [int] NULL ,
[County_Parish_Seat] [varchar] (50) NULL ,
[Est_Totlal_Acres] [numeric](12, 3) NULL ,
[Project_Start_Date] [datetime] NULL ,
[Project_Billing_Date] [datetime] NULL ,
[StatusID] [int] NULL ,
[AccountNumber] [varchar] (50) NULL,
CONSTRAINT State_FK1 FOREIGN KEY (StateID) REFERENCES StatesTbl (StatesID)
)

CREATE TABLE [OwnerType] (
[OwnTypeID] [int]PRIMARY KEY IDENTITY (1,1) NOT NULL ,
[OwnType] [varchar] (50) NULL
)
INSERT INTO OwnerType Values('LandMan')
INSERT INTO OwnerType Values('Accountant')
INSERT INTO OwnerType Values('Inspector')
INSERT INTO OwnerType Values('Boss Man')
Select * from OwnerType
----------------------------------------------

CREATE TABLE [OwnersTbl] (
[OwnerID] [int]PRIMARY KEY IDENTITY(1,1) NOT NULL ,
[ProjectID][int]Null,
[OwnerType] [int] NULL ,
[TractNumber] [int] NULL ,
[Name] [varchar] (50) NULL ,
[Spouse] [varchar] (50) NULL ,
[Address] [varchar](255) NULL ,
[PhoneNumber] [char](25) NULL ,
[Comments] [text] NULL,
CONSTRAINT Project_FK2 FOREIGN KEY(ProjectID)REFERENCES ProjectTbl (ProjectID),
CONSTRAINT OwnerType_FK1 FOREIGN KEY (OwnerType)REFERENCES OwnerType (OwnTypeID)
)

INSERT INTO ProjectTbl Values('TestProj1','Jasper','3','Jasper Seat','123.545','12/12/1992','12/25/2000','3','123456789')
INSERT INTO ProjectTbl Values('TestProj2','Alabama','2','Alabama Seat','321.454','05/05/2002','05/25/2002','2','321654987')
INSERT INTO ProjectTbl Values('TestProj3','Oklahoma','4','Okla Seat','546.321','02/02/2003','02/25/2003','1','789456123')
INSERT INTO ProjectTbl Values('TestProj4','Corn Husker','1','Neb Seat','458.213','09/20/2004','09/30/2004','3','956874612')

INSERT INTO TractsTbl Values('5','123458','273.161','Oklahoma','Oklahoma','4','This Tract has 50% of mineral to Tot.Acres','14','2','1','N/A')
INSERT INTO TractsTbl Values('5','231654','273.161','Oklahoma','Oklahoma','4','This Tract has the other 50% to ToT.Acres','12','2','1','N/A')
INSERT INTO TractsTbl Values('4','898745','458.213','Nebraska','Corn Husker','1','100% belongs to this Tract','10','1','3','N/A')
INSERT INTO TractsTbl Values('2','658987','321.454','Alabama','Alabama','2','100% belongs to this Tract','2','1','2','N/A')
INSERT INTO TractsTbl Values('1','545698','41.181','Texas','Jasper','3','75% belongs to this Tract','6','3','3','N/A')
INSERT INTO TractsTbl Values('1','658985','41.181','Texas','Jasper','3','75% belongs to this Tract','8','3','3','N/A')
INSERT INTO TractsTbl Values('1','478956','41.181','Texas','Jasper','3','75% belongs to this Tract','4','3','3','N/A')

INSERT INTO OwnersTbl Values('5','1','123458','Dave Trahan','N/A','312B Knollwood Dr. Lafayette,La 70506','337-346-5566','This is the LandMan for this Tract')
INSERT INTO OwnersTbl Values('5','4','231654','Jarred Landry','N/A','154 Circle Rd. Dallas,TX 54668','504-258-9689','This is the Boss of the LandMan for this Tract')
INSERT INTO OwnersTbl Values('4','4','898745','Danny Dubroc','Constance Dubroc','1065 Ellen Rd. Ville Platte,La 70586','337-290-0880','This is the Boss of 100% for this Tract')
INSERT INTO OwnersTbl Values('2','4','658987','JoJo Lodrigue','N/A','254 Ballon Dr. Port Barre,La 70508','337-564-5456','This Boss owns 100% of this Tract')
INSERT INTO OwnersTbl Values('1','1','545698','Todd Newbanks','Jackie Newbanks','215 Lexman Dr. Opelousas,La 70570','337-564-2525','This LandMan owns 75% of this Tract')
INSERT INTO OwnersTbl Values('1','2','658985','Jack Harbour','N/A','222 Jake Dr. Jasper,TX 54667','504-856-8080','This Accountant owns 75% of this Tract')
INSERT INTO OwnersTbl Values('1','3','478956','Nick Orwell','Melissa Orwell','54 Tom Green,TX 54665','504-956-5454','This Inspector owns the last 75% of this Tract')

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2007-01-08 : 14:39:32

Thanks for the DDL and DML but they errored out when I tried to run it.

>>I am receiving multiple results could you give me a couple of reasons why this could be happening.

There is no criteria to limit the results either in a WHERE clause or your JOIN criteria. You will receive all rows from your tables where there is related data based on your joins.

Be One with the Optimizer
TG

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-08 : 17:42:37

After rearraning the DDL, I can see that Project 5 is missing from the ProjectTbl.
Here is the new DDL order.

CREATE TABLE [StatesTbl] (
[StatesID] [int]PRIMARY KEY IDENTITY(1,1) NOT NULL ,
[States] [varchar] (25) NOT NULL 
) 

CREATE TABLE [ProjectTbl] (
[ProjectID] [int]PRIMARY KEY IDENTITY(1,1) NOT NULL ,
[ProjectName] [varchar] (50) NULL ,
[County_Parish] [varchar] (50) NULL ,
[StateID] [int] NULL ,
[County_Parish_Seat] [varchar] (50) NULL ,
[Est_Totlal_Acres] [numeric](12, 3) NULL ,
[Project_Start_Date] [datetime] NULL ,
[Project_Billing_Date] [datetime] NULL ,
[StatusID] [int] NULL ,
[AccountNumber] [varchar] (50) NULL,
CONSTRAINT State_FK1 FOREIGN KEY (StateID) REFERENCES StatesTbl (StatesID) 
)

CREATE TABLE [StatusTbl] (
[StatusID] [int] PRIMARY KEY IDENTITY(1,1)NOT NULL ,
[Status] [varchar] (25) NOT NULL 
) 

CREATE TABLE [TractsTbl] (
[TractID] [int] PRIMARY KEY IDENTITY(1,1)NOT NULL ,
[ProjectID] [int] NULL ,
[TractNumber] [int] NOT NULL ,
[Acreage] [numeric](12, 3) NULL ,
[Location] [varchar](50) NOT NULL ,
[County] [varchar] (50) NULL ,
[State] [int] NULL ,
[Description] [text] NULL ,
[TractImg] [int] NULL ,
[TractCount] [int] NULL ,
[Status] [int] NULL ,
[LocationType] [varchar] (50) NULL,
CONSTRAINT Status_FK1 FOREIGN KEY(Status) REFERENCES StatusTbl (StatusID),
CONSTRAINT State_FK2 FOREIGN KEY (State) REFERENCES StatesTbl (StatesID),
CONSTRAINT Project_FK1 FOREIGN KEY (ProjectID)REFERENCES ProjectTbl (ProjectID)
) 

CREATE TABLE [OwnerType] (
[OwnTypeID] [int]PRIMARY KEY IDENTITY (1,1) NOT NULL ,
[OwnType] [varchar] (50) NULL 
) 


CREATE TABLE [OwnersTbl] (
[OwnerID] [int]PRIMARY KEY IDENTITY(1,1) NOT NULL ,
[ProjectID][int]Null,
[OwnerType] [int] NULL ,
[TractNumber] [int] NULL ,
[Name] [varchar] (50) NULL ,
[Spouse] [varchar] (50) NULL ,
[Address] [varchar](255) NULL ,
[PhoneNumber] [char](25) NULL ,
[Comments] [text] NULL,
CONSTRAINT Project_FK2 FOREIGN KEY(ProjectID)REFERENCES ProjectTbl (ProjectID),
CONSTRAINT OwnerType_FK1 FOREIGN KEY (OwnerType)REFERENCES OwnerType (OwnTypeID)
)

INSERT INTO StatesTbl VALUES('Nebraska')
INSERT INTO StatesTbl VALUES('Alabama')
INSERT INTO StatesTbl VALUES('Texas')
INSERT INTO StatesTbl VALUES('Oklahoma')


INSERT INTO ProjectTbl Values('TestProj1','Jasper','3','Jasper Seat','123.545','12/12/1992','12/25/2000','3','123456789')
INSERT INTO ProjectTbl Values('TestProj2','Alabama','2','Alabama Seat','321.454','05/05/2002','05/25/2002','2','321654987')
INSERT INTO ProjectTbl Values('TestProj3','Oklahoma','4','Okla Seat','546.321','02/02/2003','02/25/2003','1','789456123')
INSERT INTO ProjectTbl Values('TestProj4','Corn Husker','1','Neb Seat','458.213','09/20/2004','09/30/2004','3','956874612')


INSERT INTO StatusTbl VALUES('Open')
INSERT INTO StatusTbl VALUES('Closed')
INSERT INTO StatusTbl VALUES('Limbo')



--INSERT INTO TractsTbl Values('5','123458','273.161','Oklahoma','Oklahoma','4','This Tract has 50% of mineral to Tot.Acres','14','2','1','N/A')
--INSERT INTO TractsTbl Values('5','231654','273.161','Oklahoma','Oklahoma','4','This Tract has the other 50% to ToT.Acres','12','2','1','N/A')
INSERT INTO TractsTbl Values('4','898745','458.213','Nebraska','Corn Husker','1','100% belongs to this Tract','10','1','3','N/A')
INSERT INTO TractsTbl Values('2','658987','321.454','Alabama','Alabama','2','100% belongs to this Tract','2','1','2','N/A')
INSERT INTO TractsTbl Values('1','545698','41.181','Texas','Jasper','3','75% belongs to this Tract','6','3','3','N/A')
INSERT INTO TractsTbl Values('1','658985','41.181','Texas','Jasper','3','75% belongs to this Tract','8','3','3','N/A')
INSERT INTO TractsTbl Values('1','478956','41.181','Texas','Jasper','3','75% belongs to this Tract','4','3','3','N/A')

INSERT INTO OwnerType Values('LandMan')
INSERT INTO OwnerType Values('Accountant')
INSERT INTO OwnerType Values('Inspector')
INSERT INTO OwnerType Values('Boss Man')


--INSERT INTO OwnersTbl Values('5','1','123458','Dave Trahan','N/A','312B Knollwood Dr. Lafayette,La 70506','337-346-5566','This is the LandMan for this Tract')
--INSERT INTO OwnersTbl Values('5','4','231654','Jarred Landry','N/A','154 Circle Rd. Dallas,TX 54668','504-258-9689','This is the Boss of the LandMan for this Tract')
INSERT INTO OwnersTbl Values('4','4','898745','Danny Dubroc','Constance Dubroc','1065 Ellen Rd. Ville Platte,La 70586','337-290-0880','This is the Boss of 100% for this Tract')
INSERT INTO OwnersTbl Values('2','4','658987','JoJo Lodrigue','N/A','254 Ballon Dr. Port Barre,La 70508','337-564-5456','This Boss owns 100% of this Tract')
INSERT INTO OwnersTbl Values('1','1','545698','Todd Newbanks','Jackie Newbanks','215 Lexman Dr. Opelousas,La 70570','337-564-2525','This LandMan owns 75% of this Tract')
INSERT INTO OwnersTbl Values('1','2','658985','Jack Harbour','N/A','222 Jake Dr. Jasper,TX 54667','504-856-8080','This Accountant owns 75% of this Tract')
INSERT INTO OwnersTbl Values('1','3','478956','Nick Orwell','Melissa Orwell','54 Tom Green,TX 54665','504-956-5454','This Inspector owns the last 75% of this Tract')

/*
drop table OwnersTbl
drop table OwnerType
drop table TractsTbl
drop table StatusTbl
drop table ProjectTbl
drop table StatesTbl
*/

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-08 : 17:44:23

It is impossible to tell how to correct the query unless you also provide your expected (correct) output based on your provided sample data.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources