November 9, 2011 at 3:03 pm
If have 6 tables.
tblPRTS is my master table which has 1 to many relationships to 5 other tables.
To test my join statemnet I created the following query to retrieve the key field from each table. based on a single value in tblPRTS.
Although I am able to retrieve the key column from each table, the number of records is multiplied. I get 20 records instead of 5 which is the maximum number of records in tblPRTSBene.
select a.dtimesheetid,b.dtimesheetid,c.dtimesheetid, d.dtimesheetid,e.dtimesheetid,f.dtimesheetid
from tblPRTS as a
join tblPRTSEarn as b on a.dTimesheetID = b.dTimesheetID
join tblPRTSDeduct as c on a.dTimesheetID = c.dtimesheetid
join tblPRTSBene as d on a.dTimesheetID = d.dtimesheetid
join tblPRTSLeave as e on a.dTimesheetID = e.dtimesheetid
join tblPRTSWC as f on a.dTimesheetID = f.dtimesheetid
where a.dTimesheetID = 40827.651917091
November 9, 2011 at 3:42 pm
You probably have more than 1 record for each ID. What are the counts in each of those tables for that ID?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 9, 2011 at 5:41 pm
Sean,
you are correct some of the tables have a multiple records.
Does the query syntax correct for joining multiple tables.
select a.dtimesheetid,b.dtimesheetid,c.dtimesheetid, d.dtimesheetid,e.dtimesheetid,f.dtimesheetid
from tblPRTS as a (1 Record - Master Record)
join tblPRTSEarn as b on a.dTimesheetID = b.dTimesheetID (1 Record - but can be 1 or many)
join tblPRTSDeduct as c on a.dTimesheetID = c.dtimesheetid (2 Records - but can be 1 or many)
join tblPRTSBene as d on a.dTimesheetID = d.dtimesheetid (5 Records - but can be 1 or many)
join tblPRTSLeave as e on a.dTimesheetID = e.dtimesheetid(2 Records - but can be 1 or many)
join tblPRTSWC as f on a.dTimesheetID = f.dtimesheetid (1 Records - but can be 1 or many)
where a.dTimesheetID = 40827.651917091 and a.sTimesheetType = 'D'
November 9, 2011 at 10:11 pm
The query is correct for joining multiple tables. As you have multiple records in the child tables, the result set eventually will have multiple records.
try putting a DISTINCT in the query.
November 9, 2011 at 10:15 pm
Also i see there is no need for the selecting all id column of all the child tables:
select a.dtimesheetid,b.dtimesheetid,c.dtimesheetid, d.dtimesheetid,e.dtimesheetid,f.dtimesheetid
since a.dtimesheetid = b.dtimesheetid, both are same in the output.
Also, a.dtimesheetid = c.dtimesheetid, then both are same.
So, instead of writing
select a.dtimesheetid,b.dtimesheetid,c.dtimesheetid, d.dtimesheetid,e.dtimesheetid,f.dtimesheetid
you can write like
select DISTINCT a.dtimesheetid
FROM
......
Hope this helped you.
November 10, 2011 at 10:51 am
nfpacct (11/9/2011)
Sean,you are correct some of the tables have a multiple records.
Does the query syntax correct for joining multiple tables.
select a.dtimesheetid,b.dtimesheetid,c.dtimesheetid, d.dtimesheetid,e.dtimesheetid,f.dtimesheetid
from tblPRTS as a (1 Record - Master Record)
join tblPRTSEarn as b on a.dTimesheetID = b.dTimesheetID (1 Record - but can be 1 or many)
join tblPRTSDeduct as c on a.dTimesheetID = c.dtimesheetid (2 Records - but can be 1 or many)
join tblPRTSBene as d on a.dTimesheetID = d.dtimesheetid (5 Records - but can be 1 or many)
join tblPRTSLeave as e on a.dTimesheetID = e.dtimesheetid(2 Records - but can be 1 or many)
join tblPRTSWC as f on a.dTimesheetID = f.dtimesheetid (1 Records - but can be 1 or many)
where a.dTimesheetID = 40827.651917091 and a.sTimesheetType = 'D'
Sure, the query is syntactically correct, but logically not what you are looking for. Notice it will join the IDs where they match for each subordinate table. With 2 rows in tblPRTSDeduct and 5 in tblPRTSBene, there will be 10 combinations. The total number of combinations for your joins will be 1x2x5x2x1=20 in this example. If you want a listing of all subordinate rows that match the one master row, use unions to select from each of the subordinate tables separately.
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply