Problem Selecting from Hierarchy and Link Table

1. Junior Member
Join Date
Dec 1969
Posts
15

## Problem Selecting from Hierarchy and Link Table

Hi - I&#039;m having a problem writing the sql to do a join between a hierarchical table and a link table, for example:<BR><BR><BR>Zone Table<BR>***********<BR><BR>Zone_ID Zone_Name<BR><BR>1 &#124 UK<BR>2 &#124 Yorkshire<BR>3 &#124 Buckinghamshire<BR>4 &#124 London<BR>5 &#124 Manchester<BR>6 &#124 USA<BR>7 &#124 Washington<BR>8 &#124 Florida<BR>9 &#124 Idaho<BR><BR><BR><BR>Zone_Hierarchy Table<BR>********************<BR><BR>Zone_ID Subzone_ID<BR><BR>1 &#124 2<BR>1 &#124 3<BR>1 &#124 4<BR>1 &#124 5<BR>6 &#124 7<BR>6 &#124 8<BR>6 &#124 9<BR><BR><BR><BR>How would I go about writing a sql statement that retrieves the data like this:<BR><BR>Parent_Zone_Id Parent_Zone Subzone_Id Subzone<BR><BR>1 UK 2 Yorkshire<BR>1 UK 3 Buckinghamshire<BR>1 UK 4 London<BR>1 UK 5 Manchester<BR>1 USA 7 Washington<BR>1 USA 8 Florida<BR>1 USA 9 Idaho<BR><BR><BR>Hope this makes it clear - thanks for any help :)<BR><BR>James

2. Junior Member
Join Date
Dec 1969
Posts
3

## RE: Problem Selecting from Hierarchy and Link Tabl

Try the following query:<BR><BR>Select PZ.Zone_ID As Parent_Zone_ID, PZ.Zone_Name As Parent_Zone, CZ.Zone_ID As Subzone_ID, CZ.Zone_Name As Subzone<BR>From Zone_Hierarchy H<BR>Inner Join Zone PZ<BR>On H.Zone_ID = PZ.Zone_ID<BR>Inner Join Zone CZ<BR>On H.Subzone_ID = CZ.Zone_ID<BR><BR>Please do let me know if it does not work.<BR><BR>Take care...<BR>Prashant

3. Senior Member
Join Date
Dec 1969
Posts
96,118

## Access *might* complain about that...

...since there are no parens around the first join. Ehhh...probably not, since both are inner joins. <BR><BR>But you could certainly play it safe and use an "implicit join" instead:<BR><BR>Select PZ.Zone_ID As Parent_Zone_ID, PZ.Zone_Name As Parent_Zone, <BR> CZ.Zone_ID As Subzone_ID, CZ.Zone_Name As Subzone<BR>From Zone AS PZ, Zone_Hierarchy AS H, Zone AS CZ<BR>WHERE H.Zone_ID = PZ.Zone_ID<BR>AND CZ.Zone_ID = H.Subzone_ID<BR>ORDER BY PZ.Zone_ID, CZ.Zone_ID<BR><BR>[Prashant left off the ORDER BY, and without it there&#039;s no guarantee of what order you&#039;ll get.]<BR><BR>If you didn&#039;t tumble to it, Prashant used "PZ" to mean "Parent Zone" and "CZ" to mean "Child Zone". Nice way of aliasing the table so it&#039;s all very readable.<BR><BR>

4. Junior Member
Join Date
Dec 1969
Posts
15

## RE: Problem Selecting from Hierarchy and Link Tabl

&nbsp;<BR>works great - thanks prashant :)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•