PDA

View Full Version : Apply different maths depending on string contents

nxzmplty
12-12-2011, 06:39 PM
EDIT: Having done some more research I have got a little closer to my solution. Please see the second post before replying!
EDIT 2: Resolved. I'll show the answer below :)

Hi all, a bit of a wierd one now. I am trying to limit this to the fewest number of queries (2), but if it's not possible then I can always just use a bit of PHP.

It's a bit complicated to explain the entire database structure but here goes.

I have my first query working well, which outputs two columns: tagName (string) and tagRating (float). Let's say this output is:

tagName | tagRating
'red' | 4.2
'blue' | 3.9
'silver'| 2.4

If you hadn't guessed, I'm trying to do a "recommended for you" type query. So that's the first output, and it essentially tells us that the user really likes red cars.

Now comes the next bit. I want to go back into the database and select all the tagNames that have been added by ALL users. So the table may look like this:

*NOTE: yes, car 34 has been tagged as both 'blue' and 'red'.

tagID | userID | tagName | carID
1 | 2 | 'red' | 34
2 | 3 | 'red' | 45
3 | 4 | 'blue' | 34

Now here's the tricky bit. I want to group the cars by ID, but create another temporary column called (for example) 'tagScore'. If tagName = 'red', tagScore would be 4.2 as per the original output. This is put into the query using the PHP.

Then, once grouped we would have another temporary column in an output like this:

carID | carScore
34 | 8.1 //(4.2+39)
45 | 3.9

So yeah, a bit long winded but would appreciate some answers!

Is it even possible?
Is it worth it or should I just go ahead and use 5 or so queries?
EDIT: Simple question: Is it possible, in a SELECT statement, to create a temporary column with values depending on the contents of a string?

BubikolRamios
12-12-2011, 07:03 PM
if your tables froma above are A
,B,C

select B.carId, sum(A.tagRating) as carScore
from A left join B on A.tagName = B.tagName
group by B.carID

Something like that, for start, would give you C.

nxzmplty
12-12-2011, 07:32 PM
OK so I have just learned about the CASE operator :)

SELECT carID,
CASE tagName
WHEN 'red' THEN 4.2
WHEN 'blue' THEN 3.9
WHEN 'silver' THEN 2.4
END as tagScore
FROM tags

This seems to do the first bit. However, is there now a way to take tagScore as a column, group by carID, and get the SUM(tagScore)? I currently get an error that tagScore is not a valid column. How can I reuse this temporary column?

nxzmplty
12-12-2011, 07:35 PM
if your tables froma above are A
,B,C

select B.carId, sum(A.tagRating) as carScore
from A left join B on A.tagName = B.tagName
group by B.carID

Something like that, for start, would give you C.

Thanks for the reply. However, tagRating is calculated on-the-fly in the first query and is not available for a join.

nxzmplty
12-12-2011, 07:52 PM
SELECT sum(
CASE tagName
WHEN 'red'
THEN 4.2
WHEN 'blue'
THEN 3.9
WHEN 'silver'
THEN 2.4
END ) AS carScore, carID
FROM tags
GROUP BY carID

Well then, that turned out to be a lot easier than I made it out to be. Hope this helps somebody at some point!