September 4, 2008 at 10:46 am
At the moment I am trying to do a multi-table query. The problem I am having is apparently within the 4th table in the query. The 4th table, depend_demo, only has 3 records in it. After I finish this query it is showing 5 records. What is happened is it is listing multiple times under the same employee, when they are a dependent and should only be listing once depending on what kind of payroll_id they are under.
Here is the query.
select distinct a.emp_ssno, d.emp_lname, d.emp_fname, d.emp_mi, d.emp_dob, d.emp_add1,
d.emp_add2, d.emp_city, d.emp_state, d.emp_zip, e.deduc_payroll_id,
e.deduc_no_of_deduc, e.deduc_tax_status, e.deduc_amt_per_deduc, e.deduc_cov_conf_timestamp,
f.depend_lname, f.depend_fname, f.depend_dob, f.depend_relat
from emp_id a LEFT JOIN emp_demo d ON a.pgb_id = d.pgb_id
and a.client_name = 'COUNTYNAME'
LEFT JOIN emp_deduc e ON a.pgb_id = e.pgb_id
LEFT JOIN depend_demo f ON a.pgb_id = f.pgb_id
PGB_ID is a foreign in the last 3 tables, with emp_id being the parent table.
All the information I need is working fine except when it gets to the 4th table which is when those few pieces of data appear in places they shouldn't.
Can anyone give me any pointers on this.
September 4, 2008 at 11:38 am
When you get the multiple records, do the values for the columns at the end of your query (f.depend_lname, f.depend_fname, f.depend_dob, f.depend_relat) change for each row? It sounds like you have multiple records in depend_demo for a particular pgb_id.
Thanks,
Chad
September 4, 2008 at 11:51 am
The problem is actually in the emp_deduc table. There are multiple deductions for each employee.
I can query the depend_demo and emp_demo with no issues or unknown situations.
When I try to query the emp_deduc table and the depend_demo table it starts giving me information in columns there shouldn't be.
for example:
Say an employee has 3 benefits, 2 benefits are from a dependent (John Doe). So when I do the query it should show the employee 3 times, with all 3 benefits, with 1 dependent column being NULL and the other 2 with John Doe listed.
What is happening is John Doe is listed for all 3 when he only has 2 benefits.
How can I get the benefit that doesn't include him to be NULL like it supposed to instead of just adding his name for no reason at all.
September 4, 2008 at 12:01 pm
What would really help here is the DDL (create statements) for the tables, sample data for each of the tables (as insert statementws that can be cut, paste, and run in SSMS to load the tables), expected output based on the sample data. We already have your code, so we can see what it is currntly doing once we have the tables and sample data.
😎
September 4, 2008 at 12:07 pm
CREATE TABLE [dbo].[emp_id]
(
[pgb_id] [int] IDENTITY(1,1) NOT NULL,
[emp_ssno] [varchar](11),
[emp_no] [varchar](20),
[client_id] [varchar](50),
[client_name] [varchar](50),
PRIMARY KEY CLUSTERED
(
[Pgb_id] 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
CREATE TABLE [dbo].[emp_demo](
[demo_id] [int] IDENTITY(1,1) NOT NULL,
[pgb_id] [int] NULL,
[emp_fname] [varchar](25) NULL,
[emp_lname] [varchar](25) NULL,
[emp_mi] [varchar](5) NULL,
[emp_suffix] [varchar](25) NULL,
[emp_add1] [varchar](50) NULL,
[emp_add2] [varchar](50) NULL,
[emp_city] [varchar](25) NULL,
[emp_state] [varchar](2) NULL,
[emp_zip] [varchar](25) NULL,
[emp_home_phone] [varchar](20) NULL,
[emp_dob] [varchar](20) NULL,
[emp_doh] [varchar](20) NULL,
[emp_gender] [varchar](10) NULL,
[emp_work_phone] [varchar](50) NULL,
[emp_email] [varchar](50) NULL,
[emp_pays] [varchar](50) NULL,
[emp_contract_length] [varchar](50) NULL,
PRIMARY KEY CLUSTERED
(
[demo_id] 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
ALTER TABLE [dbo].[emp_demo] WITH CHECK ADD CONSTRAINT [FK_emp_demo] FOREIGN KEY([pgb_id])
REFERENCES [dbo].[emp_id] ([Pgb_id])
GO
ALTER TABLE [dbo].[emp_demo] CHECK CONSTRAINT [FK_emp_demo]
CREATE TABLE [dbo].[emp_deduc](
[deduc_id] [int] IDENTITY(1,1) NOT NULL,
[pgb_id] [int] NOT NULL,
[deduc_payroll_id] [varchar](25) NULL,
[deduc_amt_per_deduc] [varchar](25) NULL,
[deduc_no_of_deduc] [varchar](25) NULL,
[deduc_tax_status] [varchar](25) NULL,
[deduc_cov_conf_timestamp] [varchar](25) NULL,
CONSTRAINT [PK_emp_deduc] PRIMARY KEY CLUSTERED
(
[deduc_id] 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
ALTER TABLE [dbo].[emp_deduc] WITH CHECK ADD CONSTRAINT [FK_emp_deduc] FOREIGN KEY([pgb_id])
REFERENCES [dbo].[emp_id] ([pgb_id])
GO
ALTER TABLE [dbo].[emp_deduc] CHECK CONSTRAINT [FK_emp_deduc]
CREATE TABLE [dbo].[depend_demo]
(
[pgb_id] [int] NOT NULL,
[depend_id] [int] IDENTITY(1,1) NOT NULL,
[depend_ssno] [varchar](11) NULL,
[depend_lname] [varchar](25) NULL,
[depend_fname] [varchar](25) NULL,
[depend_mi] [varchar](25) NULL,
[depend_relat] [varchar](25) NULL,
[depend_gender] [varchar](10) NULL,
[depend_dob] [varchar](25) NULL,
[depend_add1] [varchar](50) NULL,
[depend_add2] [varchar](50) NULL,
[depend_city] [varchar](50) NULL,
[depend_state] [varchar](2) NULL,
[depend_zip] [varchar](15) NULL,
[depend_school] [varchar](50) NULL,
PRIMARY KEY CLUSTERED
(
[depend_id] 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
ALTER TABLE [dbo].[depend_demo] WITH CHECK ADD CONSTRAINT [FK_depend_demo] FOREIGN KEY([pgb_id])
REFERENCES [dbo].[emp_id] ([Pgb_id])
GO
ALTER TABLE [dbo].[depend_demo] CHECK CONSTRAINT [FK_depend_demo]
September 4, 2008 at 12:38 pm
You're 1/3 of the way there. Still need sample data and expected results based on the sample data.
We can at least start looking at the query, but we need something to test and check against.
😎
September 4, 2008 at 12:54 pm
Can you tell me how you know when a particular record in emp_deduc is for a dependant and when it is for the employee? It doesn't look like there is anything in that table to distinguish between deductions for an employee and deductions for a dependant. Is that correct? If so, you need to change your model in order to get it to work. The simplest (but definately not "normalized") way given where you are at right now would be to add a depend_id to the emp_deduc table and have it be null if the deduction is for the employee. There are a few other options too, depending on if the assumption above is correct or not and if you are able to make some significant changes to normalize the model.
Chad
September 4, 2008 at 1:04 pm
The information I am getting are from excel files sent to us by other counties so there really isn't a good way for me to add in a column that would allow me to better distinguish between the two. There is a dependent table as you can see that has them listed correctly but I still think I should be able to query them as is and not get replicate data, I just simply haven't figured out how to do it.
I really can't give any good examples of what I am faced with however due to privacy issues. To give a mediocre example would take a quite a bit of time.
But to answer the question Chad, there really isn't a "for certain" way to tell the difference than actually looking at the excel file itself.
September 4, 2008 at 1:29 pm
Help me out here... is this a decent mock up of the tables in question (some columns removed)?
emp_id
pgb_idName
1Chad
2Frank
3Joe
emp_deduc
pdb_iddeduc_amt_per_deduc
110%
120%
130%
25%
310%
depend_demo
pdb_idName
1Jack
1Sam
2Tealc
2Daniel
Based on this information, there is no way to tell if the 10% deduction for me (I'm Chad) is from myself, "Jack" or "Sam". In fact, the three deductions could all be for me or all for one of my dependants - the data you need to tell the difference does not exist in the database, so no query will be able to return it. You have to add additional information to the tables in some way (there are several options depending on how much you can shake up the model) in order to be able to run the query you want.
I'm hoping you reply with "oh - you misunderstand this column" or "there is another table that does this", in which case we just need to look at it again with the new info you provide.
Chad
September 4, 2008 at 2:11 pm
After looking over everything I had again you may be correct. There are separate columns for it to an extent in the excel files but nothing concrete enough to possibly run a query on it.
What I am going to do is redesign the database a small amount to try to accommodate for this problem in the future. Possibly make a seperate table for dependent deductions if I have the information for it.
Thanks for all your help.
September 4, 2008 at 2:22 pm
Good Luck - one last thought before I go. I'm going to go way out here on a guess. Does your Excel sheet have one line for each deduction, with columns for employee, deduction information and (sometimes) dependant information? If so, you model might look similar to what you have except each dependant would have it's own ID (not the employee's ID) and there would be a table between the deduction and the dependant like this:
emp_deduc
pdb_id deductionIDdeduc_amt_per_deduc
1 110%
1 220%
1 330%
2 45%
3 510%
emp_dep_ded
deductioniddependantID
1100
3101
4102
5103
depend_demo
dependantID Name
100 Jack
101 Sam
102 Tealc
103 Daniel
in this case, you can see (and query) that the 10% deduction is from the dependant Jack, the 20% deduction is from the employee (no record in emp_dep_ded) and the 30% deduction is for the dependant Sam.
Anyway - best wishes.
Chad
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply