Expand|Select|Wrap|Line Numbers
- 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
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
Expand|Select|Wrap|Line Numbers
- 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)
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