I need the opposite of this query/function

[fife]

Hi Guys

 

I have written this function  which basically finds all assets that have a certain document associated with them in a certain location.  It works great however I need the opposite.  I need all the assets that don't have this document but i cant get my head around the query can anyone help me please?

 

function GetUploadedDocsNoAsset($doctype,$archive, $location){ $colname_DocUp = "-1";if (isset($doctype)) {  $colname_DocUp = $doctype;} $colname_Archive = "-1";if (isset($archive)) {  $colname_Archive = $archive;}$colname_Location = "-1";if (isset($location)) {  $colname_Location = $location;} $query_DocUp = sprintf("SELECT idAssetDocStand FROM AssetDocStand INNER JOINAsset ON AssetDocStand.idAsset = Asset.idAssetWHERE AssetDocStand.idDocumentStandards = %s AND AssetDocStand.Archive=%s AND Asset.idLocation = %s",    GetSQLValueString($colname_DocUp, "int"),   GetSQLValueString($colname_Archive, "text"),   GetSQLValueString($colname_Location, "int"));$DocUp = mysql_query($query_DocUp);$totalRows_DocUp = mysql_num_rows($DocUp);return $totalRows_DocUp;}

[jazzman1]

If "idAssetDocStand" is a column from a table named "AssetDocStand" (left table), then you'd use a LEFT JOIN with the where clase where this ID is NULL to retrieve a set of rows not matching rows in the right table (Asset), but I'm not sure about that The better way is to explain your tables structure
 

PS, try next:

$query_DocUp = sprintf("SELECT idAssetDocStand FROM AssetDocStand 

LEFT JOIN

Asset ON AssetDocStand.idAsset = Asset.idAsset

WHERE 

AssetDocStand.idDocumentStandards = %s AND 

AssetDocStand.Archive=%s AND 

Asset.idLocation = %s AND Asset.idAsset is NULL", 

   GetSQLValueString($colname_DocUp, "int"),

   GetSQLValueString($colname_Archive, "text"),

   GetSQLValueString($colname_Location, "int"));

Edited March 20, 2014 by jazzman1

[fife]

Thanks Jazzman1

 

Ok let me explain the structure of the tables in use.

 

so I have an

 

Asset Table

idAsset

AssetName

idLocation

 

Document table

idDocument

DocumentName

 

DocumentStandards

idDocumentStandards

DocumentStandName

 

AssetDocStand

idAsset

idDocument

idDocumentStandards

 

basically when someone uploads a document it has to conform to a standard that's on the system.  I need to be able to check which assets do not currently have a document uploaded that matches a required standard in the current location.

Edited March 20, 2014 by fife

[fife]

Jazzman1 I tried your change but its only giving me all the assets that do have a document uploaded not dont.

[jazzman1]

Give us some sample of data, please. I don't see where "idAssetDocStand" belongs to in your explanation above.

[Psycho]

OK, here is your current query in a more readable format

SELECT idAssetDocStand

FROM AssetDocStand

INNER JOIN Asset
  ON AssetDocStand.idAsset = Asset.idAsset

WHERE AssetDocStand.idDocumentStandards = %s
  AND AssetDocStand.Archive = %s
  AND Asset.idLocation = %s

So, let's think about what records would get included. The WHERE clause is pretty easy. Only the records that match all three of those conditions would be included. So, in the new query you want to have the same three conditions checking where the records do not equal those values and you want to use OR clauses (i.e. field1 <> value1 OR field2 <> field2 OR field3 <> value3).

 

But, there's another facet to consider - the JOIN clause. That JOIN clause would initially filter out any records where the JOIN condition does not exist. So, we want to also ensure that we get the records that were excluded from the JOIN. You might think that we could do the reverse of that JOIN to get the ones that o not match the condition - but that would also exclude records that were part of the original JOIN but excluded because of the WHERE conditions - and you want those. So, I would do a LEFT JOIN using the same condition as before. Then add a new condition to the WHERE clauses to include records where the JOINed values were NULL.

 

Try this

$query = sprintf("SELECT idAssetDocStand
                  FROM AssetDocStand
                  LEFT JOIN Asset
                    ON AssetDocStand.idAsset = Asset.idAsset
                  WHERE Asset.idAsset IS NULL
                     OR AssetDocStand.idDocumentStandards = %s
                     OR AssetDocStand.Archive = %s
                     OR Asset.idLocation = %s",

                 GetSQLValueString($colname_DocUp, "int"),
                 GetSQLValueString($colname_Archive, "text"),
                 GetSQLValueString($colname_Location, "int")
                );

Edited March 20, 2014 by Psycho

[fife]

Sorry matey.

 

right so asset 10 (laptop) in location 5 (head office) needs to conform to the standard with the id of 6 (build directive).  I'm trying to use my AssetDocStand table to say that asset (laptop) has a document uploaded that conforms to the standard of the (build directive).  The field idAssetDocStand is irrelevant.  I just need to pull something back from the AssetDocStand table so I can count how many rows are returned.  We could select * if need be.   Ive reposted the query to represent this.  please just tell me if i'm still not making sense.  i hope I am.

 

AssetDocStand

Laptop, idDocument, Build Directive

 

 

 

function GetUploadedDocsNoAsset($doctype,$archive, $location){ $colname_DocUp = "-1";if (isset($doctype)) {  $colname_DocUp = $doctype;} $colname_Archive = "-1";if (isset($archive)) {  $colname_Archive = $archive;}$colname_Location = "-1";if (isset($location)) {  $colname_Location = $location;} $query_DocUp = sprintf("SELECT AssetDocStand.* FROM AssetDocStand LEFT JOIN Asset ON AssetDocStand.idAsset = Asset.idAsset WHERE AssetDocStand.idDocumentStandards = %s AND AssetDocStand.Archive=%s AND Asset.idLocation = %s", GetSQLValueString($colname_DocUp, "int"),GetSQLValueString($colname_Archive, "text"),GetSQLValueString($colname_Location, "int"));$DocUp = mysql_query($query_DocUp);$totalRows_DocUp = mysql_num_rows($DocUp);return $totalRows_DocUp;} 

[fife]

Hi Psycho

 

That seems to give me a count of rows in the AssetDocStand table where the current asset has a record.  If that's the way I explained it im sorry.  What I really want is all the assets from the current location that don't have a document uploaded that belongs to standard 2 for example

[fife]

Gosh I hope I make sense because I'm confusing myself now

[jazzman1]

 

What I really want is all the assets from the current location that don't have a document uploaded that belongs to standard 2 for example

 

Then, you need to join the "Document table" table as well to your query. That's why I told you to provide some sample of data and what do you want to retrieve from them.

[Psycho]

Hmm, you simply stated you wanted the opposite of the original query. I'm pretty sure what I provided would do that. But, now you state

 

 

What I really want is all the assets from the current location that don't have a document uploaded that belongs to standard 2 for example

That is not the opposite. That part would be the same as your current query. Plus, you state you are wanting "assets" in the result set, but the original query was pulling data from the AssetDocStand table.

 

I think my original query will work with two modifications: 1) Switch the tables so we are starting from the asset table and 2) Make the location condition inclusive. Note I forgot to make the conditions <> in the first one I provided

 

 

$query = sprintf("SELECT idAssetDocStand
                  FROM Asset
                  LEFT JOIN AssetDocStand
                    ON AssetDocStand.idAsset = Asset.idAsset
                  WHERE (AssetDocStand.idAsset IS NULL
                     OR AssetDocStand.idDocumentStandards <> %s
                     OR AssetDocStand.Archive <> %s)
                     AND Asset.idLocation = %s",

                 GetSQLValueString($colname_DocUp, "int"),
                 GetSQLValueString($colname_Archive, "text"),
                 GetSQLValueString($colname_Location, "int")
                );

 

This will include all records from the asset table which:

 - idLocation Matches the $colname_Location value AND

 

 - Do not have any matching records in the AssetDocStand table

 - OR idDocumentStandards does not match $colname_DocUp

 - OR Archive does not match $colname_Archive

[fife]

Guys.  This is still not right.  Let me explain where we are up to. Psycho if I dump your query after I have removed the archive field and directly inputted values I get this

 

Array ( [idAssetDocStand] => ) 
Array ( [idAssetDocStand] => ) 
Array ( [idAssetDocStand] => ) 
Array ( [idAssetDocStand] => ) 
Array ( [idAssetDocStand] => 6 ) 
Array ( [idAssetDocStand] => 10 ) 

 

 

I've now removed irrelevant info from the table and inserted the values directly to make it easier to find what I am after.

 

mysql_select_db($database_dbconnect, $dbconnect);$query_DocUp = "SELECT idAssetDocStand                  FROM Asset                  LEFT JOIN AssetDocStand                    ON AssetDocStand.idAsset = Asset.idAsset                  WHERE (AssetDocStand.idAsset IS NULL                     OR AssetDocStand.idDocumentStandards <> '2')                                         AND Asset.idLocation = '1'";$DocUp = mysql_query($query_DocUp, $dbconnect) or die(mysql_error());$totalRows_DocUp = mysql_num_rows($DocUp);

 

 

I'm just trying to count all assets that don't have a risk assessment in the current location.  In my case it should be 4.  I have 5 assets.  One of them has a row in the AssetDocStand table saying it has a risk assessment,   Another way of saying it is:

 

"I need all the assets in the current location that dont have a record in the AssetDocStand table where the idDocumentStandards = 2 "

 

I think because I didn't fully understand what I was after I've done a poor job of explaining what I need.  That last explanation above is totally accurate

Edited March 21, 2014 by fife

[fife]

Ok.  I have done this which is exactly what we are after if I only want counts but if I want the names of the assets left it no longer works.  Its almost like I need the differences between the 2 arrays.

 

mysql_select_db($database_dbconnect, $dbconnect);$query_Asset = "SELECT idAsset, AssetName FROM Asset WHERE idLocation = '1'";$AssetUp = mysql_query($query_Asset, $dbconnect) or die(mysql_error());$array1 = mysql_fetch_assoc($AssetUp);$totAsset = mysql_num_rows($AssetUp); mysql_select_db($database_dbconnect, $dbconnect);$query_DocUp = "SELECT AssetDocStand.idAsset FROM AssetDocStand INNER JOIN Asset ON AssetDocStand.idAsset = Asset.idAssetWHERE AssetDocStand.idDocumentStandards = '2' AND Asset.idLocation ='1'";$DocUp = mysql_query($query_DocUp, $dbconnect) or die(mysql_error());$array2 = mysql_fetch_assoc($DocUp);$totStand = mysql_num_rows($DocUp);  $tot = $totAsset - $totStand;echo $tot;

Edited March 21, 2014 by fife

[fife]

Infact this allows me to change the question almost.  No From this way I have these 2 arrays

 

Array ( [idAsset] => 10000005 [AssetName] => HP ) 
Array ( [idAsset] => 10000006 [AssetName] => HP Server ) 
Array ( [idAsset] => 10000009 [AssetName] => HP Laptop ) 
Array ( [idAsset] => 10000010 [AssetName] => Office Printer ) 
Array ( [idAsset] => 10000023 [AssetName] => test ) 



Array ( [idAsset] => 10000023 [AssetName] => test ) 

 

 

Is it possible to find the difference only and iterate through them?