[RESOLVED] Getting multiple returns Help

fnp

I have a slight problem that I have been racking my head over for a couple of days now.

I have a table (tblSubmissions) that holds a key, this key is attached to another table (tblListings) that could have up to 12 entries with the key from tblSubmissions. Inside the tblListings there is a field that denotes the end date (EndDate) of the row.

I am trying to get a MySQL query that will return a list of submissions that have all the end dates filled in with an actual date. What I actually get is a list of all submissions that don't have a '0000-00-00' in the date. The DISTINCT helps to only produce one submission, but that submission usually has more than one listing attached to it and one will generally have an end date of '0000-00-00'.

I various other criteria that must be met as well but that is where I am struggling.

Here is the code

            $query = "SELECT DISTINCT SBX.SubmissionID, SBX.StoreID, SBX.SubxTitle, DATE_FORMAT(SBX.DateReceived, '%m/%d/%Y') AS DateReceived ";
            $query .= "FROM tblSubmissions SBX, tblListings AUC ";
            $query .= $wherestore.$wheredates."AND SBX.SubmissionID=AUC.SubmissionID AND SBX.CreatedBy Is NOT NULL AND SBX.SubmittedBy Is NOT NULL AND SBX.ApprovedBy Is NOT NULL AND SBX.RTSReason='' AND AUC.DateEnded != '0000-00-00' ";
            $query .= "ORDER BY SBX.SubmissionID DESC";
			$listTitle="Submissions Without Active Auctions";

paulnaj

I'd say remove the DISTINCT, and the change the line ...

AUC.DateEnded != '0000-00-00'

... to ...

AUC.DateEnded > '0000-00-00'

PS. you could make the code easier to manage by laying it out something like

<?php
$query = '
SELECT 
	SBX.SubmissionID
, 	SBX.StoreID
, 	SBX.SubxTitle
, 	DATE_FORMAT(SBX.DateReceived, "%m/%d/%Y") AS DateReceived 
FROM tblSubmissions SBX, tblListings AUC
'.$wherestore.'
'.$wheredates.'
AND SBX.SubmissionID = AUC.SubmissionID
AND SBX.CreatedBy IS NOT NULL 
AND SBX.SubmittedBy IS NOT NULL 
AND SBX.ApprovedBy IS NOT NULL 
AND SBX.RTSReason = "" 
AND AUC.DateEnded > "0000-00-00"
ORDER BY SBX.SubmissionID DESC
';
?>

fnp

Thanks for help, although I don't think I explained the problem well enough.

The tblLisings holds several rows with the same SubmissionID key from tblSubmissions, when I run this code I get a list of all submissions that match the criteria, but it also pulls all the Listings that have the same SubmissionID.

What I need it to do is to only list the submissions that do not have a '0000-00-00' in EndedDate field. But with this I still get submissions that have this because some of the other listings with the same SubmissionID don't have '0000-00-00'.

Hope this helps

Lars_Berg

To get submissions without any listing with dateEnded not filled in

select SubmissionID, 
       StoreID, 
       SubxTitle, 
       DATE_FORMAT(DateReceived, '&#37;m/%d/%Y') AS DateReceived 
  from tblSubmissions as sbx
 where not exists
      (select *
         from tblListings as auc
        where sbx.SubmissionID = auc.SubmissionID
          and auc.DateEnded = '0000-00-00')

fnp

When running this code through MySQL I get the following error.

#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'exists'.

I can't seem to get the 'NOT EXISTS' statement to work.

bradgrafelman

Which version of MySQL server are you using? If it's less than MySQL 4.1, then your server is not only ancient but it also doesn't support the "EXISTS" syntax. 🙂

fnp

You are correct, I will be updating my databases tonight and try to get back to this tomorrow.

Thanks

fnp

Now that my databases are MySQL 5.0 and a slight tweak to the code and wa-la. I will post the finished code here.

SELECT SBX.SubmissionID, 
	SBX.StoreID,
	SBX.SubxTitle, 
	DATE_FORMAT(SBX.DateReceived, '%m/%d/%Y') AS DateReceived
		FROM tblSubmissions SBX
			WHERE SBX.CreatedBy Is NOT NULL 
			AND SBX.SubmittedBy Is NOT NULL 
			AND SBX.ApprovedBy Is NOT NULL 
			AND SBX.RTSReason='' 
			AND NOT EXISTS
				(SELECT * FROM tblListings AUC 
					WHERE SBX.SubmissionID=AUC.SubmissionID 
					AND AUC.DateEnded = '0000-00-00')";
				ORDER BY SBX.SubmissionID DESC

Thanks for the help