SQLTeam.com | Weblogs | Forums

[HELP] STRCMP or any other way to compare strings

So I'm having a problem now comparing strings or using any other method to make my database give me the objects with different colors.

So the problem goes as follow:
I need SQL to show me the urgent requests of a store that have 2 or more items to be fixed with 2 different colors.

SELECT request.Id_request, caractristica_peca.color
FROM request, request_fix, fix, caractristica_peca
WHERE request.Id_request=request_fix.Id_request
AND request_fix.Id_fix=fix.Id_fix
AND fix.Id_item=caractristica_peca.Id_item
AND request.Taxa_Urgency LIKE "Yes"

Now my SQL shows me all the urgent requests_id, one of them repeated because it's the only one with 2 items...i just don't know how to compare the colors between those items to check they are indeed different.
I have tried STRCMP() but nothing i do seems to work...i'm sure i'm the problem tho...i'm very new at this xD

I'm not real familiar with MySQL, but this SQL is generic enough it should work there:


SELECT request.Id_request, MIN(caractristica_peca.color) AS low_color,
    MAX(caractristica_peca.color) AS high_color
FROM request, request_fix, fix, caractristica_peca
WHERE request.Id_request=request_fix.Id_request
AND request_fix.Id_fix=fix.Id_fix
AND fix.Id_item=caractristica_peca.Id_item
AND request.Taxa_Urgency LIKE "Yes"
GROUP BY request.Id_request
HAVING COUNT(caractristica_peca.color) > 1 AND 
    MIN(caractristica_peca.color) <> MAX(caractristica_peca.color)

Thank you very much man, that really solved my problem :slight_smile:

You're welcome very much :-).