[xquery-talk] Help required for XQuery - nested inner joins

[Amy Gordon]

Hi everyone!

 

I'm trying to convert a rather complex SQL query into XQuery format (the
relational database is currently being viewed as an XML file). However
the solution that I've come up with doesn't seem to take into account
the nature of the inner joins in the SQL query since I've got too many
result entries in the output file.

 

Here's the SQL query I'm trying to convert:

 

SELECT     ACTIVITIES_ITS_VESSEL.Activity_Number,
VESSELSDATASHEET.Equipment_No, VESSELSDATASHEET.Function_Loc, 

           SCHEDULE_ITS_VESSEL.DateScheduled,
ACTIVITIES_ITS_VESSEL.Inspection_Task

 

FROM         ACTIVITIES_ITS_VESSEL INNER JOIN

             VESSELSDATASHEET ON
ACTIVITIES_ITS_VESSEL.Vessel_Equipment_ID =
VESSELSDATASHEET.Vessel_Equipment_ID 

             INNER JOIN

            SCHEDULE_ITS_VESSEL ON
ACTIVITIES_ITS_VESSEL.Vessel_Equipment_ID =
SCHEDULE_ITS_VESSEL.Vessel_Equipment_ID AND 

             ACTIVITIES_ITS_VESSEL.Activity_Number =
SCHEDULE_ITS_VESSEL.Activity_Number

 

 

And here's my current code (that's giving more results than the SQL
query):

 

xquery version "1.0";

<query>

{

 

for $act in doc("test4.xml")//ACTIVITIES_ITS_VESSEL,

     $vds in doc("test4.xml")//VESSELSDATASHEET,

     $sch in doc("test4.xml")//SCHEDULE_ITS_VESSEL

     

where 

   $act/Vessel_Equipment_ID = $vds/Vessel_Equipment_ID

            and  $act/Vessel_Equipment_ID = $sch/Vessel_Equipment_ID

            and  $act/Activity_Number = $sch/Activity_Number 

            

            return

            <record>

            {$act/Activity_Number}

            {$vds/Equipment_No}

            {$vds/Function_Loc}

            {$sch/DateScheduled}

            {$act/Inspection_Task}

            </record>

}

</query>

 

I somehow need to get the inner joins in there but have no idea how.

 

Any prompt help would be much appreciated.

Thanks

Amy

 

  

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://xquery.com/pipermail/talk/attachments/20050825/c0f275fe/attachment.htm