patm
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 = users.id
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 users.id = {s_therapist}
GROUP BY caseservice.case_nmber
HAVING Count(*)< 8
|
 |
 |
mamboBROWN
Posts: 1713
|
| Posted: 05/18/2010, 7:51 PM |
|
patm,
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.
|
|
|
|
|
