- CRITERIA;
-
+-------------+--------------+------+-----+---------+-------+
-
| Field | Type | Null | Key | Default | Extra |
-
+-------------+--------------+------+-----+---------+-------+
-
| CritCode | int(4) | NO | PRI | 0 | |
-
| Description | varchar(150) | YES | | NULL | |
-
| CritGroup | varchar(10) | YES | | NULL | |
-
| Detail | varchar(30) | YES | | NULL | |
-
+-------------+--------------+------+-----+---------+-------+
-
-
-
SCHCRIT;
-
+------------+--------+------+-----+---------+-------+
-
| Field | Type | Null | Key | Default | Extra |
-
+------------+--------+------+-----+---------+-------+
-
| SchemeCode | int(6) | NO | PRI | 0 | |
-
| CritCode | int(4) | NO | PRI | 0 | |
-
+------------+--------+------+-----+---------+-------+
-
-
SCHEMES;
-
+------------------------+---------------+------+-----+---------+-------+
-
| Field | Type | Null | Key | Default | Extra |
-
+------------------------+---------------+------+-----+---------+-------+
-
| SchemeCode | int(6) | NO | PRI | 0 | |
-
| SourceOrg | int(6) | YES | | NULL | |
-
| Title | varchar(100) | NO | MUL | | |
-
| Objectives | mediumtext | YES | | NULL | |
-
| UpdateDate | date | YES | | NULL | |
-
| CheckDates | char(1) | YES | | NULL | |
-
| DecisionMakers | mediumtext | YES | | NULL | |
-
| DecisionTime | mediumtext | YES | | NULL | |
-
| GroupIndividuals | char(1) | YES | | NULL | |
-
| EligibleDistricts | mediumtext | YES | | NULL | |
-
| Eligibility | mediumtext | YES | | NULL | |
-
| organisationID | int(11) | YES | | NULL | |
-
+------------------------+---------------+------+-----+---------+-------+
-
-
Main query:
-
select DISTINCT d.title, d.schemecode, d.objectives,
-
d.eligibility, d.eligibledistricts
-
-
,( select DISTINCT COUNT(cr.CritCode) AS Rank
-
from CRITERIA cr
-
INNER JOIN SCHCRIT sc on sc.CritCode=cr.CritCode
-
where sc.SchemeCode= a.schemecode
-
AND cr.CritCode in(71,5043, 5074, 5024, 5025, 4003, 0)) AS match_rank
-
,(select IF(cr.Description) IS NULL,'XX',cr.Description)
-
from CRITERIA cr
-
INNER JOIN SCHCRIT sc on sc.CritCode=cr.CritCode where sc.SchemeCode= a.schemecode
-
AND cr.CritCode = 400 ) AS cost1
-
-
FROM SCHCRIT a inner join SCHCRIT b on(a.schemecode=b.schemecode)
-
inner join SCHCRIT c on(a.schemecode=c.schemecode)
-
inner join SCHEMES d on(a.schemecode=d.schemecode) e)
-
inner join personOrganisation po on (d.sourceorg=po.personid))
-
WHERE (a.critcode =1 or a.critcode =71)
-
and c.critcode in (4000, 4003, 0, 0, 0)
-
and b.critcode in (5000, 5043, 5074, 5024, 5025)
-
order by match_rank DESC, cost1 ASC
-
-
subquery: this the part of main query mention above and here is having problem with
-
select IF(cr.Description) IS NULL,'XX',cr.Description)
-
from CRITERIA cr
-
INNER JOIN SCHCRIT sc on sc.CritCode=cr.CritCode where sc.SchemeCode= a.schemecode
-
AND cr.CritCode = 400 ) AS cost1
Activity:
I have three tables to pull data from when user is making a search with different 'CRITERIA' those are attached with
different 'SCHEMES'. now i am dysplayning searched results with different sort order.
1. Order by maximum CRITERIA matched SCHEMES ( that i am getting from the subquery result field "AS match_rank" )
2. then 2nd sort is on "cost1" that sorting among given "match_rank". cost1 is coming with some null values those are coming in the top
when i do the sorting in present.
3. both sorting are associated with my main query.
Problem: my main query working fine and fetching all expecting data, but in the second sort on cost1 (that i mentioned above)
"null" value fields are coming onthe top but i wanted them in the bottom. then i tried with different functions to replace NULL value
to bring those end of the sesond sorting but null cant being replaced.
i tryed those differet way with subquery
- 1. if((field IS NULL), 'XX', field)
-
2. ISNULL(field),'XX',field
-
3. CASE WHEN field IS NULL then 'XX' else field end
-
4. order by case when isNULL(cr.Description) then 1 else 0 end, cr.Description asc
-
4. IF(field IS NULL,'XX',field)
but none of those replacing NULL value
please help me on that.
Note:
1. main query and sub query working fine and giving expected result
2. ISNULL(), IF(), CASE statement working if i use them separately and able to replace NULL but
not as a subquery
thanks in advance
