Jump to content

Multiple Nested queries - problem


DKrzemien

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
Link to comment
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.
Link to comment
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.

Link to comment
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.
Link to comment
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

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...