Creating a view joining two tables
-
Help!!!! I'm starting to feel pretty stupid.
I'm trying to create a view in SQL Server 2000 using Enterprise Manager. When I have just one table everything is wonderful. As soon as I add the second table, I get duplicate rows (many, many). I don't know if I left my brain in some other town or what, but I can't figure out how to eliminate the duplicates.
I know this is basic stuff, but I can't get it to work. Does anyone know the answer?
Thanks,
Debbie
-
It will be something to do with the structure of the tables and the way that you have joined them - there will probably be a many-to-many relationship somewhere that is causing all the rubbish. Can you post a bit more detail - eg the SQL for the dodgy view & some sample results?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Did you specify a join condition in the view??
-
If you post the SQL statement on here it should be quite easy to sort out, it sounds like you're joining the two tables but not specifying how they are joining and without that it will return all permutations of the two tables resulting in lots of records being returned.
-
Try using QA for the View. Granted this is not a GUI but is a better place to perform development. This way you can specify INNER JOIN, LEFT|RIGHT JOIN etc..
Good Hunting!AJ Ahrens
webmaster@kritter.net -
This sucks. I just typed everything in and it disappeared. I'll try again.
SELECT DISTINCT
dbo.PRTIME.COMPANY AS PRTIME_Company,
dbo.PRTIME.EMPLOYEE AS PRTIME_Emp,
dbo.PRTIME.CHECK_ID AS PRTIME_CheckId,
dbo.PRTIME.PAY_SUM_GRP AS PRTIME_PaySumGrp, dbo.PRTIME.TIME_SEQ AS
PRTIME_TimeSeq,
dbo.PRTIME.JOB_CODE AS PRTIME_JobCode,
dbo.PRTIME.HOURS AS PRTIME_Hours, dbo.PRTIME.DEPARTMENT AS PRTIME_Dept,
dbo.PRTIME.DST_ACCT_UNIT AS PRTIME_AcctUnit,
dbo.PRTIME.DST_ACCOUNT AS PRTIME_Acct, dbo.PRTIME.TR_DATE AS PRTIME_TrDate,
dbo.PRTIME.PAYROLL_YEAR AS PRTIME_PayrollYear,
dbo.PRTIME.QUARTER AS PRTIME_Qtr, dbo.PRTIME.PER_END_DATE AS
PRTIME_PerEndDate,
dbo.PRTIME.CHECK_TYPE AS PRTIME_CheckType,
dbo.PRTIME.DATE_STAMP AS PRTIME_DateStamp,
dbo.EMPLOYEE.COMPANY AS EMPLOYEE_Company,
dbo.EMPLOYEE.EMPLOYEE AS EMPLOYEE_Emp,
dbo.EMPLOYEE.LAST_NAME AS EMPLOYEE_LastName,
dbo.EMPLOYEE.FIRST_NAME AS EMPLOYEE_FirstName,
dbo.EMPLOYEE.MIDDLE_INIT AS EMPLOYEE_MiddleInit,
dbo.EMPLOYEE.ADJ_HIRE_DATE AS EMPLOYEE_AdjHireDate,
dbo.PAYSUMGRP.COMPANY AS PAYSUMGRP_Company,
dbo.PAYSUMGRP.PAY_SUM_GRP AS PAYSUMGRP_PaySumGrp,
dbo.PAYSUMGRP.DESCRIPTION AS PAYSUMGRP_Desc
FROM dbo.PRTIME INNER JOIN
dbo.EMPLOYEE ON dbo.PRTIME.COMPANY = dbo.EMPLOYEE.COMPANY INNER JOIN
dbo.PAYSUMGRP ON dbo.PRTIME.COMPANY = dbo.PAYSUMGRP.COMPANY
WHERE (dbo.PRTIME.PAYROLL_YEAR = 2005) AND (dbo.PRTIME.PER_END_DATE =
CONVERT(DATETIME, '2005-03-12 00:00:00', 102))
ORDER BY dbo.PRTIME.PER_END_DATE, dbo.PRTIME.EMPLOYEE, dbo.PRTIME.TR_DATE
EXPLANATIONS ABOUT MY DATA:
1) PRTIME has multiple rows for each employee. EMPLOYEE has one row for each employee. PAYSUMGRP has multiple rows for each pay summary group.
2) In PRTIME each employee would have at most 12-14 rows as it is a record for each TrDate (transaction date). An employee in PRTIME may have more than one pay summary group. The pay summary group can be Regular Pay, Overtime, Sick, etc.
PRTIME PRTIME PRTIME PRTIME EMPLOYEE PAYSUMGRP PAYSUMGRP
Emp PaySumGrp Hours TrDate Emp 100 Regular Pay
2014 100 8 2/28/2005 2014 100 Regular Pay
2014 100 8 2/28/2005 3012 100 Regular Pay
2014 100 8 2/28/2005 3020 100 Regular Pay
2014 100 8 2/28/2005 3053 100 Regular Pay
It goes on this way for rows and rows and rows.....
What I would be expecting to see is a different transaction date with the same employee number in PRTIME as in EMPLOYEE. Also, PAYSUMGRP would also be changing, but it doesn't.
Thanks,
Debbie
-
Hi,
I just had a quick look at this, but the problem seems to be in your join conditions. You are joining the tables on COMPANY only, and from what you've explained it seems like you need to use the EMPLOYEE field as a join condition as well.
Hope this helps.
Martin.
-
First of all - if you have a long message, I suggest that you type it in Notepad or Word first, to avoid the time-out that you experienced (we've all done it!).
Second, I think Martin is right. Relationship 1 looks straightforward:
Employee.Employee to Prtime.Employee
Which will result in multiple lines per employee (1 for every record on Prtime, as you would expect).
Relationship 2 is less clear. How do you define which pay sum groups an employee is in? Is this by company (the only possibility I can see at the moment). You state that this is also a one to many relationship (an employee can have several groups) - and this will continue to manifest itself in the form of duplicate lines (which DISTINCT should get rid of). But if this is the case, try changing your FROM condition to this:
FROM dbo.PRTIME INNER JOIN
dbo.EMPLOYEE ON dbo.PRTIME.Employee = dbo.EMPLOYEE.Employee INNER JOIN
dbo.PAYSUMGRP ON dbo.Employee.COMPANY = dbo.PAYSUMGRP.COMPANY
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply