Solved

Records with field not null

Posted on 2009-07-01
6
263 Views
Last Modified: 2012-05-07
I have this code SELECT * FROM Events WHERE CornPalace='1' AND APPROVED='1'  ORDER BY RAND() Limit 5 - how do I get it to only pull up the records where Image3FileName is not empty?
0
Comment
Question by:katlees
  • 2
  • 2
  • 2
6 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24757636
simply add that condition
SELECT * FROM Events WHERE CornPalace='1' AND APPROVED='1' AND  Image3FileName IS NOT NULL ORDER BY RAND() Limit 5 

Open in new window

0
 
LVL 17

Expert Comment

by:pssandhu
ID: 24757661
SELECT *
FROM Events
WHERE CornPalace='1' AND
APPROVED='1' AND
(Image3FileName IS NOT NULL or  Image3FileName <> '')
 ORDER BY RAND() Limit 5
0
 
LVL 17

Expert Comment

by:pssandhu
ID: 24757675
I meant to say "AND":

SELECT *
FROM Events
WHERE CornPalace='1' AND
APPROVED='1' AND
Image3FileName IS NOT NULL AND  
Image3FileName <> ''
ORDER BY RAND() Limit 5  
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:katlees
ID: 24757718
When I try
SELECT * FROM Events WHERE CornPalace='1' AND APPROVED='1' AND  Image3FileName IS NOT NULL ORDER BY RAND() Limit 5

I still get records where Image3FileName is blank. I even ran it in the PHPMyAdmin and got the same results
Changing it to SELECT * FROM Events WHERE CornPalace='1' AND APPROVED='1' AND  Image3FileName <> ORDER BY RAND() Limit 5 gives me a blank page and an SQL error in PHPMyAdmin
Error is
SQL query:  

SELECT *
FROM EVENTS WHERE CornPalace = '1'
AND APPROVED = '1'
AND Image3FileName <> 
ORDER BY RAND( )
LIMIT 5

MySQL said:  

#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 'ORDER BY RAND() Limit 5' at line 1

0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 24757756
blank and NULL is not the same thing
SELECT * FROM Events WHERE CornPalace='1' AND APPROVED='1' AND  Image3FileName IS NOT NULL 
AND Image3FileName != ''
ORDER BY RAND() Limit 5

Open in new window

0
 

Author Closing Comment

by:katlees
ID: 31598929
Thank you.
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
date diff with Fiscal Calendar 4 75
T-SQL: New to using transactions 9 53
sql query display the latest row 10 51
Query - Duplicate dates with different activities counts 10 43
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question