CodeCharge Studio
search Register Login  

Visual Web Reporting

Visually create Web Reports in PHP, ASP, .NET, Java, Perl and ColdFusion.

YesSoftware Forums -> CodeCharge -> Programming

 SQL Query vice Table Query results are no records found when all columns in the search record are populated.

Print topic Send  topic

Author Message

Posts: 3
Posted: 05/17/2010, 8:08 PM

When writing an SQL Query to database vice Table Query results are no records found when all columns in the search record are populated.
This does not happen when you use the table query. Example In the table query you have in your search record 3 columns you can search on and you only populate one. The other two return a null set and are overlooked in the query. This does not happen when you convert to an SQL query. The query uses all columns in its query and if not populate looks for records that are null in these columns. This is not what I want. I want them to be excluded from the query as it does in the Table Query.

Below is my sql query:

SELECT Count(*) AS 'totalsession', caseservice.*, case_name, prog_code, closed, name
FROM ((caseservice LEFT JOIN intake ON
caseservice.case_nmber = intake.case_nmbr AND caseservice.instance = intake.instance) LEFT JOIN case_assignment ON
caseservice.case_nmber = case_assignment.case_nmber AND caseservice.instance = case_assignment.instan) LEFT JOIN users ON
case_assignment.case_worker =
WHERE(caseservice.session_date IS NOT NULL
AND caseservice.session_date >= '{s_from}'
AND caseservice.session_date <= '{s_to}' )
AND caseservice.client_cancel = {Expr0}
AND caseservice.theripist_cancel = {Expr1}
AND caseservice.client_noshow = {Expr2}
AND (intake.closed >= '{s_from}'
AND intake.closed <= '{s_to}')
AND intake.prog_code = '{s_prog}'
AND caseservice.case_nmber = {s_cnumber}
AND = {s_therapist}
GROUP BY caseservice.case_nmber
HAVING Count(*)< 8
View profile  Send private message

Posts: 1713
Posted: 05/18/2010, 7:51 PM

You could have default values in the event of null values. There is another way but I can seem to remember it right now. When it comes to me I will let you know.
View profile  Send private message

Add new topic Subscribe to topic   

These are Community Forums for users to exchange information.
If you would like to obtain technical product help please visit

Web Database

Join thousands of Web developers who build Web applications with minimal coding.

Home   |    Search   |    Members   |    Register   |    Login

Powered by UltraApps Forum created with CodeCharge Studio
Copyright 2003-2004 by  and YesSoftware, Inc.