Jump to content
DKrzemien

Multiple Nested queries - problem

Recommended Posts

Greetings, I've used sql to a moderate degree within vba and some third-party software (i.e. ReportWriter) and for the most part I do ok. I currently have a report I am trying to generate with an underlying sql query and I just can't get it to work. I was able to mimic what I needed in MS Access but there are some syntax and formatting that I have not used before. I've looked through the examples but I'm missing something. The report will displays radio assets that have Not been assigned a workorder ticket for a current project that is underway. Particulars:- ProjectID=27 if the asset has/had a workorder created for the project. NULL otherwise.- CategoryID=21, 22 or 24 if the radio asset can be assigned the ProjectID (21=Portable Radio, 22=Mobile Radio, 24=Control Station) * I've created a report that shows all the assets that have been assigned the ProjectID=27 which was easy enough but I have to be able to query a view table that has both Current and History records - with that said, you can possibly have the same AssetID multiple times. What I've tried to do is perform a query that will1. Pull All AssetID's with CategoryID in(21,22,24) from ASSET_V table.2. Left Join the Asset_V table (assetid) with WorkOrderAll_V table (assetid) / Where ProjectID=27 AND with CategoryID in(21,22,24)3. Left Join both (1 & 2) Where WorkOrderAll_V.assetid Is Null. I have two tables with the fields below: Asset_V-----------asset_v.assetidasset_v.serialnumberasset_v.categoryasset_v.agencygroupasset_v.agencynameasset_v.agencyidasset_v.categoryid ServiceCodeIDasset_v.assignedtoasset_v.vehicleidasset_v.aglocationasset_v.descriptionasset_v.modelnumberasset_v.statusid assetStatusIDasset_v.status WorkOrderAll_V--------------------workorderall_v.assetidworkorderall_v.projectidworkorderall_v.statusidworkorderall_v.status woStatusworkorderall_v.wotype

 SELECT DISTINCTROWasset_v.assetid,asset_v.serialnumber,asset_v.category,asset_v.agencygroup,asset_v.agencyname,asset_v.agencyid,asset_v.categoryid   ServiceCodeID,asset_v.assignedto,asset_v.vehicleid,asset_v.aglocation,asset_v.description,asset_v.modelnumber,asset_v.statusid   assetStatusID,asset_v.status,r1.projectid,r1.statusid,r1.status woStatus,r1.wotypeFROM  (asset_vLEFTJOIN  (SELECT DISTINCTROW		 a1.ASSETID,		 a1.CATEGORYID ServiceCodeID		 FROM asset_v as a1)		 )LEFTJOIN  (SELECT		 r1.assetid,		 r1.projectid		 FROM workorderall_v as r1)		ON  a1.assetid = r1.assetid		WHERE (((a1.CATEGORYID) In (21,22,24)) AND ((r1.projectid)=27)) WHERE  (((a1.ServiceCodeID) in (21,22,24)) AND ((r1.assetid) IS NULL)) 

Edited by DKrzemien

Share this post


Link to post
Share on other sites
What records does that return, what needs to change?
Hello, it is Supposed to return All records that have never had a ProjectID = 27. I cannot get it to work. I was hoping another set of eyes might see some problem with the code.

Share this post


Link to post
Share on other sites

You didn't list an assetid column for the work order table, but if that has the assetid that refers to the assetid in the other table then you probably need to add a condition similar to this: asset_v.assetid NOT IN (SELECT assetid FROM workorderall_v where projectid=27) That subquery will find everything with a project ID of 27, and then you want to find assets that aren't in that list.

Share this post


Link to post
Share on other sites
You didn't list an assetid column for the work order table, but if that has the assetid that refers to the assetid in the other table then you probably need to add a condition similar to this: asset_v.assetid NOT IN (SELECT assetid FROM workorderall_v where projectid=27) That subquery will find everything with a project ID of 27, and then you want to find assets that aren't in that list.
I have it as r1.assetid above. I was trying to use alias' because of the multiple use of the asset_v table. I've not nested queries before so I'm not quite sure of the accuracy of the syntax.

Share this post


Link to post
Share on other sites

I have resolved my issues with the code below:

 SELECT DISTINCTA1.assetid,A1.agencygroup,A1.agencyname,A1.agencyid,A1.categoryid ServiceCodeID,A1.assignedto,A1.vehicleid,A1.aglocation,A1.description,A1.modelnumber,A1.statusid assetStatusID,A1.status,Rebanded.projectid,Rebanded.statusid,Rebanded.status woStatus,Rebanded.wotype FROM mcm."asset_v" A1 LEFT JOIN (SELECTA2.assetid,A2.agencygroup,A2.agencyname,A2.agencyid,A2.categoryid ServiceCodeID,A2.assignedto,A2.vehicleid,A2.aglocation,A2.description,A2.modelnumber,A2.statusid assetStatusID,A2.status,W1.projectid,W1.statusid,W1.status woStatus,W1.wotype FROM mcm."asset_v" A2 LEFT JOIN mcm."workorderall_v" W1 ON A2.assetid = W1.assetid WHERE (((A2.categoryid)IN (21,22,24)) AND ((W1.projectid)= 27))) AS Rebanded ON A1.assetid = Rebanded.assetid WHERE (((Rebanded.assetid) IS NULL) AND ((A1.categoryid) IN (21,22,24)))ORDER BY A1.agencygroup, A1.agencyname, A1.agencygroup; 

The query returns All records that have not been assigned a ProjectID=27

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

×
×
  • Create New...