join tables problem? - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

join tables problem?

Author

Topic

streetWinner
Starting Member

20 Posts

Posted - 2012-02-04 : 19:21:55

How can I JOIN next table, to get a valid result?

TABLE1: CommissionReport
(
idReport(PK) INT NOT NULL,
idCurriculum(FK) INT NOT NULL, //referenced table3 Curriculum, column idCurriculum
DATE date NOT NULL,
exam_Score INT NULL,
idExam INT NOT NULL,
idProfesor INT NOT NULL,
)

TABLE2: CommissionReportItems
(
idReport(PK) INT NOT NULL, //referenced table1, column idreport
idItemsReport(PK) INT NOT NULL,
studentId(FK) INT NOT NULL, //referenced table Students,
note char(255)
)

TABLE3: Curriculum
(
idCurriculum(PK) INT NOT NULL,
idDepartment(FK) INT NOT NULL, // REFERENCE table StudentIdDepartment, column idDepartnemt
yearOfStudy DATE NOT NULL,
PlanOfStudy CHAR(7) NOT NULL //LIKE THIS 2010/11
)

TABLE4: CurriculumItems
(
idCurriculum(PK) INT NOT NULL, // reference TABLE3 Curriculum, column idCurriculum
idItems(PK) INT NOT NULL,
idExam INT NOT NULL,
Semester INT NOT NULL,
ESPB INT NOT NULL
)

Those tables represent little part of ER model of Student Services database. I need some information from it, very cardinal for me, but I cant get them. From all tables I need some values, but when I include columns ESPB and SEMESTER from table4 in my join qeury, my results was abnormal!

I need next column from represents tables in my report:

idreport, idItemsreport,idCurriculum, idDepartment, idProfesor, idExam, ESPB, semester, yearOfStudy.

This solution shold have 1000 tuples. In my query results, JOIN tables gives 10,000 tuples. I don't know why?

SELECT cr.idReport,
cri.idItemsReport,
cri.StudentId,
c.idCurriculum,
c.idDepartment,
c.yearOfStudy,
cr.idExam,
cr.idProfesor,
cr.exam_Score,
ci.ESPB,
ci.semester
FROM Curriculum c
INNER JOIN CommissionReportcri cr ON cr.idCurriculum=c.idCurriculum
INNER JOIN CommissionReportItems cri ON cr.idreport=cri.idReport
INNER JOIN CurriculumItems ci ON ci.idCurriculum=c.idCurriculum
WHERE PlanOfStudy='2010/11'

When I put out of columns ESPB and SEMESTER from structure of query, query resulted successfully.

Does anyone know how to solve the problem?

P.S. Sorry for my bad English!

streetWinner

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-04 : 23:14:22

that means the relationship from Curriculum to CurriculumItems is one to many ie each record from Curriculum has more than one matches in CurriculumItems so you need to determine how you want to return value from CurriculumItems ie whether you want all of them or subset.
between one question , in your resultset of 10,000 are values of ESPB and SEMESTER getting duplicated or are they different?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

streetWinner
Starting Member

20 Posts

Posted - 2012-02-05 : 11:06:08

quote:
Originally posted by visakh16

between one question , in your result set of 10,000 are values of ESPB and SEMESTER getting duplicated or are they different?

visakh16, sorry for mistake.Join query return 372744 rows.

Values of ESPB and Semester getting duplicate, but JOIN query records are different for all 372,744 rows.

while individual tables return next results:
table Curriculum consist 9 rows,
table CurriculumItems(details) consist 214 rows,
table Commsssionreport consist 1,011 rows and
table CommissionreportItems(details) has 10,076 rows.

_________________________________________________
I real need help!

streetWinner

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-05 : 11:23:56

if thats case what does this return?


SELECT cr.idReport, 
cri.idItemsReport,
cri.StudentId,
c.idCurriculum, 
c.idDepartment,
c.yearOfStudy,
cr.idExam,
cr.idProfesor,
cr.exam_Score,
ci.ESPB,
ci.semester
FROM Curriculum c
INNER JOIN CommissionReportcri cr ON cr.idCurriculum=c.idCurriculum
INNER JOIN CommissionReportItems cri ON cr.idreport=cri.idReport
INNER JOIN (SELECT DISTINCT idCurriculum,ESPB,semester from CurriculumItems) ci ON ci.idCurriculum=c.idCurriculum
WHERE PlanOfStudy='2010/11'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

streetWinner
Starting Member

20 Posts

Posted - 2012-02-05 : 12:04:26

quote:
Originally posted by visakh16

if thats case what does this return?

SELECT cr.idReport,
cri.idItemsReport,
cri.StudentId,
c.idCurriculum,
c.idDepartment,
c.yearOfStudy,
cr.idExam,
cr.idProfesor,
cr.exam_Score,
ci.ESPB,
ci.semester
FROM Curriculum c
INNER JOIN CommissionReportcri cr ON cr.idCurriculum=c.idCurriculum
INNER JOIN CommissionReportItems cri ON cr.idreport=cri.idReport
INNER JOIN (SELECT DISTINCT idCurriculum,ESPB,semester from CurriculumItems) ci ON ci.idCurriculum=c.idCurriculum
WHERE PlanOfStudy='2010/11'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16, I didn't understand your prevision question correlated for ESPB and Semester. In table CurriculumItems there is no duplicates. Column idExam is in fact Subject (maths, biology, informatics, sql programming ...) for students. For all curiculumItems, existed 214 subjects or idExam-s. In us case, for curriculums number of 43, existed 214 idExams.

Your query returns 185,858 row(s) . Better from my attemp, but is still not well.

Sorry for my mistakes!

streetWinner

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-05 : 12:12:14

can you post some data from your tables? That should clear the doubts

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-05 : 13:51:51

just post 5 rows from tables each and show the output from them

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-05 : 19:26:08

where's the output?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-06 : 13:45:20

can you clearly state what exactly you want giving information in below format?

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

streetWinner
Starting Member

20 Posts

Posted - 2012-02-06 : 14:54:52

Oki, I hope so that is enough to the filling tables.


create database studentService -- default settings
go

USE [StudentService]
GO

CREATE DATABASE 
/****** Object:  Table [dbo].[_Curriculum]    Script Date: 02/06/2012 20:48:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[_Curriculum](
	[idCurriculum] [int] NOT NULL,
	[idDepartment] [char](2) NOT NULL,
	[yearOfStudy] [int] NOT NULL,
	[PlanOfStudy] [nchar](10) NULL,
 CONSTRAINT [PK__Curriculum] PRIMARY KEY CLUSTERED 
(
	[idCurriculum] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[_Curriculum] ([idCurriculum], [idDepartment], [yearOfStudy], [PlanOfStudy]) VALUES (43, N'15', 2009, N'2009/10   ')
INSERT [dbo].[_Curriculum] ([idCurriculum], [idDepartment], [yearOfStudy], [PlanOfStudy]) VALUES (44, N'20', 2009, N'2009/10   ')
INSERT [dbo].[_Curriculum] ([idCurriculum], [idDepartment], [yearOfStudy], [PlanOfStudy]) VALUES (45, N'29', 2009, N'2009/10   ')
INSERT [dbo].[_Curriculum] ([idCurriculum], [idDepartment], [yearOfStudy], [PlanOfStudy]) VALUES (46, N'30', 2009, N'2009/10   ')
INSERT [dbo].[_Curriculum] ([idCurriculum], [idDepartment], [yearOfStudy], [PlanOfStudy]) VALUES (47, N'31', 2009, N'2009/10   ')
/****** Object:  Table [dbo].[_curriculumDetails]    Script Date: 02/06/2012 20:48:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[_curriculumDetails](
	[idCurriculum] [int] NOT NULL,
	[IdItems] [int] NOT NULL,
	[idExam] [int] NOT NULL,
	[Semester] [int] NULL,
	[ESPB] [money] NULL,
 CONSTRAINT [pk_idCurriculumIdItems_ci] PRIMARY KEY CLUSTERED 
(
	[idCurriculum] ASC,
	[IdItems] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[_curriculumDetails] ([idCurriculum], [IdItems], [idExam], [Semester], [ESPB]) VALUES (43, 1, 520, 1, 6.0000)
INSERT [dbo].[_curriculumDetails] ([idCurriculum], [IdItems], [idExam], [Semester], [ESPB]) VALUES (43, 2, 501, 1, 8.0000)
INSERT [dbo].[_curriculumDetails] ([idCurriculum], [IdItems], [idExam], [Semester], [ESPB]) VALUES (43, 3, 506, 1, 4.0000)
INSERT [dbo].[_curriculumDetails] ([idCurriculum], [IdItems], [idExam], [Semester], [ESPB]) VALUES (43, 4, 507, 1, 5.0000)
INSERT [dbo].[_curriculumDetails] ([idCurriculum], [IdItems], [idExam], [Semester], [ESPB]) VALUES (43, 5, 521, 1, 4.0000)
/****** Object:  Table [dbo].[_Commissionreport]    Script Date: 02/06/2012 20:48:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[_Commissionreport](
	[idReport] [int] NOT NULL,
	[idCurriculum] [int] NULL,
	[Date] [date] NULL,
	[idExam] [int] NOT NULL,
	[idProfesor] [int] NOT NULL,
 CONSTRAINT [pk_idReport_C] PRIMARY KEY CLUSTERED 
(
	[idReport] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[_Commissionreport] ([idReport], [idCurriculum], [Date], [idExam], [idProfesor]) VALUES (6949, 43, CAST(0x61320B00 AS Date), 507, 90)
INSERT [dbo].[_Commissionreport] ([idReport], [idCurriculum], [Date], [idExam], [idProfesor]) VALUES (6974, 43, CAST(0x6D320B00 AS Date), 520, 1)
INSERT [dbo].[_Commissionreport] ([idReport], [idCurriculum], [Date], [idExam], [idProfesor]) VALUES (7028, 43, CAST(0x6B320B00 AS Date), 501, 33)
INSERT [dbo].[_Commissionreport] ([idReport], [idCurriculum], [Date], [idExam], [idProfesor]) VALUES (7195, 43, CAST(0x74320B00 AS Date), 506, 92)
INSERT [dbo].[_Commissionreport] ([idReport], [idCurriculum], [Date], [idExam], [idProfesor]) VALUES (7429, 43, CAST(0xBA320B00 AS Date), 520, 1)
/****** Object:  Table [dbo].[_CommissionreportDetails]    Script Date: 02/06/2012 20:48:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[_CommissionreportDetails](
	[idReport] [int] NOT NULL,
	[idItemsReport] [int] NOT NULL,
	[studentId] [int] NOT NULL,
	[Note] [varchar](50) NULL,
	[EXAM_SCORE] [int] NULL,
 CONSTRAINT [pk_idReporti_idItemsreportis_CRD] PRIMARY KEY CLUSTERED 
(
	[idReport] ASC,
	[idItemsReport] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[_CommissionreportDetails] ([idReport], [idItemsReport], [studentId], [Note], [EXAM_SCORE]) VALUES (6949, 1, 1317, NULL, 7)
INSERT [dbo].[_CommissionreportDetails] ([idReport], [idItemsReport], [studentId], [Note], [EXAM_SCORE]) VALUES (6974, 1, 1317, NULL, 7)
INSERT [dbo].[_CommissionreportDetails] ([idReport], [idItemsReport], [studentId], [Note], [EXAM_SCORE]) VALUES (7028, 1, 1292, NULL, 8)
INSERT [dbo].[_CommissionreportDetails] ([idReport], [idItemsReport], [studentId], [Note], [EXAM_SCORE]) VALUES (7195, 2, 1293, NULL, 7)
INSERT [dbo].[_CommissionreportDetails] ([idReport], [idItemsReport], [studentId], [Note], [EXAM_SCORE]) VALUES (7429, 3, 1294, NULL, 8)
/****** Object:  ForeignKey [FK_idCurriculum_crc]    Script Date: 02/06/2012 20:48:04 ******/
ALTER TABLE [dbo].[_Commissionreport]  WITH CHECK ADD  CONSTRAINT [FK_idCurriculum_crc] FOREIGN KEY([idCurriculum])
REFERENCES [dbo].[Curriculum] ([idCurriculum])
GO
ALTER TABLE [dbo].[_Commissionreport] CHECK CONSTRAINT [FK_idCurriculum_crc]
GO
/****** Object:  ForeignKey [FK_idCurriculum_ciiss]    Script Date: 02/06/2012 20:48:04 ******/
ALTER TABLE [dbo].[_CommissionreportDetails]  WITH CHECK ADD  CONSTRAINT [FK_idCurriculum_ciiss] FOREIGN KEY([idReport])
REFERENCES [dbo].[CommissionReport] ([idReport])
GO
ALTER TABLE [dbo].[_CommissionreportDetails] CHECK CONSTRAINT [FK_idCurriculum_ciiss]
GO
/****** Object:  ForeignKey [FK_idCurriculum_cic]    Script Date: 02/06/2012 20:48:04 ******/
ALTER TABLE [dbo].[_curriculumDetails]  WITH CHECK ADD  CONSTRAINT [FK_idCurriculum_cic] FOREIGN KEY([idCurriculum])
REFERENCES [dbo].[Curriculum] ([idCurriculum])
GO
ALTER TABLE [dbo].[_curriculumDetails] CHECK CONSTRAINT [FK_idCurriculum_cic]
GO

My problems is represent above on the top.

streetWinner

streetWinner
Starting Member

20 Posts

Posted - 2012-02-06 : 15:15:51

OUTPUT


My JOIN tables problem
______________________________________________
SELECT distinct cr.idReport, 
       cri.idItemsReport,
       cri.StudentId,
         c.idCurriculum, 
         c.idDepartment,
         c.yearOfStudy,
        cr.idExam,
        cr.idProfesor,
        cri.EXAM_SCORE,
      ci.ESPB,
      ci.semester
FROM _Curriculum c
inner join _CommissionReport cr ON cr.idCurriculum=c.idCurriculum
inner join _CommissionreportDetails cri ON cr.idreport=cri.idReport
inner join _CurriculumDetails  ci ON ci.idCurriculum=c.idCurriculum
order by cr.idreport asc


-----------------------------------------------------------------

OUTPUT - result set

idReport	idItemsReport	StudentId	idCurriculum	idDepartment	yearOfStudy	idExam	idProfesor	EXAM_SCORE	ESPB	semester
6949	1	1317	43	15	2009	507	90	7	4.00	1
6949	1	1317	43	15	2009	507	90	7	5.00	1
6949	1	1317	43	15	2009	507	90	7	6.00	1
6949	1	1317	43	15	2009	507	90	7	8.00	1
6974	1	1317	43	15	2009	520	1	7	4.00	1
6974	1	1317	43	15	2009	520	1	7	5.00	1
6974	1	1317	43	15	2009	520	1	7	6.00	1
6974	1	1317	43	15	2009	520	1	7	8.00	1
7028	1	1292	43	15	2009	501	33	8	4.00	1
7028	1	1292	43	15	2009	501	33	8	5.00	1
7028	1	1292	43	15	2009	501	33	8	6.00	1
7028	1	1292	43	15	2009	501	33	8	8.00	1
7195	2	1293	43	15	2009	506	92	7	4.00	1
7195	2	1293	43	15	2009	506	92	7	5.00	1
7195	2	1293	43	15	2009	506	92	7	6.00	1
7195	2	1293	43	15	2009	506	92	7	8.00	1
7429	3	1294	43	15	2009	520	1	8	4.00	1
7429	3	1294	43	15	2009	520	1	8	5.00	1
7429	3	1294	43	15	2009	520	1	8	6.00	1
7429	3	1294	43	15	2009	520	1	8	8.00	1

As you can see, problem is in table _CurriculumDetails, which related to table _curriculum, but not related with table CommissionReport. CommissionReport is related with table Curriculum, but it should be associated with table _curriculumDetails.
That situation giving multiplied and abnormal data.
That is current situation in greater database on which I'm working. My tasks are to export data from database StudentService to new database associated with new system. Because of this situation, I have problem to export data from one to other databse. The current model of database StudentService is not made on the right way.

I hope so to understood my problems?

streetWinner

streetWinner
Starting Member

20 Posts

Posted - 2012-02-06 : 15:18:38

I'm forget tables data


Curriclum
-----------------------------------------------------------
idCurriculum idDepartment yearOfStudy PlanOfStudy
43 15 2009 2009/10 
44 20 2009 2009/10 
45 29 2009 2009/10 
46 30 2009 2009/10 
47 31 2009 2009/10 
__________________________________________________________________

CurriculumItems (details)
-----------------------------------
idCurriculum IdItems idExam Semester ESPB
43 1 520 1 6.00
43 2 501 1 8.00
43 3 506 1 4.00
43 4 507 1 5.00
43 5 521 1 4.00


Commissionreport
--------------------------------------------
6949 43 2010-01-22 507 90
6974 43 2010-02-03 520 1
7028 43 2010-02-01 501 33
7195 43 2010-02-10 506 92
7429 43 2010-04-21 520 1
_________________________________________________


CommissionReportItems(details)
-------------------------------------------
idReport idItemsReport studentId Note EXAM_SCORE
6949 1 1317 NULL 7
6974 1 1317 NULL 7
7028 1 1292 NULL 8
7195 2 1293 NULL 7
7429 3 1294 NULL 8

streetWinner

streetWinner
Starting Member

20 Posts

Posted - 2012-02-06 : 17:39:41

After JOIN tables, result should look like below, unlike the output above showed in two post before.


idReport	idItemsReport	StudentId	idCurriculum	idDepartment	yearOfStudy	idExam	idprofesor	EXAM_SCORE	ESPB	Semseter
6949	1	1317	43	15	2009	507	90	7	5	1
6974	1	1317	43	15	2009	520	1	7	6	1
7028	1	1292	43	15	2009	501	33	8	8	1
7195	2	1293	43	15	2009	506	92	7	4	1
7429	3	1294	43	15	2009	520	1	8	6	1

streetWinner

streetWinner
Starting Member

20 Posts

Posted - 2012-02-09 : 11:53:39

quote:
Originally posted by streetWinner

After JOIN tables, result should look like below, unlike the output above showed in two post before.

idReport idItemsReport StudentId idCurriculum idDepartment yearOfStudy idExam idprofesor EXAM_SCORE ESPB Semseter
6949 1 1317 43 15 2009 507 90 7 5 1
6974 1 1317 43 15 2009 520 1 7 6 1
7028 1 1292 43 15 2009 501 33 8 8 1
7195 2 1293 43 15 2009 506 92 7 4 1
7429 3 1294 43 15 2009 520 1 8 6 1

streetWinner

THIS IS A answer on my question:


SELECT distinct cr.idReport, 
         c.idCurriculum,          
         c.idDepartment,
         c.yearOfStudy,
        cr.idExam,
        cr.idProfesor as idprofesor,
        cri.EXAM_SCORE,
        ci.ESPB,
        ci.semester
FROM CommissionReport cr
inner join _CommissionreportDetails cri ON cr.idreport=cri.idReport
inner join _CurriculumDetails  ci ON ci.idCurriculum=cr.idCurriculum AND ci.idExam=cr.idExam
inner join _Curriculum c ON c.idCurriculum=ci.idCurriculum
order by cr.idreport asc

I found right code!

Thank you all!

streetWinner

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources