Joining Tables: case statement for no matches?

Question

I have this query:

SELECT p.text,se.name,s.sub_name,SUM((p.volume / (SELECT SUM(p.volume) 
                FROM phrase p
                WHERE p.volume IS NOT NULL) * sp.position))
AS `index`
FROM phrase p
LEFT JOIN `position` sp ON sp.phrase_id = p.id
LEFT JOIN `engines` se ON se.id = sp.engine_id
LEFT JOIN item s ON s.id = sp.site_id
WHERE p.volume IS NOT NULL
AND s.ignored = 0
GROUP BY se.name,s.sub_name
ORDER BY se.name,s.sub_name

There are a few things I want to do with it:

1) The end of the calculation for 'index', I multiple it all by sp.position, then get it's SUM. If there is NO MATCH in the first LEFT JOIN 'position', I want to give sp.position a value of 200. So basically if in the 'phrase' table I have an ID=2, but that does not exist in sp.phrase_id in the entire 'position' table, then sp.position=200 for the 'index' calculation, otherwise it will it will be whatever value is stored in the 'position' table. I hope that makes sense.

2) I do a GROUP BY se.name. I would like to actually SUM the entire 'index' values for similar se.name fields. So in the resultset as it stands now, if there were 20 p.text rows with the same se.name, I would like to SUM the index column for the same se.name(s).

I am more of a PHP guy, but trying to learn more MySQL. I have become a big believer in making the DB do as much of the work as possible instead of trying to manipulate the dataset after it's been returned.

I hope the questions were clear. Anyways, can both 1) and 2) be done? There's much more I want to modify this query to do, but I think if I need more help in the future on it, it would require a different question.

The position table has a engines_id, phrase_id, item_id which will make it a unique entry. The value I am trying to calculate is the sp.position value. But there are cases when there is no entry for these IDs combined. If there is no entry for the combo of 3 IDs I just listed, I would like to use sp.position=200 in my calculation.

Answer 1

How's this:

select x.name, sum(index) from
(
SELECT p.text,se.name,s.sub_name,SUM((p.volume / (SELECT SUM(p.volume) 
            FROM phrase p
            WHERE p.volume IS NOT NULL) * if(sp.position is null,200,sp.position)))
AS `index`
FROM phrase p
LEFT JOIN `position` sp ON sp.phrase_id = p.id
LEFT JOIN `engines` se ON se.id = sp.engine_id
LEFT JOIN item s ON s.id = sp.site_id
WHERE p.volume IS NOT NULL
AND s.ignored = 0
GROUP BY se.name,s.sub_name
ORDER BY se.name,s.sub_name
 )x
GROUP BY x.name

Answer 2

Try the following:

1.) Use IFNULL(), in your case IFNULL(sp.position, 200)

2.) I am not entirely clear on this part, but it seems like you already have part of what you are asking.