Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Join Multiple Tables Expand / Collapse
Author
Message
Posted Wednesday, November 9, 2011 3:03 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 18, 2014 3:01 PM
Points: 38, Visits: 176
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
Post #1203207
Posted Wednesday, November 9, 2011 3:42 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:50 AM
Points: 13,082, Visits: 12,547
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1203210
Posted Wednesday, November 9, 2011 5:41 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 18, 2014 3:01 PM
Points: 38, Visits: 176
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'
Post #1203221
Posted Wednesday, November 9, 2011 10:11 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, April 28, 2013 1:54 AM
Points: 37, Visits: 80
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.
Post #1203251
Posted Wednesday, November 9, 2011 10:15 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, April 28, 2013 1:54 AM
Points: 37, Visits: 80
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.
Post #1203253
Posted Thursday, November 10, 2011 10:51 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 2:16 PM
Points: 477, Visits: 3,673
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.
Post #1203732
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse