[SOLVED] Query help ?

[lordphate]

Hi Everyone

 

I'm having an issue where I am trying to retrieve only profiles that have not been approved. I cannot edit the DB, so I cannot rework that portion at all (otherwise i would have rebuilt the DB to begin with).

 

The table is formed like:

ContactID (int)

FieldID (int)

Value (string)

DateUpdated (timestamp)

 

-----

 

Here is my query:

"SELECT * FROM ContactCustomProfileField WHERE ContactID NOT EXISTS (SELECT ContactID FROM ContactCustomProfileField WHERE FieldID=4964744) AND FieldID=4953960 AND Value LIKE '%' ORDER BY DateUpdated DESC";

 

I also tried:

 

SELECT * FROM ContactCustomProfileField

WHERE ContactID NOT IN (SELECT ContactID FROM ContactCustomProfileField WHERE FieldID=4964744) AND FieldID=4953960 AND Value LIKE '%' ORDER BY DateUpdated DESC

 

FieldID 4964744 is the "Approved" field. It is only inserted during the approval process. Before the approval process that field (for that particular ContactID) is not in the database. Once approved or denied, a insertion is made that makes FieldID 4964744 for the ContactID with the Value of 'Approved' or 'Denied'

 

 

Any Ideas ?

 

Thanks!

 

[MadTechie]

I'm sorry but I don't understand the logic,

 

can you post some sample record entries, which are UnApproved, Approved, Denied..

 

PS i am going to move this to the MySQL Topic

[lordphate]

I do not have sample records, I have NO access to the database. This is all through an API (which is out of my control as well).

  $approvedCondition = array(
  'QueryText' => "SELECT * FROM ContactCustomProfileField WHERE ContactID NOT IN (SELECT ContactID FROM ContactCustomProfileField WHERE FieldID=4964744)",
  'PageSize' => 1,
  'PageNumber' => 1
  );
  $approvedRequest = new soapval('request', 'QueryRequest', $approvedCondition, false, 'tns');
  $approvedParam = array('request' => $approvedRequest);
  $approvedResponse = $client->call('Query', array('parameters' => $approvedParam));
  $approvedResult = $approvedResponse['QueryResult'];
    foreach ($approvedResult[Records][Record] as $approved){
  $republicCondition = array(
  'QueryTexxt' => "SELECT * FROM ContactCustomProfileField WHERE ContactID={$approved} AND FieldID=4953960 AND Value LIKE '%' ORDER BY DateUpdated DESC",
  'PageSize' => 20,
  'PageNumber' => $_GET["page"]+1
  );
  $republicRequest = new soapval('request', 'QueryRequest', $republicCondition, false, 'tns');
  $republicParam = array('request' => $republicRequest);
  $republicResponse = $client->call('Query', array('parameters' => $republicParam));
  $republicResult = $republicResponse[QueryResult][Records][Record];
  print_r($republicResponse);exit;
  foreach($republicResult as $republican) {
  //---Get The Profile---//
       $profileCondition = array(
       'QueryText' => "SELECT * FROM ContactProfile WHERE ContactID=$republican[ContactID]",
       'PageSize' => 100,
       'PageNumber' => 1
       );
       $profileRequest = new soapval('request', 'QueryRequest', $profileCondition, false, 'tns');
       $profileParam = array('request' => $profileRequest);
       $profileResponse = $client->call('Query', array('parameters' => $profileParam));
  	   $profile = $profileResponse["QueryResult"]["Records"]["Record"];
...

I even tried two separate queries

 

 

[MadTechie]

Okay can we break this down a little, I don't get what's with FieldID 4964744 and 4953960..

Which one is for what ?

[lordphate]

FieldID 4964744 is the "Approved" field. It is only inserted during the approval process. Before the approval process that field (for that particular ContactID) is not in the database. Once approved or denied, a insertion is made that makes FieldID 4964744 for the ContactID with the Value of 'Approved' or 'Denied'

[MadTechie]

I'm sorry but I don't understand the logic,

 

FieldID 4964744 is the "Approved" field.

I get that

 

It is only inserted during the approval process. Before the approval process that field (for that particular ContactID) is not in the database. Once approved or denied, a insertion is made that makes FieldID 4964744 for the ContactID with the Value of 'Approved' or 'Denied'

But this suggests that the field doesn't exist until its been approved,

 

This would be easier if you had a schema or some samples!